Oracle 12.2 came up with a new feature that we can generate AWR report from Physical Standby database if we have enabled active datagaurd.
We need to perform the following steps to enable AWR reporting from a physical standby database.
Step 1: Unlock SYS$UMF user and change its password using SYS user. SYS$UMF account is used to administer Remote Management Framework, including the remote Automatic Workload Repository (AWR).
SQL>alter user sys$umf identified by oracle account unlock;
Step 2: Create the database link from primary to the standby database and standby database to a primary.
- Primary database DB_UNIQUE_NAME: std_dbupgrade
- Standby database DB_UNIQUE_NAME: dbupgrade
SQL> create database link primary_to_standby connect to "SYS$UMF" identified by "oracle" using 'dbupgrade'; Database link created. SQL> create database link standby_to_primary connect to "SYS$UMF" identified by "oracle" using 'std_dbupgrade'; Database link created.
Step 3: Check if both database link works properly.
SQL> select 1 from dual@primary_to_standby; 1 ---------- 1 SQL> select 1 from dual@standby_to_primary; 1 ---------- 1
Step 4: Now we need to configure database to add topology. Each database name must be assigned a unique name. Default name is db_unique_name. In my case dbupgrade and std_dbupgarde.
DBMS_UMF.CONFIGURE_NODE procedure configures a node that needs to be registered with the RMF topology. This procedure must be executed on the node that needs to be configured.
DBMS_UMF.CONFIGURE_NODE( node_name IN VARCHAR2 DEFAULT NULL, dblink_to_target IN VARCHAR2 DEFAULT NULL);
On primary :
SQL> exec dbms_umf.configure_node('std_dbupgrade');
PL/SQL procedure successfully completed.
On standby :
SQL>exec dbms_umf.configure_node ('dbupgrade','standby_to_primary');
PL/SQL procedure successfully completed.
Step 5: Create RMF topology
DBMS_UMF.CREATE_TOPOLOGY procedure creates the RMF topology and designates the node on which it is executed as the destination node for that topology.
DBMS_UMF.CREATE_TOPOLOGY( topology_name IN VARCHAR2);
SQL> exec DBMS_UMF.create_topology ('Topology_1');
PL/SQL procedure successfully completed.
Step 6: Check DBA_UMF_REGISTRATION and dba_umf_topology view
SQL> select * from dba_umf_topology; TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY --------------- ---------- ---------------- -------- Topology_1 4040050753 8 ACTIVE SQL> col node_name format a20 SQL> select * from DBA_UMF_REGISTRATION; TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE -------------------- -------------------- ---------- ---------- ----- ----- ----- Topology_1 std_dbupgrade 4040050753 0 FALSE FALSE OK
We can see here it displays both databases.
Step 7: Register the standby database with topology
DBMS_UMF.REGISTER_NODE function and procedure register a node with the RMF topology. This procedure and function must be executed only on the destination node in the RMF topology.
Syntax
DBMS_UMF.REGISTER_NODE( topology_name IN VARCHAR2, node_name IN VARCHAR2, dblink_to_node IN VARCHAR2 DEFAULT NULL, dblink_from_node IN VARCHAR2 DEFAULT NULL, as_source IN VARCHAR2 DEFAULT 'TRUE', as_candidate_target IN VARCHAR2 DEFAULT 'FALSE');
SQL> exec DBMS_UMF.register_node ('Topology_1', 'dbupgrade', 'primary_to_standby', 'standby_to_primary', 'FALSE','FALSE');
PL/SQL procedure successfully completed.
Step 8: Enable AWR service on the remote node
DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE
procedure registers a remote database in the Automatic Workload Repository (AWR) using the Remote Management Framework (RMF).
DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE( node_name IN VARCHAR2, topology_name IN VARCHAR2 DEFAULT NULL);
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'dbupgrade'); PL/SQL procedure successfully completed.
Step 9: Now again verify in dba_umf_registration view
SQL> select * from dba_umf_registration; TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE --------------- --------------- ---------- ---------- ----- ----- -------------------- Topology_1 std_dbupgrade 4040050753 0 FALSE FALSE OK Topology_1 dbupgrade 1978911757 0 FALSE FALSE OK
Step 10: Create snapshot using RMF in the primary database for the remote database.
DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT
function and procedure create a remote snapshot using the Remote Management Framework (RMF). The function returns the snapshot ID.
DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT( node_id IN NUMBER, flush_level IN VARCHAR2 DEFAULT 'BESTFIT');
SQL> exec dbms_workload_repository.create_remote_snapshot('dbupgrade');
PL/SQL procedure successfully completed.
Do it 2 – 3 times so we can generate AWR report based on a snapshot
Step 11: Create AWR report from a standby database
Note: NODE ID generated above consider as DBID for a standby database.
SQL> @?/rdbms/admin/awrrpti Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'. 'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active report Enter value for report_type: html Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ ---------- --------- ---------- ------ 1978911757 1 DBUPGRAD dbupgrade localhost.lo * 1987568604 1 DBUPGRAD dbupgrade localhost1.lo Enter value for dbid: 1978911757 Using 1978911757 for database Id Enter value for inst_num: 1 Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Instance DB Name Snap Id Snap Started Snap Level ------------ ------------ ---------- ------------------ ---------- dbupgrade DBUPGRAD 4 13 Aug 2018 10:00 1 5 13 Aug 2018 10:41 1 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 4 Begin Snapshot Id specified: 4 Enter value for end_snap: 5 End Snapshot Id specified: 5 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_4_1_5.html. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: awr_from_standby Using the report name awr_from_standby
After specifying AWR report type HTML it will ask for DBID, give dbid associated with nodeid in dba_umf_registration.
Give other inputs for AWR report.
Once it is completed you can see AWR report as follows :
We can see in First line Role is written as PHYSICAL STANDBY.
Stay tuned for More articles on Oracle DataGuard
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel : https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
