Oracle 19c : Active Data Guard and AWR Reports

On this activity we are going to generate snapshots on an active standby so we can be able to create AWR reports.

This is our setup:

	DGMGRL> show configuration
	
	Configuration - dg_test19c
	
	  Protection Mode: MaxPerformance
	  Members:
	  test19c - Primary database
	    teststb - Physical standby database 
	
	Fast-Start Failover:  Disabled
	
	Configuration Status:
	SUCCESS   (status updated 50 seconds ago)
	
	DGMGRL> show database 'teststb';
	
	Database - teststb
	
	  Role:               PHYSICAL STANDBY
	  Intended State:     APPLY-ON
	  Transport Lag:      0 seconds (computed 1 second ago)
	  Apply Lag:          0 seconds (computed 1 second ago)
	  Average Apply Rate: 4.00 KByte/s
	  Real Time Query:    ON
	  Instance(s):
	    TESTSTB
	
	Database Status:
	SUCCESS
	
	DGMGRL> 

We need to perform the following steps to enable AWR reporting from a physical standby database.

1:- Unlock SYS$UMF user and reset the password. SYS$UMF account is used to administer Remote Management Framework, including the remote Automatic Workload Repository (AWR).

	SQL>  select username,common,account_status from dba_users where username like 'SYS$UMF%';
	
	USERNAME	     COM ACCOUNT_STATUS
	-------------------- --- --------------------------------
	SYS$UMF 	     YES LOCKED
	
	
	SQL> alter user sys$umf identified by oracle account unlock;
	User altered.
	
	SQL> select username,common,account_status from dba_users where username like 'SYS$UMF%';
	
	USERNAME	     COM ACCOUNT_STATUS
	-------------------- --- --------------------------------
	SYS$UMF 	     YES OPEN

2:- Create dblink from primary to standby database, and standby database to a primary.

        Primary database DB_UNIQUE_NAME: test19c
        Standby database DB_UNIQUE_NAME: TESTSTB



	SQL> create database link primary_to_standby connect to "SYS$UMF" identified by "oracle" using 'TESTSTB';
	Database link created.
	SQL> create database link standby_to_primary connect to "SYS$UMF" identified by "oracle" using 'test19c';
	Database link created.
	 
	SQL> select instance_name from v$instance@primary_to_standby;
	
	INSTANCE_NAME
	----------------
	TESTSTB
	
	SQL> select instance_name from v$instance@standby_to_primary;
	
	INSTANCE_NAME
	----------------
	test19c2

3:- We need to configure the Remote Management Framework (RMF) topology.The RMF is used for collecting performance statistics for an Oracle Database.heck both dblinks work

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.

Usage: 
	DBMS_UMF.CONFIGURE_NODE(
	   node_name          IN VARCHAR2 DEFAULT NULL,
	   dblink_to_target   IN VARCHAR2 DEFAULT NULL);
	
On primary :
	SQL> exec dbms_umf.configure_node('test19c');
	PL/SQL procedure successfully completed.


On standby :
	SQL>exec dbms_umf.configure_node ('TESTSTB','standby_to_primary');
	PL/SQL procedure successfully completed.

4:- Create the RMF topology:

Usage:
	DBMS_UMF.CREATE_TOPOLOGY(
   		topology_name IN VARCHAR2);


	SQL>  exec DBMS_UMF.create_topology ('PRI_TO_STB_TOPO');
	PL/SQL procedure successfully completed.
	SQL> 
	
	
	SQL> select * from dba_umf_topology;
	
	TOPOLOGY_NAME	      TARGET_ID TOPOLOGY_VERSION TOPOLOGY
	-------------------- ---------- ---------------- --------
	PRI_TO_STB_TOPO       863525038 	       1 ACTIVE
	
	
	SQL> select * from DBA_UMF_REGISTRATION;
	
	TOPOLOGY_NAME	     NODE_NAME		NODE_ID  NODE_TYPE AS_SO AS_CA STATE
	-------------------- --------------- ---------- ---------- ----- ----- --------------------
	PRI_TO_STB_TOPO      test19c	      863525038 	 0 FALSE FALSE OK

5:- 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.

Usage

	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 ('PRI_TO_STB_TOPO', 'TESTSTB', 'primary_to_standby', 'standby_to_primary', 'FALSE','FALSE');
	PL/SQL procedure successfully completed.
	
	SQL>  select * from dba_umf_registration;
	
	TOPOLOGY_NAME	     NODE_NAME		NODE_ID  NODE_TYPE AS_SO AS_CA STATE
	-------------------- --------------- ---------- ---------- ----- ----- --------------------
	PRI_TO_STB_TOPO      test19c	      863525038 	 0 FALSE FALSE OK
	PRI_TO_STB_TOPO      TESTSTB	     4038575875 	 0 FALSE FALSE OK
	

6:- Enable AWR service on the remote node and create snapshot:

Usage:
	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=>'TESTSTB');
	PL/SQL procedure successfully completed.


Create a snapshot on standby:

	SQL> exec dbms_workload_repository.create_remote_snapshot('TESTSTB');
	PL/SQL procedure successfully completed.

7:- Change frequency of snapshots (if needed):

Default is the local db:
	SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 15); 
	PL/SQL procedure successfully completed.

For the standby, we need to specify the dbid:
	SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval  =>  15, dbid => 4038575875);
	PL/SQL procedure successfully completed.

Everything should be set now, you need to be aware after a switchover this procedure might not work, so please review documentation and usage of DBMS_UMF.SWITCH_DESTINATION.

Now lets see if this is working.

Lets connect to standby a run this query multiple times to see if it appears in the report:

	SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";
	Session altered.
	
	SQL> select sysdate from dual;
	
		SQL> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";
	Session altered.
	
	SQL> select sysdate from dual;
	
	SYSDATE
	--------------------
	02-NOV-2021 09:46:35
	
	SYSDATE
	--------------------
	02-NOV-2021 09:51:09
	
	SQL> /
	
	SYSDATE
	--------------------
	02-NOV-2021 09:51:10
	
	SQL> /

Create AWR report:

	SQL> @?/rdbms/admin/awrrpti.sql
	
	Specify the Report Type
	~~~~~~~~~~~~~~~~~~~~~~~
	AWR reports can be generated in the following formats.	Please enter the
	name of the format at the prompt. Default value is 'html'.
	
	   'html'	   HTML format (default)
	   'text'	   Text format
	   'active-html'   Includes Performance Hub active report
	
	Enter value for report_type: text
	
	
	
	Type Specified: text
	
	
	Instances in this Workload Repository schema
	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	  DB Id      Inst Num	DB Name      Instance	  Host
	------------ ---------- ---------    ----------   ------
	  2463024481	 2	TEST19C      test19c2	  RACnode2.loc
	  4038575875	 1	TEST19C      TESTSTB	  standbyracno
	* 2463024481	 1	TEST19C      test19c1	  RACnode1.loc
	
	Enter value for dbid: 4038575875
	Using 4038575875 for database Id
	Enter value for inst_num: 1
	Using 1 for instance number
	
	
	Listing the last day's Completed Snapshots
	Instance     DB Name	  Snap Id	Snap Started	Snap Level
	------------ ------------ ---------- ------------------ ----------
	
	TESTSTB      TEST19C		  1  02 Nov 2021 09:19	  1
					  2  02 Nov 2021 09:42	  1
					  3  02 Nov 2021 10:00	  1
	
	

And checking file, we can see we are generating report on the desired target:

	WORKLOAD REPOSITORY report for
	
	DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
	------------ ----------- ----------- ---------------- ------- ---------- --- ---
	TEST19C       4038575875 TESTSTB     PHYSICAL STANDBY EE      19.0.0.0.0 NO  YES
	
	Instance     Inst Num Startup Time
	------------ -------- ---------------
	TESTSTB             1 30-Oct-21 21:37
	
	Container DB Id  Container Name       Open Time
	--------------- --------------- ---------------
	     1498295204 TEST19C_PDB1    31-Oct-21 13:29
	     2463024481 CDB$ROOT        31-Oct-21 01:15
	     3340915237 PDB$SEED        31-Oct-21 01:15
	
	Host Name        Platform                         CPUs Cores Sockets Memory(GB)
	---------------- -------------------------------- ---- ----- ------- ----------
	standbyracnode.l Linux x86 64-bit                    4     4       1      11.42
	
	              Snap Id      Snap Time      Sessions Curs/Sess  PDBs
	            --------- ------------------- -------- --------- -----
	Begin Snap:         2 02-Nov-21 09:42:32        75        .7     3
	  End Snap:         3 02-Nov-21 10:00:10        77        .7     3
	   Elapsed:               17.63 (mins)
	   DB Time:               34.36 (mins)
	
	...
	SQL ordered by Executions                 DB/Inst: TEST19C/TESTSTB  Snaps: 2-3
	-> %CPU   - CPU Time      as a percentage of Elapsed Time
	-> %IO    - User I/O Time as a percentage of Elapsed Time
	-> Total Executions:             997
	-> Captured SQL account for   71.3% of Total
	
	                                              Elapsed
	 Executions   Rows Processed  Rows per Exec   Time (s)  %CPU   %IO    SQL Id
	------------ --------------- -------------- ---------- ----- ----- -------------
	         210             210            1.0        0.0  82.5     0 7h35uxf5uhmm1
	Module: sqlplus@standbyracnode.localdomain (TNS V1-V3)
	   PDB: CDB$ROOT
	select sysdate from dual    <<<<<<--------------------------------------- OUR query