AWR on ADG
Active Data Guard is a lot more than the read-only standby database. Let’s focus on this part a bit. Read-only workloads are select workloads and are just queries.
Working on a read-only database where redo apply is running results in a complex mechanism of keeping data up to date and combining it with user queries. From time to time, you can’t help but question the performance of the queries or how the overall standby database is performing. What could possibly be impacting the performance of these queries?
We have a very good MOS-note (2409808.1) describing how to gather AWR data in regards to the standby database.
Let’s run through this note.
First step is to confirm that your standby database is running in read-only mode with redo apply active, so in a correct Active Data Guard state.
|
1 2 3 4 5 6 7 |
SQL> select inst_id, open_mode, database_role from gv$database order by 1; INST_ID OPEN_MODE DATABASE_ROLE ---------- -------------------- ---------------- 1 READ ONLY WITH APPLY PHYSICAL STANDBY SQL> |
In the primary database, we need the sys$umf user to be unlocked. This user 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select inst_id, open_mode, database_role from gv$database order by 1; INST_ID OPEN_MODE DATABASE_ROLE ---------- -------------------- ---------------- 1 READ WRITE PRIMARY SQL> alter user sys$umf identified by sysumf account unlock; User altered. SQL> alter user sys$umf identified by "Welc0me2##"; User altered. SQL> |
Next step is to configure some database links both from and to the standby and the primary database.
|
1 2 3 4 5 6 7 8 9 |
SQL> create database link dbl_dgdemovm1_to_dgdemovm2 connect to sys$umf identified by "Welc0me2##" using 'dgdemovm2'; Database link created. SQL> create database link dbl_dgdemovm2_to_dgdemovm1 connect to sys$umf identified by "Welc0me2##" using 'dgdemovm1'; Database link created. SQL> |
On the primary database, we need to tell the framework that is has several nodes.
|
1 2 3 4 5 |
SQL> exec dbms_umf.configure_node ('dgdemovm1'); PL/SQL procedure successfully completed. SQL> |
From the perspective of the primary database, the standby database is seen as a remote database. Therefore, we need to configure it to the framework that way as well. So, on the standby db, we tell it it belongs to the primary via db-link.
|
1 2 3 4 5 |
SQL> exec dbms_umf.configure_node ('dgdemovm2','dbl_dgdemovm2_to_dgdemovm1'); PL/SQL procedure successfully completed. SQL> |
Back to the primary, we need to create a topology:
|
1 2 3 4 5 |
SQL> exec DBMS_UMF.create_topology ('my_dgdemo_topology'); PL/SQL procedure successfully completed. SQL> |
When querying the primary database, we find that it has been correctly registered in the newly created topology:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select * from dba_umf_topology; TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY -------------------- ---------- ---------------- -------- my_dgdemo_topology 4050676483 1 ACTIVE SQL> select * from dba_umf_registration; TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE -------------------- --------------- ---------- ---------- ----- ----- -------------------- my_dgdemo_topology dgdemovm1 4050676483 0 FALSE FALSE OK SQL> |
But there is no standby yet, so we need to add it to the topology as well. This is done on the primary:
|
1 2 3 4 5 |
SQL> exec DBMS_UMF.register_node ('my_dgdemo_topology','dgdemovm2','dbl_dgdemovm1_to_dgdemovm2','dbl_dgdemovm2_to_dgdemovm1'); PL/SQL procedure successfully completed. SQL> |
Next step is that we need to enable the service:
|
1 2 3 4 5 |
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'dgdemovm2'); PL/SQL procedure successfully completed. SQL> |
And then we can verify if all went as expected:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> select * from dba_umf_topology; TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY -------------------- ---------- ---------------- -------- my_dgdemo_topology 4050676483 4 ACTIVE SQL> select * from dba_umf_registration; TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SO AS_CA STATE -------------------- --------------- ---------- ---------- ----- ----- -------------------- my_dgdemo_topology dgdemovm1 4050676483 0 FALSE FALSE OK my_dgdemo_topology dgdemovm2 1944231250 0 TRUE FALSE OK SQL> select * from dba_umf_service; TOPOLOGY_NAME NODE_ID SERVICE -------------------- ---------- ------- my_dgdemo_topology 1944231250 AWR SQL> |
At this point, we have 2 nodes in this topology.
Now, we need to create snapshots on the standby (at least 2 for a report):
|
1 2 3 4 5 6 7 8 9 |
SQL> exec dbms_workload_repository.create_remote_snapshot('dgdemovm2'); PL/SQL procedure successfully completed. SQL> exec dbms_workload_repository.create_remote_snapshot('dgdemovm2'); PL/SQL procedure successfully completed. SQL> |
The proof of the pudding is in the eating, so we need to create the AWR report from the ADG standby database, but we will do that from the primary.
|
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 |
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 ------------ ---------- --------- ---------- ------ 1944231250 1 DGDEMO dgdemovm2 oelvm2.local * 1025669099 1 DGDEMO dgdemovm1 oelvm1.local Enter value for dbid: 1944231250 Using 1944231250 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 ------------ ------------ ---------- ------------------ ---------- dgdemovm2 DGDEMO 1 02 Jun 2020 05:49 1 2 02 Jun 2020 05:49 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: /tmp/my_demo_report.html |
We then open the AWR report of the standby database:
This shows that when you have purchased the proper licenses, you can easily generate the required AWR information to quickly troubleshoot some performance issues on the standby.
Also, pay attention to the parts of the report:
You can find the ADG recovery statistics as well and their breakdown:
As always, questions, remarks?
find me on twitter @vanpupi