Setup AWR on Standby DB

 Starting with Oracle 12.2, AWR can be configured and run against Standby DB, before that release (since 11gR2) the only option available for having similar data was by generating an ASH report on the Standby DB, which was limited by how much data is still available in the standby instance memory.

Here are the briefed steps of how to set up AWR on the standby DB (Steps are the same for both RAC & standalone DB):

[On Primary]: Unlock user sys$umf

SQL> alter user sys$umf identified by "abc#123#ORCL" account unlock;


[On Primary]: Enable "_umf_remote_enabled" parameter:

SQL> alter system set "_umf_remote_enabled"=TRUE scope=BOTH;


[On Primary]: Create two DB links, from primary to standby and vice versa:

SQL> drop database link PR_TO_STBY;
SQL> drop database link STBY_TO_PR;

SQL> create database link PR_TO_STBY CONNECT TO sys$umf IDENTIFIED BY "abc123" using 'ORCL';
SQL> create database link STBY_TO_PR CONNECT TO sys$umf IDENTIFIED BY "abc123" using 'ORCLSTBY';
 

--Make sure the DB Links are working:

select * from dual@PR_TO_STBY;

D
-
X

select * from dual@STBY_TO_PR;

D
-
X
 

[On Primary] Add the primary DB to the new AWR Topology:

SQL> exec dbms_umf.unconfigure_node;
SQL> exec dbms_umf.configure_node ('prim');


[On Standby] Add the Standby DB to the new AWR Topology:

SQL> exec dbms_umf.UNCONFIGURE_NODE;
SQL> exec dbms_umf.configure_node ('stby', 'STBY_TO_PR');

[On Primary]: Create a new Topology:

SQL> exec DBMS_UMF.drop_topology   ('Topology_1');
SQL> exec DBMS_UMF.create_topology ('Topology_1');


[On Primary]: Register the standby database with the topology:

SQL> exec DBMS_UMF.register_node ('Topology_1', 'stby', 'PR_TO_STBY', 'STBY_TO_PR', 'FALSE', 'FALSE');

[On Primary]: Register the Standby DB as a remote DB: [May take from 2-75 seconds]

SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

If you encounter "ORA-13519": Try to unregister and register the DB again:
SQL> exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database('stby','Topology_1',TRUE);
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

If encounter "ORA-20509: Service not registered": Run the following commands in sequence:
SQL> exec DBMS_UMF.unregister_node ('Topology_1', 'stby');
SQL> exec DBMS_UMF.register_node ('Topology_1', 'stby', 'PR_TO_STBY', 'STBY_TO_PR', 'FALSE', 'FALSE');
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

[On Primary]: Verify the above steps by running these queries:

SQL>     set line 170
    col topology_name format a15
    col node_name       format a15
    select * from dba_umf_topology;
    select TOPOLOGY_NAME, NODE_NAME, NODE_ID, NODE_TYPE from umf$_registration;
    select * from dba_umf_registration;


[On Primary]: Create a Snapshot on the Standby DB:

SQL> alter system archive log current;
SQL> exec dbms_workload_repository.create_remote_snapshot('stby');

Note: By default one snapshot will be created automatically every one hour on the Standby DB.

[On Primary or Standby]: To generate an AWR report for the standby DB:

SQL> @?/rdbms/admin/awrrpti
When asked for dbid, enter the DBID of the Standby DB (from the list).

 

Change the AWR SNAPSHOT Frequency on the Standby:

Unfortunately, the only way to change the AWR Snapshot frequency is to change the frequency on the Primary, then it will reflect on the Standby!!:

-- Changing the AWR Snapshot Frequency to 30 minutes on both Primary & Standby:

SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30);

[Up to the date of writing this post; Oracle didn't provide a way to change the SNAPSHOT frequency on Standby without impacting the Snapshot frequency on the Primary]
 

Note: Whenever a SWITCHOVER happen between primary and standby you have to do a similar role switch for AWR settings as well using this command:

SQL> exec DBMS_UMF.SWITCH_DESTINATION(topology_name =>'Topology_1',force_switch=> 'TRUE');

In case the above command failed after the switchover and the snapshots are not getting automatically generated on the new primary DB, try to re-deploy all the above mentioned steps again to re-configuring AWR on the new standby.

References:
https://www.oracle.com/technetwork/database/availability/redo-apply-2745943.pdf