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 OKRegister 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 OKRegister 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: 4041047630The AWR instance report generated would list the role as physical standby.
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. Pressingwithout 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 topologyexec DBMS_UMF.drop_topology('FRA_LON_TOPOLOGY');
PL/SQL procedure successfully completed.Run un-configure procedure on each nodeSQL> 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 standbySQL> exec dbms_umf.configure_node ('fraDB','fra_to_lon');
PL/SQL procedure successfully completed.Same topology name is usedexec DBMS_UMF.create_topology ('FRA_LON_TOPOLOGY');Register new standbyexec 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
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.
