Oracle introduced Remote Management Framework (RMF) in 12.2 which allows creating AWR reports on standby database when
active data guard is in use. This post list the steps for setting up the RMF so AWR reports could be generated on standby. The post use the data guard configuration set up on 18.3, which is mentioned in a
previous post. Current data guard setup and standby open mode is as follows.
DGMGRL> show configuration
Configuration - paas_iaas_dg
Protection Mode: MaxAvailability
Members:
fradb_fra1kk - Primary database
londb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 11 seconds ago)
DGMGRL> show database londb
Database - londb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: ON
Instance(s):
lonDB
Database Status:
SUCCESSOracle provide a pre-created user sys$umf (locked by default) which has all the necessary privileges to carry out the RMF related work. Unlock this user from primary DB and set a password.
alter user sys$umf identified by rmfuser account unlock;
Next create two database link that sys$umf user will use to connect to and from the standby DB. The TNS entries used here were created in the
previous post. Before creating database link take a note of the global_names parameter. If this is set to true, then DB links are expected to be the same name as the DB they connect to. If not ORA-02085 error could be encountered when trying to use the DB links.
create database link fra_to_lon CONNECT TO sys$umf IDENTIFIED BY rmfuser using 'LONDBTNS';
create database link lon_to_fra CONNECT TO sys$umf IDENTIFIED BY rmfuser using 'FRADBTNS';
Check the DB links are working by querying remote instance using them. Run both on primary and standby.
SQL> select instance_name from v$instance@fra_to_lon;
INSTANCE_NAME
----------------
lonDB
SQL> select instance_name from v$instance@lon_to_fra;
INSTANCE_NAME
----------------
fraDB
On primary run the following to configure the primary node with RMF. Configure node require a unique name for each node configured. If none is provided, the db_unique_name will be used instead.
SQL> exec dbms_umf.configure_node ('fraDB');
PL/SQL procedure successfully completed.On standby run the following to configure the standby with RMF. In this case a unique name for standby and the db link name from standby to primary is given as inputs.
SQL> exec dbms_umf.configure_node ('lonDB','lon_to_fra');
PL/SQL procedure successfully completed.Create the RMF topology by running following on primary.
SQL> exec DBMS_UMF.create_topology ('FRA_LON_TOPOLOGY');
PL/SQL procedure successfully completed.
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
-------------------- ---------- ---------------- --------
FRA_LON_TOPOLOGY 1423735874 1 ACTIVEView the registered nodes. Only primary is registered so far.
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
-------------------- ---------- ---------- ---------- ----- ----- --------------------
FRA_LON_TOPOLOGY fraDB 1423735874 0 FALSE FALSE OK
Register the standby with the topology. The meaning of the input parameters could be found
here. Execute the following on primary.
exec DBMS_UMF.register_node ('FRA_LON_TOPOLOGY', 'lonDB', 'fra_to_lon', 'lon_to_fra', 'FALSE', 'FALSE');Check both nodes are registered.
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
-------------------- ---------- ---------- ---------- ----- ----- -----
FRA_LON_TOPOLOGY fraDB 1423735874 0 FALSE FALSE OK
FRA_LON_TOPOLOGY lonDB 4041047630 0 FALSE FALSE OK
Register the AWR service on the remote node.
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'lonDB');
PL/SQL procedure successfully completed.
Verify AWR service is active on the remote node
SQL> select * from dba_umf_service;
TOPOLOGY_NAME NODE_ID SERVICE
-------------------- ---------- -------
FRA_LON_TOPOLOGY 4041047630 AWR
To generate AWR report create two snapshots on the remote database.
SQL> exec dbms_workload_repository.create_remote_snapshot('lonDB');
PL/SQL procedure successfully completed.Once a snapshot is created the standby DB is listed in the AWR with the DB ID that is equal to the node ID (highlighted above) in the UMF registration.
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
1042410484 1 FRADB lonDB lonvm
4041047630 1 FRADB lonDB lonvm
* 1042410484 1 FRADB fraDB fravm
Enter value for dbid: 4041047630
The AWR instance report generated would list the role as physical standby.
The AWR control view list the standby database as well.
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- ----------
1042410484 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 0 1042410484
4041047630 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 1042410484 lonDBThe remote snapshots will be automatically taken according to snapshot internal.
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
1042410484 1 FRADB lonDB lonvm
4041047630 1 FRADB lonDB lonvm
* 1042410484 1 FRADB fraDB fravm
Enter value for dbid: 4041047630
Using 4041047630 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 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
------------ ------------ ---------- ------------------ ----------
lonDB FRADB 1 29 Nov 2018 13:11 1
2 29 Nov 2018 13:59 1
3 29 Nov 2018 14:58 1
4 29 Nov 2018 15:58 1
However, after a switchover, when roles changes the automatic snapshot taking will stop, both on new primary (old standby) and new standby (old primary). In order to automatic AWR snapshot to continue do the following after a switchover. As the first step unregistered the new primary (old standby) as a remote database.
SQL> exec DBMS_WORKLOAD_REPOSITORY.UNREGISTER_REMOTE_DATABASE('lonDB','FRA_LON_TOPOLOGY',false);
PL/SQL procedure successfully completed.Drop the topology
exec DBMS_UMF.drop_topology('FRA_LON_TOPOLOGY');
PL/SQL procedure successfully completed.Run un-configure procedure on each node
SQL> exec DBMS_UMF.UNCONFIGURE_NODE;
PL/SQL procedure successfully completed.
Re-create the topology again with new primary and standby. On new primary
SQL> exec dbms_umf.configure_node ('lonDB');
PL/SQL procedure successfully completed.On new standby
SQL> exec dbms_umf.configure_node ('fraDB','fra_to_lon');
PL/SQL procedure successfully completed.Same topology name is used
exec DBMS_UMF.create_topology ('FRA_LON_TOPOLOGY');Register new standby
exec DBMS_UMF.register_node ('FRA_LON_TOPOLOGY', 'fraDB', 'lon_to_fra', 'fra_to_lon', 'FALSE', 'FALSE');Register remote DB for AWR by specifying the node ID of the DB shown in dba_umf_registration earlier. If the remote DB registration was to be done using DB name as before then "
ORA-13516: AWR Operation failed: ORA-13516: AWR Operation failed: Remote source not registered for AWR" would be encountered when snapshots are taken. Doing log switches as suggested by 2409808.1 did not resolve this issue.
exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(1423735874);
PL/SQL procedure successfully completed.
Once remote DB is registered remote snapshots could be taken
SQL> exec dbms_workload_repository.create_remote_snapshot('fraDB');
PL/SQL procedure successfully completed.After the role reversal, automatic snapshots will continue on the snapshot interval. Run AWR instance reports same as before.
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
1042410484 1 FRADB fraDB fravm
1423735874 1 FRADB fraDB fravm
* 1042410484 1 FRADB lonDB lonvm
Enter value for dbid: 1423735874
The new primary (old standby) will be reflected on the AWR report taken against it.
Useful Metalink Note
How to Generate AWRs in Active Data Guard Standby Databases [ID 2409808.1]
Related Posts
Enabling Automatic AWR Snapshots on PDB
Update on 2020-07-13
Oracle documentation now has a separate section on "Managing ADG Role Transition". The doc shows the use of DBMS_UMF.SWITCH_DESTINATION for role reversal scenarios.
