Resolve archive gap between PRIMARY and STANDBY

A Physical Standby database synchs 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 logfiles in standby to resolve the gap.

Please use below query to find out archive gap on Standby:

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
 
Thread  Last Sequence Received   Last Sequence Applied     Difference
———-  ———————-                          ———————        ———-  
1                   9545                                             9535                   10
 
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;
 MAX(SEQUENCE#)
————–
9535

COPY ARCHIVELOG FILE TO STANDBY FROM PRIMARY:

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

REGISTER LOGFILE AT STANDBY:

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.

If archive gap difference is huge(say more than 500 logs) , Please check this http://oracledbamasters.wordpress.com/2013/10/22/resolve-huge-archive-gap-primary-standby/

Post a comment or leave a trackback: Trackback URL.

Comments

Trackbacks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: