Generating AWR report from Active Dataguard

Oracle has introduced a new feature that can generate AWR report from Physical Standby database if we have enabled active dataguard from 12.2 onwards.  

To enable AWR reporting from a physical standby database perform below steps.  

1. Unlock SYS$UMF user and change its password as SYS user. SYS$UMF account is used to administer Remote Management Framework, including the remote Automatic Workload Repository (AWR). It is the default database user that has all the privileges to access the system-level RMF views and tables.

alter user sys$umf identified by <password> account unlock;

2.  Create the database link from primary to standby and standby to primary.

create database link primary_to_standby connect to "SYS$UMF" identified by "<password>" using 'orcl';
create database link standby_to_primary connect to "SYS$UMF" identified by "<password>" using 'orclsby'; 

3.  Validate the database links

select * from dual@primary_to_standby
select * from dual@standby_to_primary;

4.  Make sure the parameter "_umf_remote_enabled" is set to TRUE. If not you will receive “ORA-20501: Remote UMF is disabled” while performing any UMF related operation on the database.

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

5. The RMF topology is a centralized architecture that consists of all the participating database nodes along with their metadata and connection information. 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. 

Note that each database name must be assigned a unique name. Default name is db_unique_name. In this case orcl and orclsby.

On Primary :

exec dbms_umf.configure_node ('orcl');

On Standby :

exec dbms_umf.configure_node ('orclsby','standby_primary');

6. Create RMF topology using the procedure DBMS_UMF.CREATE_TOPOLOGY.

exec DBMS_UMF.create_topology ('Topology_orcl');

7. Validate the configuration on primary. 

select * from dba_umf_topology; 
select * from dba_umf_registration;
select * from dba_umf_link;

8. Register the standby database with topology using  DBMS_UMF.REGISTER_NODE procedure.  

 

exec DBMS_UMF.register_node ('Topology_orcl', 'orclsby', 'primary_standby', 'standby_primary', 'FALSE', 'FALSE');

9.Register the standby node for the AWR service using DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE procedure from primary database. 

exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'orclsby');

10. Validate the configuration using on primary again. 

select * from dba_umf_topology; 
select * from dba_umf_registration;
select * from dba_umf_link;

11. Once the setup is completed generate some remote snapshots by executing below command from primary database. (Minimum two snapshots are required to generate an AWR). 

alter system switch logfile;
exec dbms_workload_repository.create_remote_snapshot('orclsby');

12. Now you can generate AWR for standby either from primary or standby database.

@?/rdbms/admin/awrrpti.sql