Wednesday, September 15, 2021

Oracle 12cR2/18c/19c - Generate AWRs in Active Data Guard (ADG) Physical Standby Databases

Generate AWRs in Active Data Guard (ADG) Physical Standby Databases

Starting with Oracle 12cR2 (12.2), Automatic Workload Repository (AWR) data can be captured for ADG standby databases. This feature enables analyzing any performance-related issues for ADG standby databases.

Environment: 
------------------

1. Primary RAC: Oracle 19c (19.3) GI, Oracle 19c (19.12) RU and same for RDBMS
2. Standby RAC: Oracle 19c (19.3) GI, Oracle 19c (19.12) RU and same for RDBMS
3. Operating System: Oracle Linux Server 7.9

1. Confirm the open mode and the database role on the standby:

SQL> select distinct instance_name,database_role,protection_mode,flashback_on,status from gv$database,gv$instance;

INSTANCE_NAME   DATABASE_ROLE        PROTECTION_MODE      FLASHBACK_ON         STATUS
--------------- -------------------- -------------------- -------------------- ----------
orcls1          PHYSICAL STANDBY     MAXIMUM PERFORMANCE  YES                  OPEN
orcls2          PHYSICAL STANDBY     MAXIMUM PERFORMANCE  YES                  OPEN

2. On Primary RAC Database 

The SYS$UMF user is the default database user that has all the privileges to access the system-level Remote Management Framework (RMF) views and tables. 

All the AWR related operations in RMF can be performed only by the SYS$UMF user. The SYS$UMF user is locked by default and it must be unlocked before deploying the RMF topology:

SQL> alter user sys$umf identified by sysumf account unlock;
User altered.

3. Create the database link between the primary database and the standby database and vice versa:

SQL> create database link dbl_orcl_to_orcls CONNECT TO sys$umf IDENTIFIED BY sysumf using 'orcls';
Database link created.

SQL> create database link dbl_orcls_to_orcl CONNECT TO sys$umf IDENTIFIED BY sysumf using 'orcl';
Database link created.

4. We need to configure database nodes to add to the topology. Each database node in a topology must be assigned a unique name (default is DB_UNIQUE_NAME):

Example we have chosen "primary" for primary and "stanby" for standby.

SQL> alter system set "_umf_remote_enabled"=TRUE scope=BOTH;
System altered.

SQL> exec dbms_umf.configure_node ('primary');
PL/SQL procedure successfully completed.

5. The standby database is remote to the target system (the primary database). We can register it via the corresponding database link.

On Physical Standby Database:

SQL> exec dbms_umf.configure_node ('stanby','dbl_orcls_to_orcl');
PL/SQL procedure successfully completed.
SQL>

6. Create the RMF topology:

SQL> exec DBMS_UMF.create_topology ('Topology1');
PL/SQL procedure successfully completed.
SQL>

7. Verify the steps done at primary RAC database

SQL> select * from dba_umf_topology;
TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology1        509936611                1 ACTIVE

SQL> select * from dba_umf_registration;
TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology1       primary          509936611          0 FALSE FALSE OK
SQL>

8. Register the standby database with the topology

SQL> exec DBMS_UMF.register_node ('Topology1', 'stanby', 'dbl_orcl_to_orcls', 'dbl_orcls_to_orcl', 'FALSE', 'FALSE');
PL/SQL procedure successfully completed.
SQL>

9. Enable the AWR service on the node:

SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stanby');
PL/SQL procedure successfully completed.

10. Verify all the queries 

SQL> select * from dba_umf_topology;
TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology1        509936611                7 ACTIVE
SQL>

SQL> select * from dba_umf_registration;
TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology1       primary          509936611          0 FALSE FALSE OK
Topology1       stanby          3184583509          0 FALSE FALSE OK
SQL>

SQL> select * from dba_umf_service;
TOPOLOGY_NAME      NODE_ID SERVICE
--------------- ---------- -------
Topology1       3184583509 AWR
SQL>

11. Create a remote snapshot using the RMF

SQL> exec dbms_workload_repository.create_remote_snapshot('stanby');
PL/SQL procedure successfully completed.
Note: We need to run at least two to get the begin_snap and end_snap.

12. Switch the logfiles at Primary RAC Database

SQL> alter system switch all logfile;
System altered.

SQL> alter system switch all logfile;
System altered.

SQL> alter system switch all logfile;
System altered.

13. Login to Physical Standby Database and execute AWR reports

SQL> select distinct instance_name,database_role,protection_mode,flashback_on,status from gv$database,gv$instance;

INSTANCE_NAME   DATABASE_ROLE        PROTECTION_MODE      FLASHBACK_ON         STATUS
--------------- -------------------- -------------------- -------------------- ----------
orcls1          PHYSICAL STANDBY     MAXIMUM PERFORMANCE  YES                  OPEN
orcls2          PHYSICAL STANDBY     MAXIMUM PERFORMANCE  YES                  OPEN

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: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1610181442     1      ORCL         orcl1        oel70-base1
  1610181442     2      ORCL         orcl2        oel70-base2
  3184583509     1      ORCL         orcls1       oel70-base3
  3184583509     2      ORCL         orcls2       oel70-base4
Enter value for dbid: 3184583509
Using 3184583509 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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 5
Listing the last 5 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------
orcls1       ORCL                 1  14 Sep 2021 23:44    1
                                  2  15 Sep 2021 01:01    1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End   Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_2.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awrrpt_orcls1.html
14. Execute for Instance-2:

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: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  1610181442     1      ORCL         orcl1        oel70-base1
  1610181442     2      ORCL         orcl2        oel70-base2
  3184583509     1      ORCL         orcls1       oel70-base3
  3184583509     2      ORCL         orcls2       oel70-base4
Enter value for dbid: 3184583509
Using 3184583509 for database Id
Enter value for inst_num: 2
Using 2 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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------
orcls2       ORCL                 1  14 Sep 2021 23:47    1
                                  2  15 Sep 2021 01:03    1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End   Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_2_1_2.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: /home/oracle/awrrpt_orcls2.html
Using the report name /home/oracle/awrrpt_orcls2.html











Hope it helps....


Please refer for more details:
How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1)

No comments:

Post a Comment