As you know, generating AWR reports in an Active Data Guard (ADG) environment is not normally possible. This is logically due to the inability to create snapshots on a database that is in read-only mode. In Oracle 12c Release 2, a solution was introduced, and it became possible to generate AWR reports for ADG databases.
In this new feature, a snapshot is first created and workload data from the Data Guard database is stored in a database that is in read-write mode (there is no mandatory requirement to use the primary database for this). After a defined time interval, a second snapshot is created, and the data collection is completed. Finally, the AWR report can be generated. In the following sections, we explain how to perform this process.
This capability (remote snapshot) uses the Remote Management Framework (RMF). Before generating the AWR report, several steps are required to create and configure the RMF topology. These steps, along with the other required procedures, are described below.
Step 1: Check the Status of the SYS$UMF User
SQL> select username,account_status from dba_users where username=‘SYS$UMF’;
USERNAME ACCOUNT_STATUS
SYS$UMF EXPIRED & LOCKED
As shown, this user is expired and locked by default. In the first step, it must be opened:
SQL> alter user sys$umf identified by a account unlock;
User altered
Step 2: Create Two-Way Database Links Between Primary and ADG
For this test, the primary database is used as the source (read-write database).
First, configure the tnsnames.ora file on both the primary and ADG databases:
ADG=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ADG_HOST)(PORT = 1521))
)
(CONNECT_DATA =
(sid=ADG)
)
)
PRIM=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIM_HOST)(PORT = 1521))
)
(CONNECT_DATA =
(sid=PRIM)
)
)
After configuring tnsnames.ora, create the database links:
–PRIMARY Database
SQL> create database link PRIM_TO_ADG CONNECT TO sys$umf IDENTIFIED BY root using ‘ADG’;
Database link created.
SQL> create database link ADG_TO_PRIM CONNECT TO sys$umf IDENTIFIED BY root using ‘PRIM’;
Database link created.
Test the database links:
SQL> select database_role from v$database@PRIM_TO_ADG;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
SQL> select database_role from v$database@ADG_TO_PRIM;
DATABASE_ROLE
—————-
PRIMARY
Step 3: Configure RMF Nodes
Before creating the RMF topology, each database must be assigned a unique node name.
— primary database:
SQL> exec dbms_umf.configure_node (‘PRIM_NODE’);
PL/SQL procedure successfully completed.
**— ADG Standby:**
SQL> exec dbms_umf.configure_node(‘ADG_NODE’,’ADG_TO_PRIM’);
PL/SQL procedure successfully completed.
Step 4: Create the RMF Topology
— primary database:
SQL> exec DBMS_UMF.create_topology (‘TOPOL1’);
PL/SQL procedure successfully completed.
To view the list of topologies:
select * from dba_umf_topology;
TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY_STATE
TOPOL1 3967923579 1 ACTIVE
Step 5: Register the Standby Node in the Topology
SQL> exec DBMS_UMF.register_node (‘TOPOL1’, ‘ADG_NODE’, ‘PRIM_TO_ADG’, ‘ADG_TO_PRIM’, ‘FALSE’, ‘FALSE’);
PL/SQL procedure successfully completed.
Step 6: Enable the AWR Service for Data Guard
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>’ADG_NODE’);
PL/SQL procedure successfully completed.
Verify the topology status:
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SOURCE AS_CANDIDATE_TARGET STATE
TOPOL1 PRIM_NODE 3967923579 0 FALSE FALSE OK
TOPOL1 ADG_NODE 1195199270 0 FALSE FALSE OK
SQL> select * from dba_umf_service;
TOPOLOGY_NAME NODE_ID SERVICE_ID
TOPOL1 1195199270 AWR
SQL> select * from dba_umf_link;
TOPOLOGY_NAME FROM_NODE_ID TO_NODE_ID LINK_NAME
TOPOL1 3967923579 1195199270 PRIM_TO_ADG
TOPOL1 1195199270 3967923579 ADG_TO_PRIM
Step 7: Create Remote Snapshots from ADG
SQL> exec dbms_workload_repository.create_remote_snapshot(‘ADG_NODE’);
PL/SQL procedure successfully completed.
The first snapshot for ADG is now created in the primary environment. After generating workload on ADG and waiting for a suitable interval, create the second snapshot:
SQL> exec dbms_workload_repository.create_remote_snapshot(‘ADG_NODE’)
PL/SQL procedure successfully completed.
Step 8: Generate the 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:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ———- ——— ———- ——
1195199270 1 ORCL ADG ADG_HOST
* 1112303074 1 ORCL PRIM PRIM_HOST
Enter value for dbid: 1195199270
Using 1195199270 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: 1
Listing the last day’s Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
———— ———— ———- —————— ———-
ADG ORCL 1 30 Sep 2018 15:47 1
2 30 Sep 2018 15:55 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/awrstb970708.html
Using the report name /home/oracle/awrstb970708.html
A portion of the AWR report output is shown below.
