Data Guard

Resolve huge 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 difference is huge (say more than 500 logs), you have to rebuild the standby database from scratch.
With out rebuild standby database, As an enhancement from 10g, an incremental backup created with BACKUP INCREMENTAL… FROM SCN can be used to refresh the standby database.
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                               8254                                      7954                          300

 

Find the SCN on the PRIMARY:

SQL> select current_scn from v$database;

 

CURRENT_SCN

———–  

242671761

Find the SCN on the STANDBY:

 

SQL> select current_scn from v$database;

CURRENT_SCN

———–  

223771173

Clearly you can see there is difference
Stop and shutdown the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
Shutdown the standby database
SQL> shut immediate
On the primary, take an incremental backup from the SCN number where the standby current value 223771173:

 RMAN> run { allocate channel c1 type disk format ‘/backup/%U.bkp’;

backup incremental from scn 223771173 database;

 }

On the primary, create a new standby controlfile:
SQL> alter database create standby controlfile as ‘/backup/for_standby.ctl’;
Database altered.
Copy the standby controlfile to STANDBY and bring up the standby instance in nomount status with standby controlfile:
SQL> startup nomount
SQL> alter database mount standby database;
Connect to RMAN on STANDBY, Catalog backup files to RMAN using below commands:
$ rman target=/
RMAN> catalog start with ‘/backup’;
PERFORM RECOVER:
RMAN> recover database;
Start managed recovery process:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Check the SCN’s in primary and standby it should be close to each other.
If archive gap difference is minimal or less, Please check thishttp://oracledbamasters.wordpress.com/2013/10/22/resolve-archive-gap-primary-standby/

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/