The feature uses the Remote Management Framework which comes with a New Oracle built-in user called SYS$UMF. This user is locked by default and should be unlocked before configuring the RMF.
Since Oracle Database 12c Release 2 (12.2), Automatic Workload Repository (AWR) data can be captured for Active Data Guard (ADG) standby databases. This feature enables analyzing any performance-related issues for ADG standby databases
AWR snapshots for ADG standby databases are called remote snapshots. A database node, called destination, is responsible for storing snapshots that are collected from remote ADG standby database nodes, called sources.
Optionally we can also use statspack following below documents
Installing and Using Standby Statspack (Doc ID 454848.1)
Installing and Using Standby Statspack in 12c Multitenant Database (Doc ID 2020285.1)
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).
SQL>alter user sys$umf identified by oracle account unlock;
Step 2: Create the database link from primary to the standby database and standby database to a primary.
Primary database DB_UNIQUE_NAME: Primary
Standby database DB_UNIQUE_NAME: Standby
SQL> create database link primary_to_standby connect to "SYS$UMF" identified by "oracle" using 'Standby';
Database link created.
SQL> create database link standby_to_primary connect to "SYS$UMF" identified by "oracle" using 'Primary';
Database link created.
Step 3: 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.
DBMS_UMF.CONFIGURE_NODE(
node_name IN VARCHAR2 DEFAULT NULL,
dblink_to_target IN VARCHAR2 DEFAULT NULL);
On primary :
alter system set "_umf_remote_enabled"=TRUE scope=BOTH;
SQL> exec dbms_umf.configure_node('Primary');
PL/SQL procedure successfully completed.
On standby :
SQL>exec dbms_umf.configure_node ('Standby','standby_to_primary');
PL/SQL procedure successfully completed.
Step 4: 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.
DBMS_UMF.CREATE_TOPOLOGY( topology_name IN VARCHAR2);
SQL> exec DBMS_UMF.create_topology ('Topology_1');
PL/SQL procedure successfully completed.
Step 5: Check DBA_UMF_REGISTRATION and dba_umf_topology view
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology_1 4040060753 8 ACTIVE
SQL> col node_name format a20
SQL> select * from DBA_UMF_REGISTRATION;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
-------------------- -------------------- ---------- ---------- ----- ----- -----
Topology_1 Primary 4040060753 0 FALSE FALSE OK
Step 6: 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
DBMS_UMF.REGISTER_NODE(
topology_name IN VARCHAR2,
node_name IN VARCHAR2,
dblink_to_node IN VARCHAR2 DEFAULT NULL,
dblink_from_node IN VARCHAR2 DEFAULT NULL,
as_source IN VARCHAR2 DEFAULT 'TRUE',
as_candidate_target IN VARCHAR2 DEFAULT 'FALSE');
SQL> exec DBMS_UMF.register_node ('Topology_1', 'Standby', 'primary_to_standby', 'standby_to_primary', 'FALSE','FALSE');
PL/SQL procedure successfully completed.
Step 7: 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).
DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE(
node_name IN VARCHAR2,
topology_name IN VARCHAR2 DEFAULT NULL);
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'Standby');
PL/SQL procedure successfully completed.
Step 8: Now again verify in dba_umf_registration view
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology_1 Primary 4040060753 0 FALSE FALSE OK
Topology_1 Standby 1978111757 0 FALSE FALSE OK
Step 9: Create snapshot using RMF in the primary database for the remote database.
function and procedure create a remote snapshot using the Remote Management Framework (RMF). The function returns the snapshot ID.
DBMS_WORKLOAD_REPOSITORY.CREATE_REMOTE_SNAPSHOT(
node_id IN NUMBER,
flush_level IN VARCHAR2 DEFAULT 'BESTFIT');
SQL> exec dbms_workload_repository.create_remote_snapshot('Standby');
PL/SQL procedure successfully completed.
Step 10 : 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.
Step 11 : After Role Switchover
Everything should be set now, you need to be aware after a switchover this procedure might not work . We can make the role change by executing the following procedure on the candidate destination
SQL> EXEC DBMS_UMF.SWITCH_DESTINATION(topology name, force_switch=>FALSE);
BUG 21046490 - UMF-ADG: FAILED TO TAKE SNAPSHOTS AFTER SWITCHOVERS IN ADG[This section is not visible to customers.]
Per non-published Bug 28930258, if in a hurry and has not constructed a topology, then check the value of _remote_awr_enabled on the current primary database.
SELECT b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM sys.x$ksppi a,
sys.x$ksppcv b,
sys.x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND a.ksppinm = '_remote_awr_enabled';
If it is equal to TRUE, then simply set it to FALSE.
ALTER SYSTEM SET "_remote_awr_enabled" = FALSE SCOPE = BOTH SID = '*';
This will cause AWR snapshots to be generated automatically again.
-- Run in case want to DROP the topology
SQL> exec DBMS_UMF.drop_topology('NAME-OF-TOPOLOGY');
Known Errors :
We need to run at least two to get the begin_snap and end_snap.
If you encounter "ORA-13516: AWR Operation failed: Remote source not registered for AWR" then manually switch a few (2-3) logfiles on primary:
alter system switch logfile;
If you encounter "ORA-15766: already registered in an RMF topology" unregister the node as below and then rerun "DBMS_UMF.register_node":
exec DBMS_UMF.unregister_node ('Topology_1', 'stby');
PL/SQL procedure successfully completed.
If you encounter "ORA-13519: Database id (1730117407) exists in the workload repository" unregister the remote database as below and then rerun "DBMS_WORKLOAD_REPOSITORY.register_remote_database":
exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database('stby','Topology_1',TRUE);
Views :
set line 132
col topology_name format a15
col node_name format a15
select * from dba_umf_topology;
select * from dba_umf_registration;
select * from dba_umf_service;
select * from dba_umf_link;
References :
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/gathering-database-statistics.html#GUID-E1369092-DA6B-4CF4-B286-69D4A3BDFA1E
How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1)
