Restoring missing archivelogs for standby in datagauard

Today one of my standby RAC database crashed due to SAN issues and when it came back we got error message saying some data files needs recovery when we tried to open database in read only mode. So the fix is to enable MRP to do recovery using archive log using following command and then check recovery status

SQL>  alter database recover managed standby database disconnect from session;

SQL>  select inst_id,process, status,thread#, sequence#, block#,blocks,delay_mins from gv$managed_standby;

In our case I saw MRP0 is looking for WAIT_FOR_GAP. So I investigated for GAP details in standby

SQL> select * from gv$archive_gap;

   INST_ID    THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

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

         2          2         12604          12604

         1          2         12604          12604

Checking last log shipped in Oracle dataguard standby

1) SQL> select process, status, sequence#,block#,blocks,delay_mins from v$managed_standby;

and you should see latest sequence# against MRP0 line.

2) Following command will tell us if  there is any delay in standby with primary

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

On primary use following command to restore deleted archivelog from tape

#!/bin/ksh

# Script to restore a particular archive log file from tape backup

# We can also use this syntax too

#restore archivelog from logseq = 12604 thread 2 until logseq = 12604 thread 2;

export ORAENV_ASK=NO

. oraenv

export ORACLE_SID=ptsa1

export SERV=`hostname`

export NB_ORA_POLICY=px_ptsa_rman

export NB_ORA_POLICY_ARCH=px_ptsa_arch

export NB_ORA_CLIENT="ptsa1-vip"

export CLASS=px_ptsa_arch

export ORACLE_BASE=/orasw/ptsa/app/oracle

export ORACLE_HOME=/orasw/ptsa/app/oracle/product/11.2.0/db

export PATH=$ORACLE_HOME/bin:$PATH:.:/usr/local/bin:/usr/contrib/bin

export ADMINDIR=/usr/local/admin/bin

export REPOSITORY=rman_user/password@prman

export NLS_LANG=american

export NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss'

rman log /orasw/static/oradba/bin/rman/rman_arch_restore.log <

connect catalog $REPOSITIORY

connect target /

connect rcvcat $REPOSITORY

run {

allocate  channel t1 device type 'sbt_tape';

send 'CPF1_BK_CLASS=$CLASS,CPF1_BK_POLICY=user_cp1';

send 'NB_ORA_POLICY=$NB_ORA_POLICY';

send 'NB_ORA_CLIENT=$NB_ORA_CLIENT';

set archivelog destination to '/arch/ptsa/arch';

restore archivelog sequence 12604 thread 2;

release channel t1;

}

EOF

Once the file is restored, FAL process in standby automatically transferred files from primary to standby  and GAP is resolved.

SQL> select inst_id, process, status,thread#,sequence# from gv$managed_standby;

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#

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

         1 ARCH      CONNECTED             0          0

         1 ARCH      CONNECTED             0          0

         1 ARCH      CONNECTED             0          0

         1 ARCH      CONNECTED             0          0

         1 MRP0      APPLYING_LOG          2      12611

         2 ARCH      CLOSING               1      12598

         2 ARCH      CLOSING               1       9739

         2 ARCH      CLOSING               2      12604

         2 ARCH      CLOSING               2      12610

         2 RFS       IDLE                  0          0

         2 RFS       RECEIVING             0          0

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#

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

         2 RFS       IDLE                  2      12611

         2 RFS       IDLE                  1      12599

         2 RFS       IDLE                  0          0

         2 RFS       IDLE                  0          0

         2 RFS       RECEIVING             0          0

16 rows selected.

SQL> select * from gv$archive_gap;

no rows selected

SQL> 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                  12598                 12598          0

         2                  12610                 12610          0

Once GAP is resolved we will cancel MRP and will restart in real time as follows

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select inst_id, process, status,thread#,sequence# from gv$managed_standby;