Missing ArchiveLog at Standby server

Issue : Archive log gap at Standby end. A standby was created from cold backup of Production Database.

SOLUTION: go for an incremental backup from the current scn at my standby till the production’s scn.

The following solution can be useful when there is a gap of some archive log at standby which was physically removed/delted at production end, And it was not applied to standby database.

SQL> select max(sequence#) from v$archived_log;

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL>SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

SQLPLUSW SYS/ORACLE@ORCLSBY AS SYSDBA

SQLSBY>SHUT IMMEDIATE

SQLSBY>startup nomount;

SQLSBY>alter database mount standby database;

SQLSBY>recover managed standby database disconnect from session;

STILL ERROR IN RECOVERY THEN USE INCREMENTAL BACKUP

SQLSBY>select max(sequence#) from v$log_history;

SQLSBY>recover managed standby database cancel;

ORA-16136: Managed Standby Recovery not active

SQLSBY>ALTER DATABASE RECOVER managed standby database cancel

confirm from the view v$managed_standby to see if the MRP(managed recovery process) is running or not

SQLSBY>select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

RFS (remote file server)

SQLSBY>recover standby database;

ORA-00279: change 25738819980 generated at 04/23/2011 16:13:24 needed for thread 1

ORA-00289: suggestion : C:\DBDR\ARCHIVE_DBDR\ARC_0741355170_00311_001

ORA-00280: change 25738819980 for thread 1 is in sequence #311

SQLSBY>SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

SEQUENCE# APP

----------- -----

311 NO

312 NO

313 NO

314 NO

SQLSBY>SELECT CURRENT_SCN FROM V$DATABASE;

SQLSBY>SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

TO_CHAR(CURRENT_SCN)

----------------------------------------

25738819979

RMAN Incremental backup from SCN of standby database

Taking the incremental backup of production database from the current scn (25738819979) at standby database and applying the same at standby end.

RMAN> BACKUP INCREMENTAL FROM SCN 25738824328 DATABASE FORMAT 'C:\DBDR\DBDR_%U' tag 'Archive_Gap';

Now applying the incremental backup to standby database. Catalog the backuppiece with standby.

RMAN> CATALOG BACKUPPIECE 'C:\DBDR\DBDR_0DMAO5OR_1_1';

RMAN> RECOVER DATABASE NOREDO;

RMAN> DELETE BACKUP TAG 'Archive_Gap';

SQLSBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

SQLSBY> select process from v$managed_standby;

PROCESS

---------

ARCH

ARCH

RFS

MRP0

SQLSBY> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

SEQUENCE# APP

---------- ---

311 YES

312 YES

313 YES

314 YES

We can see that the standby is now in sync with the production.

SQL>alter system switch logfile;

SQL>select max(sequence#) from v$archived_log;

SQLSBY>select max(sequence#) from v$log_history;

SQLSBY>SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;