This morning I noticed one of the managed standby databases was in a pending shutdown state. I restarted it and verified the logs were getting applied from production. However, I noticed the WAIT_FOR_GAP status on one of the old logs. Some of the logs had not been applied and were no longer on the production system. So I found out which logs were in the gap and used RMAN to recover them in production. Oracle automatically shipped them to the standby and applied them.
Discover a gap:
1* SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY
PROCESS CLIENT_P SEQUENCE# STATUS --------- -------- ---------- ------------ ARCH ARCH 136626 CLOSING ARCH ARCH 0 CONNECTED ARCH ARCH 136627 CLOSING ARCH ARCH 136628 CLOSING RFS LGWR 136629 IDLE RFS UNKNOWN 0 IDLE RFS UNKNOWN 0 IDLE MRP0 N/A 130107 WAIT_FOR_GAP RFS UNKNOWN 0 IDLE Find out the extent of the gap: 1 SELECT high.thread#, "LowGap#", "HighGap#" 2 FROM 3 ( 4 SELECT thread#, MIN(sequence#)-1 "HighGap#" 5 FROM 6 ( 7 SELECT a.thread#, a.sequence# 8 FROM 9 ( 10 SELECT * 11 FROM v$archived_log 12 ) a, 13 ( 14 SELECT thread#, MAX(next_change#)gap1 15 FROM v$log_history 16 GROUP BY thread# 17 ) b 18 WHERE a.thread# = b.thread# 19 AND a.next_change# > gap1 20 ) 21 GROUP BY thread# 22 ) high, 23 ( 24 SELECT thread#, MIN(sequence#) "LowGap#" 25 FROM 26 ( 27 SELECT thread#, sequence# 28 FROM v$log_history, v$datafile 29 WHERE checkpoint_change# <= next_change# 30 AND checkpoint_change# >= first_change# 31 ) 32 GROUP BY thread# 33 ) low 34* WHERE low.thread# = high.thread# 11:13:08 BIPROD1SYS>/ THREAD# LowGap# HighGap# ---------- ---------- ---------- 1 130106 130153 Recover the logs in Production: RMAN> restore archivelog from sequence 130106 until sequence 130153;
I had to repeat this sequence of commands several times because there were multiple gaps.