Generate AWRs in Active Data Guard (ADG) Physical Standby Databases
Starting with Oracle 12cR2 (12.2), Automatic Workload Repository (AWR) data can be captured for ADG standby databases. This feature enables analyzing any performance-related issues for ADG standby databases.
Environment:
------------------
------------------
1. Primary RAC: Oracle 19c (19.3) GI, Oracle 19c (19.12) RU and same for RDBMS
2. Standby RAC: Oracle 19c (19.3) GI, Oracle 19c (19.12) RU and same for RDBMS
2. Standby RAC: Oracle 19c (19.3) GI, Oracle 19c (19.12) RU and same for RDBMS
3. Operating System: Oracle Linux Server 7.9
1. Confirm the open mode and the database role on the standby:
SQL> select distinct instance_name,database_role,protection_mode,flashback_on,status from gv$database,gv$instance;
INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE FLASHBACK_ON STATUS
--------------- -------------------- -------------------- -------------------- ----------
orcls1 PHYSICAL STANDBY MAXIMUM PERFORMANCE YES OPEN
orcls2 PHYSICAL STANDBY MAXIMUM PERFORMANCE YES OPEN
--------------- -------------------- -------------------- -------------------- ----------
orcls1 PHYSICAL STANDBY MAXIMUM PERFORMANCE YES OPEN
orcls2 PHYSICAL STANDBY MAXIMUM PERFORMANCE YES OPEN
2. On Primary RAC Database
The SYS$UMF user is the default database user that has all the privileges to access the system-level Remote Management Framework (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:
SQL> alter user sys$umf identified by sysumf account unlock;
User altered.
User altered.
3. Create the database link between the primary database and the standby database and vice versa:
SQL> create database link dbl_orcl_to_orcls CONNECT TO sys$umf IDENTIFIED BY sysumf using 'orcls';
Database link created.
Database link created.
SQL> create database link dbl_orcls_to_orcl CONNECT TO sys$umf IDENTIFIED BY sysumf using 'orcl';
Database link created.
Database link created.
4. We need to configure database nodes to add to the topology. Each database node in a topology must be assigned a unique name (default is DB_UNIQUE_NAME):
Example we have chosen "primary" for primary and "stanby" for standby.
SQL> alter system set "_umf_remote_enabled"=TRUE scope=BOTH;
System altered.
System altered.
SQL> exec dbms_umf.configure_node ('primary');
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
5. The standby database is remote to the target system (the primary database). We can register it via the corresponding database link.
On Physical Standby Database:
SQL> exec dbms_umf.configure_node ('stanby','dbl_orcls_to_orcl');
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
6. Create the RMF topology:
SQL> exec DBMS_UMF.create_topology ('Topology1');
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
7. Verify the steps done at primary RAC database
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology1 509936611 1 ACTIVE
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology1 509936611 1 ACTIVE
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology1 primary 509936611 0 FALSE FALSE OK
SQL>
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology1 primary 509936611 0 FALSE FALSE OK
SQL>
8. Register the standby database with the topology
SQL> exec DBMS_UMF.register_node ('Topology1', 'stanby', 'dbl_orcl_to_orcls', 'dbl_orcls_to_orcl', 'FALSE', 'FALSE');
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
9. Enable the AWR service on the node:
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stanby');
PL/SQL procedure successfully completed.
10. Verify all the queries
PL/SQL procedure successfully completed.
10. Verify all the queries
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology1 509936611 7 ACTIVE
SQL>
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology1 509936611 7 ACTIVE
SQL>
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology1 primary 509936611 0 FALSE FALSE OK
Topology1 stanby 3184583509 0 FALSE FALSE OK
SQL>
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology1 primary 509936611 0 FALSE FALSE OK
Topology1 stanby 3184583509 0 FALSE FALSE OK
SQL>
SQL> select * from dba_umf_service;
TOPOLOGY_NAME NODE_ID SERVICE
--------------- ---------- -------
Topology1 3184583509 AWR
SQL>
11. Create a remote snapshot using the RMF
TOPOLOGY_NAME NODE_ID SERVICE
--------------- ---------- -------
Topology1 3184583509 AWR
SQL>
11. Create a remote snapshot using the RMF
SQL> exec dbms_workload_repository.create_remote_snapshot('stanby');
PL/SQL procedure successfully completed.
Note: We need to run at least two to get the begin_snap and end_snap.
12. Switch the logfiles at Primary RAC Database
PL/SQL procedure successfully completed.
Note: We need to run at least two to get the begin_snap and end_snap.
12. Switch the logfiles at Primary RAC Database
SQL> alter system switch all logfile;
System altered.
System altered.
SQL> alter system switch all logfile;
System altered.
System altered.
SQL> alter system switch all logfile;
System altered.
System altered.
13. Login to Physical Standby Database and execute AWR reports
SQL> select distinct instance_name,database_role,protection_mode,flashback_on,status from gv$database,gv$instance;
INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE FLASHBACK_ON STATUS
--------------- -------------------- -------------------- -------------------- ----------
orcls1 PHYSICAL STANDBY MAXIMUM PERFORMANCE YES OPEN
orcls2 PHYSICAL STANDBY MAXIMUM PERFORMANCE YES OPEN
--------------- -------------------- -------------------- -------------------- ----------
orcls1 PHYSICAL STANDBY MAXIMUM PERFORMANCE YES OPEN
orcls2 PHYSICAL STANDBY MAXIMUM PERFORMANCE YES OPEN
SQL> @?/rdbms/admin/awrrpti.sql
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
~~~~~~~~~~~~~~~~~~~~~~~
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
------------ ---------- --------- ---------- ------
1610181442 1 ORCL orcl1 oel70-base1
1610181442 2 ORCL orcl2 oel70-base2
3184583509 1 ORCL orcls1 oel70-base3
3184583509 2 ORCL orcls2 oel70-base4
Enter value for dbid: 3184583509
Using 3184583509 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: 5
Listing the last 5 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcls1 ORCL 1 14 Sep 2021 23:44 1
2 15 Sep 2021 01:01 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_2.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awrrpt_orcls1.html
14. Execute for Instance-2:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
1610181442 1 ORCL orcl1 oel70-base1
1610181442 2 ORCL orcl2 oel70-base2
3184583509 1 ORCL orcls1 oel70-base3
3184583509 2 ORCL orcls2 oel70-base4
Enter value for dbid: 3184583509
Using 3184583509 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: 5
Listing the last 5 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcls1 ORCL 1 14 Sep 2021 23:44 1
2 15 Sep 2021 01:01 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_2.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awrrpt_orcls1.html
14. Execute for Instance-2:
SQL> @?/rdbms/admin/awrrpti.sql
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
~~~~~~~~~~~~~~~~~~~~~~~
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
------------ ---------- --------- ---------- ------
1610181442 1 ORCL orcl1 oel70-base1
1610181442 2 ORCL orcl2 oel70-base2
3184583509 1 ORCL orcls1 oel70-base3
3184583509 2 ORCL orcls2 oel70-base4
Enter value for dbid: 3184583509
Using 3184583509 for database Id
Enter value for inst_num: 2
Using 2 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: 2
Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcls2 ORCL 1 14 Sep 2021 23:47 1
2 15 Sep 2021 01:03 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_2_1_2.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awrrpt_orcls2.html
Using the report name /home/oracle/awrrpt_orcls2.html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
1610181442 1 ORCL orcl1 oel70-base1
1610181442 2 ORCL orcl2 oel70-base2
3184583509 1 ORCL orcls1 oel70-base3
3184583509 2 ORCL orcls2 oel70-base4
Enter value for dbid: 3184583509
Using 3184583509 for database Id
Enter value for inst_num: 2
Using 2 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: 2
Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcls2 ORCL 1 14 Sep 2021 23:47 1
2 15 Sep 2021 01:03 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_2_1_2.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awrrpt_orcls2.html
Using the report name /home/oracle/awrrpt_orcls2.html
Hope it helps....
Please refer for more details:
How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1)
How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1)
