Recently I was asked to test performance of an Active Dataguard or Physical standby database where the DWH/reporting type SELECT workload was running, and slowness was reported by the user. In order to understand the system better its always good to generate the AWR reports, but this being the standby database which is opened in READ ONLY mode, means you cannot directly call the AWR report related scripts on the standby as they will fail with the error ‘database opened in read only’ mode.
This post is all about enabling AWR reporting’s in the standby database. I am doing this test on 12.2.0.1.0 ADG.
About test databases, we have the primary (TESLPH) is opened in Read Write mode and the standby (TESLPRBH) in Read Only Mode With real time Apply.
SQL> select db_unique_name,open_mode, database_role from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
TESLPH READ WRITE PRIMARY
SQL> select db_unique_name,open_mode, database_role from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
TESLPRBH READ ONLY WITH APPLY PHYSICAL STANDBY
Next, you have to enable the SYS$UMF account. The RMF is used for collecting performance statistics for an Oracle Database. The SYS$UMF user is the default database user that has all the privileges to access the system-level RMF views and tables. All the AWR related operations in RMF can be performed only by the SYS$UMF user. The SYS$UMF user is locked by default and it must be unlocked before deploying the RMF topology. You need to provide password for the SYS$UMF user when creating database links in the RMF topology. If the password for the SYS$UMF user is changed, all the database links in the RMF topology must be recreated.
Next you have to make sure if the “_umf_remote_enabled” underscore parameter is set to TRUE else you will receieve “ORA-20501: Remote UMF is disabled” when doing any UMF related operation on the database.
-- On both PRIMARY and STANDBY Databases.
SQL> select username,common,account_status from dba_users where username ='SYS$UMF';
USERNAME COM ACCOUNT_STATUS
--------------- --- --------------------------------
SYS$UMF YES OPEN
-- On both PRIMARY and STANDBY Databases.
SQL> alter system set "_umf_remote_enabled"=TRUE scope=BOTH;
System altered.
Next you need to create two database links for ‘primary to the standby‘ and ‘standby to the primary’ communication using SYS$UMF and DB UNIQUE NAME of the databases. Will test the connectivity of both of them to see if they are working okay.
-- On PRIMARY database.
SQL> CREATE DATABASE LINK "PRIMARY_TO_STANDBY_DBLINK" CONNECT TO "SYS$UMF" IDENTIFIED BY VALUES 'oracle90' USING 'TESLPRBH';
Database link created.
SQL> CREATE DATABASE LINK "STANDBY_TO_PRIMARY_DBLINK" CONNECT TO "SYS$UMF" IDENTIFIED BY VALUES 'oracle90' USING 'TESLPH';
Database link created.
SQL> select db_link, username, host from dba_db_links
DB_LINK USERNAME HOST
------------------------------ --------------- ----------------------------------------
STANDBY_TO_PRIMARY_DBLINK SYS$UMF TESLPH
PRIMARY_TO_STANDBY_DBLINK SYS$UMF TESLPRBH
SQL> select db_unique_name from v$database@STANDBY_TO_PRIMARY_DBLINK;
DB_UNIQUE_NAME
------------------------------
TESLPH
1 row selected.
SQL> select db_unique_name from v$database@PRIMARY_TO_STANDBY_DBLINK;
DB_UNIQUE_NAME
------------------------------
TESLPRBH
1 row selected.
-- On PRIMARY database.
SQL> CREATE DATABASE LINK "PRIMARY_TO_STANDBY_DBLINK" CONNECT TO "SYS$UMF" IDENTIFIED BY VALUES 'oracle90' USING 'TESLPRBH';
Database link created.
SQL> CREATE DATABASE LINK "STANDBY_TO_PRIMARY_DBLINK" CONNECT TO "SYS$UMF" IDENTIFIED BY VALUES 'oracle90' USING 'TESLPH';
Database link created.
SQL> select db_link, username, host from dba_db_links
DB_LINK USERNAME HOST
------------------------------ --------------- ----------------------------------------
STANDBY_TO_PRIMARY_DBLINK SYS$UMF TESLPH
PRIMARY_TO_STANDBY_DBLINK SYS$UMF TESLPRBH
-- Lets try the connectivity using DB Links that we have created
SQL> select db_unique_name from v$database@STANDBY_TO_PRIMARY_DBLINK;
DB_UNIQUE_NAME
------------------------------
TESLPH
1 row selected.
SQL> select db_unique_name from v$database@PRIMARY_TO_STANDBY_DBLINK;
DB_UNIQUE_NAME
------------------------------
TESLPRBH
1 row selected.
Next we have to add the primary database node to the UMF repository, for that you have to run below command on both the primary & standby databases.
I am assigning primary site name as –> ‘primary_site’
and standby database site name as –> ‘standby_site’
-- On PRIMARY database
SQL> exec dbms_umf.configure_node ('primary_site');
PL/SQL procedure successfully completed.
SQL>
-- On STANDBY database.
SQL> exec dbms_umf.configure_node('standby_site','STANDBY_TO_PRIMARY_DBLINK');
PL/SQL procedure successfully completed.
SQL>
-- Use in case want to UNCONFIGURE the node.
SQL> exec DBMS_UMF.UNCONFIGURE_NODE;
Next we need to create the UMF topology, I am creating it as ‘Topology_1‘ on primary database. You can only have a single UMF topology in the database, if you try to create more you will get “ORA-20506: Maximum number of topologies exceeded”
-- On PRIMARY Database
SQL> exec DBMS_UMF.create_topology ('Topology_1');
PL/SQL procedure successfully completed.
-- Lets query if the toplogy is created with no errors and is ACTIVE.
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
------------------------------ ---------- ---------------- --------
Topology_1 1530523744 1 ACTIVE
1 row selected.
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
------------------------------ ------------------------------ ---------- ---------- ----- ----- --------------------
Topology_1 primary_site 1530523744 0 FALSE FALSE OK
1 row selected.
-- Run in case want to DROP the topology
SQL> exec DBMS_UMF.drop_topology('NAME-OF-TOPOLOGY');
Next you have to add the standby node to the topology, for that you need to use register_node procedure and need to mention both of the DBLinks that we have created earlier. This you have to run on PRIMARY node.
-- On PRIMARY database
SQL> exec DBMS_UMF.register_node ('Topology_1', 'standby_site', 'PRIMARY_TO_STANDBY_DBLINK', 'STANDBY_TO_PRIMARY_DBLINK', 'FALSE', 'FALSE');
PL/SQL procedure successfully completed.
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
------------------------------ ------------------------------ ---------- ---------- ----- ----- --------------------
Topology_1 primary_site 1530523744 0 FALSE FALSE OK
Topology_1 standby_site 3265600723 0 FALSE FALSE OK
2 rows selected.
SQL>
Now when both of the nodes ‘primary_site‘ and ‘standby_site‘ are added to the topology, we have to register the standby node for the AWR service.
-- On PRIMARY database.
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'standby_site');
PL/SQL procedure successfully completed.
SQL>
SQL> select * from dba_umf_service;
TOPOLOGY_NAME NODE_ID SERVICE
------------------------------ ---------- -------
Topology_1 3265600723 AWR
1 row selected.
SQL> select * from dba_umf_link;
TOPOLOGY_NAME FROM_NODE_ID TO_NODE_ID LINK_NAME
------------------------------ ------------ ---------- ------------------------------
Topology_1 1530523744 3265600723 PRIMARY_TO_STANDBY_DBLINK
Topology_1 3265600723 1530523744 STANDBY_TO_PRIMARY_DBLINK
2 rows selected.
Now everything is set, no errors and we are all good to create some manual snaps. Let’s generate some remote snapshots, for that you have to run below command from PRIMARY database.
-- On PRIMARY database.
SQL>
SQL> alter system archive log current;
System altered.
SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.
SQL> exec dbms_workload_repository.create_remote_snapshot('standby_site');
PL/SQL procedure successfully completed.
Now time to call the AWR report base scripts to generate the AWR reports for the standby database, you can call them on either Primary of the Standby server. For example, below I called the AWR report from the primary database and passed DBID and instance number of the standby database and have got the report for the standby database.
SQL> SQL> @?/rdbms/admin/awrrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
4265600723 1 TESLPRBH TESLPRBH monkey02lx031
* 5576289360 1 TESLPRBH TESLPH monkey1903nm12
Enter value for dbid: 4265600723
Using 4265600723 for database Id
Enter value for inst_num: 1
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
TESLPRBH TESLPRBH 1 13 Jan 2022 04:10 1
2 13 Jan 2022 04:11 1
3 13 Jan 2022 04:14 1
4 13 Jan 2022 04:14 1
5 13 Jan 2022 04:14 1
6 13 Jan 2022 04:15 1
7 13 Jan 2022 04:15 1
8 13 Jan 2022 04:17 1
9 13 Jan 2022 04:18 1
10 13 Jan 2022 04:18 1
11 13 Jan 2022 04:18 1
12 13 Jan 2022 04:18 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
WORKLOAD REPOSITORY report for
DB Name DB Id Unique Name DB Role Edition Release RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
TESLPRBH 4265600723 TESLPRBH PHYSICAL STANDBY EE 12.2.0.1.0 NO NO
Instance Inst Num Startup Time
------------ -------- ---------------
TESLPRBH 1 13-Feb-21 05:16
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
monkey02lx0315b Linux x86 64-bit 4 4 4 15.49
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 1 13-Jan-22 04:10:57 61 .5
End Snap: 12 13-Jan-22 04:18:27 60 .6
Elapsed: 7.50 (mins)
DB Time: 0.73 (mins)
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 0.1 44.1 0.00 0.01
DB CPU(s): 0.1 24.1 0.00 0.01
Background CPU(s): 0.0 14.2 0.00 0.00
Redo size (bytes): 0.0 0.0
Logical read (blocks): 1,227.7 552,449.0
Block changes: 7.7 3,465.0
Physical read (blocks): 594.5 267,510.0
Physical write (blocks): 44.1 19,821.0
Read IO requests: 16.6 7,466.0
.......
................
.......................
Hope It Helped!
Prashant Dixit
