VirtualObjectives

Data Guard Standby Queries

The following a several standard queries used to return various information about the status of Oracle Data Guard Standby server.


Check the Standby database is mounted.

The Standby should be in a MOUNTED state.

If it is not in a MOUNTED state then do the following...


Check the processes are running on the Standby.

The Managed Recovery Process (MRP) should mostly be in a WAIT_FOR_LOG state and occasionally APPLYING_LOG state and then goes back to WAIT_FOR_LOG. This is normal if the gap is only 1. If it is WAIT_FOR_GAP then you may have pending\missing archivelogs.

If there is no MRP process then you will need to start it...


Use the following query to show what the Standby server is and how it is configured...

The following query will show whether the archive logging is paused (defer) or active (enable)...

To enable, the query is...

Check the Oracle alert log for more information.


Determine the archive log gap...

A gap of 1 or 2 is okay. If your Standby server was down for some time then the gap would be high. The gap would be proportional to the duration which the Standby was not communicating with the Primary. Just keep checking the gap to make sure the number goes down.
The number of threads is proportional to the number of nodes in your RAC (if you have one) otherwise it will just be 1.


If you have a large gap then you need to check the sequence numbers...

The difference between ARCH and MRPn should be 1 or less.


The following can be issued on one of the Primary nodes (not Standby) to force an archivelog to be created. Use one, not both at the same time.