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

By -

 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.

Vahid Yousefzadeh

Written by Vahid Yousefzadeh


I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com

Edit profile

Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more