Managing AWR in Active Data Guard Standby Databases

Problem
- Automatic Workload Repository (AWR) snapshots cannot be taken in a read-only standby environment.
- Performance monitoring and analysis is limited to basic STATSPACK functionality .

Solution
In Oracle Database 12.2, the AWR framework is enhanced to support capture of remote snapshots from any generic database, including Active Data Guard (ADG) databases. This framework is called the Remote Management Framework (RMF).
• A target catalog database collects snapshots from the remote databases (sources).
• Snapshots can be collected automatically or manually.
• AWR tables on the catalog database accumulate snapshot data from all sources via database links.
• Source databases must be registered on the catalog via new DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE API

These are the basic steps for setting up the RMF topology for generating AWR on the physical standby:
1. Configure database nodes to add to the topology.
2. Create the topology.
3. Register database nodes with the topology.
4. Create remote snapshots.
5. Generate the AWR.

In this example, we set the repository in the primary database; therefore it is called the target system. The standby database is called the source system.
In the following example the primary database name is: pdb and the standby database name is: sdb. For clarify, the sqlprompt was changed from SQL> to the database role Primary/Standby (for example: set sqlprompt "PRIMARY> ")
The RMF APIs are declared in the PL/SQL package DBMS_UMF. All the AWR-related operations in RMF can be performed only by the SYS$UMF user. Since the SYS$UMF user is locked by default, it must be unlocked before deploying the RMF topology:

PRIMARY> alter user sys$umf identified by sysumf account unlock;

Create the database link between the primary database and the standby database and vice versa:

PRIMARY> create DATABASE LINK DBLINK_pdb_to_sdb CONNECT TO sys$umf IDENTIFIED BY sysumf using 'sdb';
PRIMARY> create DATABASE LINK DBLINK_sdb_to_pdb CONNECT TO sys$umf IDENTIFIED BY sysumf using 'pdb';

Each database node in a topology must be assigned a unique name (default is DB_UNIQUE_NAME):

PRIMARY> exec DBMS_UMF.configure_node ('PDB');

Since the standby database is remote to the target system (the primary database), we can register it via the corresponding database link:

STANDBY> exec DBMS_UMF.configure_node ('sdb', 'DBLINK_sdb_to_pdb');

Create the RMF topology and designate the node on which it is executed as the destination node for that topology:

PRIMARY> exec DBMS_UMF.create_topology ('Topology_1');
PRIMARY> select * from DBA_UMF_TOPOLOGY;
PRIMARY> select * from DBA_UMF_REGISTRATION;

Register the standby database in the AWR using the RMF:

PRIMARY> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'sdb');
PRIMARY> select * from DBA_UMF_SERVICE;

Create a remote snapshot using the RMF:

PRIMARY> exec DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT('sdb');

Create AWR Report.

PRIMARY> @?/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

Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
3810102760 1 PDB sdb Local_host
* 3393159014 1 PDB pdb Remote_Host
Enter value for dbid: 3810102760

Source:
Gathering Database Statistics - https://docs.oracle.com/database/122/TGDBA/gathering-database-statistics.htm#TGDBA232
DBMS_UMF - https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_UMF.html
DBMS_WORKLOAD_REPOSITORY - https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_WORKLOAD_REPOSITORY.html
Oracle Active Data Guard: Power, Speed, Ease, and Protection - http://www.oracle.com/technetwork/database/availability/con6531-oracle-active-data-guard-3334919.pdf