Archive log Gap Solution in Standby

Resolving huge gap beween PRIMARY and STANDBY

STEPS:


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

1.

 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;


2.

Find the SCN on the PRIMARY:

SQL> select current_scn from v$database;

CURRENT_SCN
———– 
242671761

Find the SCN on the STANDBY:

3.

SQL> select current_scn from v$database;
CURRENT_SCN
———– 
223771173

Clearly you can see there is difference

4.

Stop and shutdown the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

5.

Shutdown the standby database

SQL> shut immediate

6.

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;
 }

7.

On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as ‘/backup/for_standby.ctl’;

Database altered.

8.

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;

9.

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.