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.
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.
******************************************************************************
----- 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.
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