DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Generating AWR Reports in an Active Data Guard using RMF(12.2)

#oracle #database #linux #sql

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)

    )

  )
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Step 4: Create the RMF Topology

— primary database:

SQL> exec DBMS_UMF.create_topology (‘TOPOL1’);

PL/SQL procedure successfully completed.
Enter fullscreen mode Exit fullscreen mode

To view the list of topologies:

select * from dba_umf_topology;

TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY_STATE
TOPOL1 3967923579 1 ACTIVE
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 7: Create Remote Snapshots from ADG 

SQL> exec dbms_workload_repository.create_remote_snapshot(‘ADG_NODE’);

PL/SQL procedure successfully completed.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

A portion of the AWR report output is shown below.

Top comments (0)