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;

SQL> select 1 from dual@standby_to_primary;


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.

   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.

   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_1	4040050753		  8 ACTIVE

SQL> col node_name format a20

-------------------- -------------------- ---------- ---------- ----- -----  -----
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.


   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


procedure registers a remote database in the Automatic Workload Repository (AWR) using the Remote Management Framework (RMF).

   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_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.


function and procedure create a remote snapshot using the Remote Management Framework (RMF). The function returns the snapshot ID.

   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 :

Skant Gupta’s LinkedIn:

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp



About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.