Data Guard Standby has fallen behind the Primary

Standby has fallen behind the Primary

I have a primary database, ORADB, which has a Data Guard physical standby, ORADR.  The log apply process runs in ORADR and applies the logs that are shipped from ORADB.
Sometimes, the standby falls behind the primary

en español

Existing Data Guard Setup:

Primary Standby
ORADB ORADR

Determining if Standby has fallen behind the Primary

This query returns the number of hours the standby has fallen behind the primary.  It runs in the standby:

. oraenv
ORACLE_SID = [] ? ORADR
sqlplus / as sysdba

SQL>select  round((sysdate-max(first_time))*(1440/60))
from  V$ARCHIVED_LOG
where
applied=’YES’;

ROUND((SYSDATE-MAX(FIRST_TIME))*(1440/60))
——————————————

                                         4


If the query returns a value greater than zero, the standby has fallen behind the primary.  The standby here is behind by four (4) hours.

Check the standby’s alert log

The standby’s alert log has very useful information.  Check it first before looking at the views.

$ORACLE_BASE/diag/rdbms/oradr/ORADR/trace/alert_ORADR.log:

Fetching gap sequence in thread 2, gap sequence 31966-32065
Wed Sep 04 14:45:27 2013
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Wed Sep 04 14:45:36 2013
Fetching gap sequence in thread 2, gap sequence 31966-32065

The standby needs to apply archived logs with sequences between 31966 and 32065 (inclusive) in thread 2.

Check to see if the archived logs are in the standby:
/ORADR/archivelog/2013_09_04):>ls  -lt *31966*

The archived logs were not shipped over from the primary to the standby.
Find out why:

  • is the apply log process running?
    • ps -ef | grep mrp
      • should return ora_mrp0_ORADR.
    • If not, that is the problem.
    • Restart the log apply process
      • sqlplus / as sysdba (in the standby)
      • alter database recover managed standby database using current logfile disconnect;
  • If the apply log process is running, make sure the logs are available in the primary for shipment.
    • If they are not.  They will need to be restored.  My system uses RMAN to back up archived logs every three hours.
    • Restore the archived logs in the primary:
      • Log into RMAN
        • rman target / catalog rman/****@RMANCAT
      • Restore the missing logs
        • rman>restore archivelog sequence between 31966 and 32065 thread 2;
      • Recheck the standby’s alert log; the “Fetching gap sequence . . .”  messages should have gone away.

TIP: To make sure RMAN keeps the archived logs in the primary until they are applied to the standby, add this command to the primary’s RMAN configuration:

rman target / catalog rman/****@RMANCAT
rman>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s