AWR on ADG

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.

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.

Next step is to configure some database links both from and to the standby and the primary database.

On the primary database, we need to tell the framework that is has several nodes.

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.

Back to the primary, we need to create a topology:

When querying the primary database, we find that it has been correctly registered in the newly created topology:

But there is no standby yet, so we need to add it to the topology as well. This is done on the primary:

Next step is that we need to enable the service:

And then we can verify if all went as expected:

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):

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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

three × two =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: