Resolve Standby Gaps by Applying Archive log File

Description: In my database archive log file are deleted from the primary database after taken RMAN backup. In physical standby database, there is log gap which is already deleted from the primary database and MRP process is waiting for the log file.

Solution:
1. Find out the backup archive log file.
list backup of archivelog sequence 8760;
2. Transfer backup archive log file into Standby database.
scp 
3. Restore archive log file using log sequence number.
restore archivelog from sequence 8760 until sequence 8760 thread=1;

Checking in Standby Database for find out the archive log file details.

SQL> SELECT PROCESS, PID, STATUS, CLIENT_PROCESS, CLIENT_PID, RESETLOG_ID,
 THREAD#, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS
 FROM V$MANAGED_STANDBY;

PROCESS          PID STATUS       CLIENT_P CLIENT_PID    RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ---------- ------------ -------- ------------- ----------- ---------- ---------- ---------- ---------- ----------
ARCH         5636392 CLOSING      ARCH     5636392         928567359          2       9357      75776        317          0
ARCH         2097262 CLOSING      ARCH     2097262         928567359          1       9461     552960        541          0
ARCH         8061076 CLOSING      ARCH     8061076         928567359          2       9359     364544       1996          0
ARCH         4390984 CONNECTED    ARCH     4390984                 0          0          0          0          0          0
MRP0         8323150 WAIT_FOR_GAP N/A      N/A             928567359          1       8760          0          0          0
RFS          7536982 IDLE         ARCH     8257884                 0          0          0          0          0          0
RFS          2818346 IDLE         ARCH     10682474                0          0          0          0          0          0
RFS          6356994 IDLE         LGWR     14876892        928567359          2       9360     461397          2          0
RFS          9175274 IDLE         LGWR     8061206         928567359          1       9462    1506619         12          0
RFS         10289552 IDLE         UNKNOWN  10223730                0          0          0          0          0          0

10 rows selected.

SQL> SELECT NAME
  FROM V$ARCHIVED_LOG
 WHERE THREAD# = 1
AND DEST_ID = 1
AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

no rows selected

Checking in primary database is there any archive log file exist or not if it does not then find the backup file and transfer into the standby database.

SQL> SELECT NAME, STANDBY_DEST, DELETED
  FROM GV$ARCHIVED_LOG
 WHERE THREAD# = 1
 AND DEST_ID = 1
 AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

NAME                                                         STA DEL
------------------------------------------------------------ --- ---
                                                             NO  YES
                                                             NO  YES

SQL>

Find out the archive log file backup location

bash-4.3$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Sun May 14 12:54:18 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ******* (DBID=******* )

RMAN> list backup of archivelog sequence 8760 thread 1;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

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

3308    3.22G      DISK        00:06:39     09-MAY-17

        BP Key: 3308   Status: AVAILABLE  Compressed: YES  Tag: ORCL_ARCHIVE

        Piece Name: /rmanbackup/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch

  List of Archived Logs in backup set 3308

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    8760    12151347030306 08-MAY-17 12151347165919 08-MAY-17

BS Key  Size       Device Type Elapsed Time Completion Time

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

3323    13.44G     SBT_TAPE    00:05:57     09-MAY-17

        BP Key: 3323   Status: AVAILABLE  Compressed: NO  Tag: TAG20170509T051719

        Handle: dks3piuo_1_1   Media: 2851

  List of Archived Logs in backup set 3323

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    8760    12151347030306 08-MAY-17 12151347165919 08-MAY-17

RMAN> exit

Recovery Manager complete.

bash-4.3$

Transfer into standby database 

scp /rmanbackup/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch oracle@****:/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch

Connect RMAN from Standby database and restore archive log file 

bash-4.3$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Sun May 14 13:36:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ***** (DBID=******* )

RMAN> catalog backuppiece '/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch';

cataloged backup piece

backup piece handle=/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch RECID=170 STAMP=943969336

RMAN> restore archivelog from sequence 8760 until sequence 8760 thread=1;

Starting restore at 14-MAY-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=6114 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=8760

channel ORA_DISK_1: reading from backup piece /rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch

channel ORA_DISK_1: piece handle=/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch tag=ORCL_ARCHIVE

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:08:05

Finished restore at 14-MAY-17

RMAN> exit

Recovery Manager complete.

Now Standby database will start applying the archive log file. If it is not started just stop and start recovery of the standby database.

Checking the applying start or not.

SQL> SELECT NAME

  FROM V$ARCHIVED_LOG

 WHERE THREAD# = 1

AND DEST_ID = 1

AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

NAME

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

+FRA/****/archivelog/2017_05_14/thread_1_seq_8760.2508.943969813

SQL>

SQL> SELECT PROCESS, PID, STATUS, CLIENT_PROCESS, CLIENT_PID, RESETLOG_ID,

 THREAD#, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS

 FROM V$MANAGED_STANDBY;   2    3

PROCESS          PID STATUS       CLIENT_P CLIENT_PID                               RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS

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

ARCH         5636392 CLOSING      ARCH     5636392                                    928567359          2       9282     129024        828          0

ARCH         2097262 CLOSING      ARCH     2097262                                    928567359          1       9462    1767424       1537          0

ARCH         8061076 CLOSING      ARCH     8061076                                    928567359          2       9359     364544       1996          0

ARCH         4390984 CONNECTED    ARCH     4390984                                            0          0          0          0          0          0

MRP0         8323150 APPLYING_LOG N/A      N/A                                        928567359          1       8799      12876     944628          0

RFS          7536982 RECEIVING    UNKNOWN  8257884                                    928567359          1       8941      32769       2048          0

RFS         10355038 IDLE         ARCH     10682474                                           0          0          0          0          0          0

RFS          8454490 IDLE         LGWR     14876892                                   928567359          2       9362     442233          4          0

RFS          9175274 IDLE         LGWR     8061206                                    928567359          1       9463    1334595          5          0

RFS         10289552 IDLE         UNKNOWN  10223730                                           0          0          0          0          0          0

RFS          2818374 RECEIVING    ARCH     14877160                                           0          0          0          0          0          0

PROCESS          PID STATUS       CLIENT_P CLIENT_PID                               RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS

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

RFS          6095152 RECEIVING    UNKNOWN  29163686                                   928567359          2       9296     761858       2048          0

RFS          7733286 RECEIVING    UNKNOWN  10092786                                   928567359          1       8940      49153       2048          0

13 rows selected.

SQL>