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

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

Step 3: Check if both database link works properly.

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.

On primary :

On standby :

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.

Step 6: Check DBA_UMF_REGISTRATION and dba_umf_topology view

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.

Syntax

Step 8: Enable AWR service on the remote node

DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE

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

Step 9: Now again verify in dba_umf_registration view

Step 10: Create snapshot using RMF in the primary database for the remote database.

DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT 

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

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.

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 : https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

 

 

Leave a Reply

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