WAIT_FOR_GAP status on standby

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.

Advertisements

4 thoughts on “WAIT_FOR_GAP status on standby

  1. Pingback: Mind The Gap… « This Broken World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s