Thursday, May 23, 2013

How to tune redo apply on a standby database

To maintain close synchronization with the primary database, it is recommended that you enable real time apply on the standby database. This will enable the standby to apply redo changes as they are received, and not wait for a log switch on the primary database. Real time apply is the default If you have created your configuration using the Data Guard broker. Real time apply will have to be enabled manually if the configuration was created using SQL*Plus. 
Query the V$DATAGUARD_STATS view to determine if Redo Apply has recovered all redo that has been received from the primary. For example:
apply lag +00 0:00:04 15-MAY-2005 10:32:49
The query indicates that Redo Apply has not applied the last 4 seconds of redo received on the standby database.
If the apply lag indicates that the standby is behind the primary by a significant amount this could indicate that a gap exist between the standby and the primary. 
A gap occurs when events have prevented the successful transmission of a complete
redo log file. Redo Apply will not be able to proceed if this has occurred until the gap has been resolved, either automatically by Data Guard, or manually by copying
the missing log files and registering them at the standby database should there be
other complications preventing the automated process from completing. To detect if a gap does exist, run the following query on both the primary and standby database and compare the results: