Be careful when you resize a datafile in a Data Guard configuration

If you resize a system datafile on a primary database in a Data Guard configuration, the media recovery process on the standby will shutdown and stop applying redo ...

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:

 

SELECT * FROM V$DATAGUARD_STATS WHERE NAME='apply lag';

 

NAME VALUE TIME_COMPUTED

----------------------------------------------------------

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:

 

 

 

Script to monitor data guard log shipping

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12

SELECT DB_NAME,
       HOSTNAME,
       LOG_ARCHIVED,
       LOG_APPLIED,
       APPLIED_TIME,
       LOG_ARCHIVED - LOG_APPLIED LOG_GAP
  FROM (SELECT NAME DB_NAME FROM V$DATABASE),
       (SELECT UPPER (
                  SUBSTR (
                     HOST_NAME,
                     1,
                     (DECODE (INSTR (HOST_NAME, '.'),
                              0, LENGTH (HOST_NAME),
                              (INSTR (HOST_NAME, '.') - 1)))))
                  HOSTNAME
          FROM V$INSTANCE),
       (SELECT MAX (SEQUENCE#) LOG_ARCHIVED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 1 AND ARCHIVED = 'YES'),
       (SELECT MAX (SEQUENCE#) LOG_APPLIED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES'),
       (SELECT TO_CHAR (MAX (COMPLETION_TIME), 'DD-MON/HH24:MI') APPLIED_TIME
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES');