Pages

Thursday, May 14, 2015

FIND ARCHIVE GAP BETWEEN PRIMARY AND STANDBY

Finding Archive gap between PRIMARY and STANDBY

  
  A Physical Standby database syncs with Primary by continuous apply of archive logs from a Primary Database. When the logs are missing on standby is less than 10-15 numbers, We can simple ship the logs which are missing in the standby database from primary database by using SCP/FTP and then register the log files in standby to resolve the gap.

Use below query on standby Db to see if there is any gap. This will be help full if u have logs shipped and not applied. But in situations when the listener is down or network disturbances. etc the logs wont be shipped so this query might not work.

Run on Primary DB :


Primary: SQL > select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

The primary thread max seq should match standby.

Run on Standby DB:



******************************************************************************
----- More Detailed info with max received seqno# & max applied seqno# included -----
******************************************************************************

select  MAX_RECEIVED_SEQNO,MAX_APPLIED_SEQNO,(MAX_RECEIVED_SEQNO - MAX_APPLIED_SEQNO) as "Difference" from (SELECT 
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED_SEQNO, 
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED_SEQNO
FROM 
  V$ARCHIVE_DEST_STATUS) ARCH ;

Sample output :


SQL> select  MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM
  V$ARCHIVE_DEST_STATUS) ARCH ;  

MAX_RECEIVED          MAX_APPLIED         Difference
------------                                -----------                ----------
       26424                                   26419                       5


*********************************************************************************
----- Simple query  to just display difference-----
*********************************************************************************

select  MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT 
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED, 
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM 
  V$ARCHIVE_DEST_STATUS) ARCH ;

Sample output :


SQL> select (MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM
  V$ARCHIVE_DEST_STATUS) ARCH ; 

Difference
----------
         3


  How to Sync small gap betwen Primary and Standby :

Lets say there is a Gap between the DB's, try to sync them manually if gap is small. Follow below process :

COPY MISSING ARCHIVELOG FILE'S FROM PRIMARY TO STANDBY :

$ scp log_file_1234.arc oracle@standby:/log_location/log_file_1234.arc

NOW REGISTER LOGFILE IN STANDBY DB:

SQL> alter database register logfile ‘/log_location/log_file_n.arc'; 
logfile registered

Repeat the same process for all the log files which are missing at standby.In case the gap is huge You can use Rman backup to cover the gap.

No comments :

Post a Comment