By Mouhamadou Diaw

Since Oracle Database 12c Release 2 (12.2), Automatic Workload Repository (AWR) data can be captured for Active Data Guard (ADG) standby databases. This feature enables analyzing any performance-related issues for ADG standby databases
AWR snapshots for ADG standby databases are called remote snapshots. A database node, called destination, is responsible for storing snapshots that are collected from remote ADG standby database nodes, called sources.
The AWR data captures for Active Data Guard require certain steps that I am trying to describe here. I am using Oracle 18c with following configuration

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DGMGRL> show configuration
Configuration - NONC18C_DR
  Protection Mode: MaxPerformance
  Members:
  NONC18C_SITE1 - Primary database
    NONC18C_SITE2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 30 seconds ago)
DGMGRL>

The primary is opened in Read Write mode and the standby in Read Only Mode With Apply

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select db_unique_name,open_mode, database_role from v$database;
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
NONC18C_SITE2                  READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> select db_unique_name,open_mode, database_role from v$database;
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
NONC18C_SITE1                  READ WRITE           PRIMARY
SQL>

The feature uses the Remote Management Framework which comes with a New Oracle built-in user called SYS$UMF. This user is locked by default and should be unlocked before configuring the RMF.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> select username,common,account_status from dba_users where username like 'SYS%';
USERNAME                       COM ACCOUNT_STATUS
------------------------------ --- --------------------------------
SYS                            YES OPEN
SYSTEM                         YES OPEN
SYSBACKUP                      YES EXPIRED & LOCKED
SYSRAC                         YES EXPIRED & LOCKED
SYSKM                          YES EXPIRED & LOCKED
SYS$UMF                        YES EXPIRED & LOCKED
SYSDG                          YES EXPIRED & LOCKED
7 rows selected.
SQL> alter user sys$umf identified by root account unlock;
User altered.

For the configuration we need in our case 2 database links. Indeed each source must have two database links, a destination-to-source database link and a source-to-destination database link. So connecting on the primary, let’s create 2 database links
=>prima_to_stand: from the primary to the standby
=>stand_to_prima: from standby to the primary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> create database link prima_to_stand CONNECT TO sys$umf IDENTIFIED BY root using 'STBY_NONC';
Database link created.
SQL> create database link stand_to_prima CONNECT TO sys$umf IDENTIFIED BY root using 'PRIMA_NONC';
Database link created.
SQL>
SQL> select * from dual@prima_to_stand;
D
-
X
SQL> select * from dual@stand_to_prima;
D
-
X
SQL>

The RMF topology is a centralized architecture that consists of all the participating database nodes along with their metadata and connection information. So let’s configure the nodes. We will call them “site_prim” for primary and “site_stby” for standby
While connecting on the primary we execute

1
2
3
4
5
SQL> exec dbms_umf.configure_node ('site_prim');
PL/SQL procedure successfully completed.
SQL>

On the standby side we do the same but here we give the database link. Be sure that the database links were created in the right direction, otherwise you will get errors later.

1
2
3
4
5
SQL> exec dbms_umf.configure_node('site_stby','stand_to_prima');
PL/SQL procedure successfully completed.
SQL>

And now from the primary, we can then create the RMF topology.

1
2
3
4
5
SQL> exec DBMS_UMF.create_topology ('Topology_1');
PL/SQL procedure successfully completed.
SQL>

To verify the status of the configuration we can use following UMF views on the primary

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology_1      1530523744                1 ACTIVE
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology_1      site_prim       1530523744          0 FALSE FALSE OK
SQL>

Everything seems fine, so we can register the standby in the topology. On the primary let’s excute the register_node procedure.

1
2
3
4
5
SQL> exec DBMS_UMF.register_node ('Topology_1', 'site_stby', 'prima_to_stand', 'stand_to_prima', 'FALSE', 'FALSE');
PL/SQL procedure successfully completed.
SQL>

If we do not have errors then we can enable the AWR service.

1
2
3
4
5
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'site_stby');
PL/SQL procedure successfully completed.
SQL>

Using UMF views, we can again verify our configuration.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> select * from dba_umf_topology;
TOPOLOGY_NAME    TARGET_ID TOPOLOGY_VERSION TOPOLOGY
--------------- ---------- ---------------- --------
Topology_1      1530523744                4 ACTIVE
SQL> select * from dba_umf_registration;
TOPOLOGY_NAME   NODE_NAME          NODE_ID  NODE_TYPE AS_SO AS_CA STATE
--------------- --------------- ---------- ---------- ----- ----- --------------------
Topology_1      site_prim       1530523744          0 FALSE FALSE OK
Topology_1      site_stby       3265600723          0 FALSE FALSE OK
SQL> select * from dba_umf_service;
TOPOLOGY_NAME      NODE_ID SERVICE
--------------- ---------- -------
Topology_1      3265600723 AWR
SQL>
SQL> select * from dba_umf_link;
TOPOLOGY_NAME   FROM_NODE_ID TO_NODE_ID LINK_NAME
--------------- ------------ ---------- --------------------
Topology_1        1530523744 3265600723 PRIMA_TO_STAND
Topology_1        3265600723 1530523744 STAND_TO_PRIMA
SQL>

It’s now time to generate remote snapshots for the standby. While connecting to the primary. Two snapshots are at least required to be able to generate an AWR report.

1
2
3
4
5
6
7
8
9
10
SQL> set time on
16:01:22 SQL> exec dbms_workload_repository.create_remote_snapshot('site_stby');
PL/SQL procedure successfully completed.
16:21:41 SQL> exec dbms_workload_repository.create_remote_snapshot('site_stby');
PL/SQL procedure successfully completed.
16:21:50 SQL>

And we can generate the report as we usually do

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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
------------ ---------- ---------    ----------   ------
  2315634502     1      NONC18C      NONC18C      standserver1
  3265600723     1      NONC18C      NONC18C      standserver1
* 2315634502     1      NONC18C      NONC18C      primaserver.
Enter value for dbid: 3265600723
Using 3265600723 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  without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------
NONC18C      NONC18C              1  24 Sep 2018 16:01    1
                                  2  24 Sep 2018 16:21    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.txt.  To use this name,
press  to continue, otherwise enter an alternative.
Enter value for report_name:

And viewing the generated report, we can see that the database role is PHYSICAL STANDBY

1
2
3
4
5
6
7
8
9
10
11
12
13
WORKLOAD REPOSITORY report for
DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
NONC18C       3265600723 NONC18C_SIT PHYSICAL STANDBY   EE      18.0.0.0.0 NO  NO
Instance     Inst Num Startup Time
------------ -------- ---------------
NONC18C             1 24-Sep-18 15:49
Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
standserver1.loc Linux x86 64-bit                    1                     2.96

Conclusion
In this blog we have shown how we can use UMF to generate AWR reports in Active Data Guard Instance. The framework UMF use the package DBMS_UMF which has many subprograms. The following note How to Generate AWRs in Active Data Guard Standby Databases (Doc ID 2409808.1) and Oracle documentation will help.

Post Views: 1,696
Thumbnail [60x60]
by
Oracle Team