Standby server - WAIT_FOR_GAP error

[Former Member]

Hi,

I have a Oracle Dataguard 10.2 running on Windows and found a WAIT_FOR_GAP error on the standby server.

--> alert log of standby

Sun Jan 30 20:00:04 2011

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

MRP0 started with pid=18, OS id=2452

Managed Standby Recovery not using Real Time Apply

parallel recovery started with 3 processes

Sun Jan 30 20:00:13 2011

Waiting for all non-current ORLs to be archived...

Clearing online redo logfile 10 E:\ORACLE\A30\ORIGLOGA\LOG_G10_M1.DBF

Clearing online log 10 of thread 1 sequence number 17848

Sun Jan 30 20:00:13 2011

Errors in file z:\oracle\a30\saptrace\background\a30_mrp0_2452.trc:

ORA-19527: physical standby redo log must be renamed

ORA-00312: online log 10 thread 1: 'E:\ORACLE\A30\ORIGLOGA\LOG_G10_M1.DBF'

Clearing online redo logfile 10 complete

Media Recovery Log L:\ORACLE\A30\ORAARCH\A30ARCH\ARC17856_0681431456.001

Sun Jan 30 20:00:13 2011

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Sun Jan 30 20:00:33 2011

Media Recovery Waiting for thread 1 sequence 17857

Sun Jan 30 20:51:19 2011

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[636]: Assigned to RFS process 4976

RFS[636]: Identified database type as 'physical standby'

Primary database is in MAXIMUM PERFORMANCE mode

Primary database is in MAXIMUM PERFORMANCE mode

Sun Jan 30 20:51:20 2011

Fetching gap sequence in thread 1, gap sequence 17857-17863

Sun Jan 30 20:51:20 2011

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[637]: Assigned to RFS process 4012

RFS[637]: Identified database type as 'physical standby'

Sun Jan 30 20:52:20 2011

RFS[637]: Archived Log: 'L:\ORACLE\A30\ORAARCH\A30ARCH\ARC17864_0681431456.001'

Sun Jan 30 20:56:34 2011

FAL[client]: Failed to request gap sequence

GAP - thread 1 sequence 17857-17863

DBID 3371425568 branch 681431456

FAL[client]: All defined FAL servers have been attempted.

-

---

Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization

parameter is defined to a value that is sufficiently large

enough to maintain adequate log switch information to resolve

archivelog gaps.

-

---

Sun Jan 30 22:00:44 2011

RFS[636]: Archived Log: 'L:\ORACLE\A30\ORAARCH\A30ARCH\ARC17865_0681431456.001'

Primary database is in MAXIMUM PERFORMANCE mode

SQL> select process,sequence#,status from v$managed_standby;

PROCESS SEQUENCE# STATUS

-

---

-

---

-

---

ARCH 0 CONNECTED

ARCH 0 CONNECTED

MRP0 17857 WAIT_FOR_GAP

RFS 0 IDLE

RFS 0 IDLE

RFS 0 IDLE

SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

-

---

-

---

-

---

1 17857 17863

SQL> select sequence#,applied from v$archived_log order by sequence#;

SEQUENCE# APP

-

---

---

17855 YES

17856 YES <--- Gap here

17864 NO

17865 NO

17866 NO

17867 NO

17868 NO

17869 NO

17870 NO

17871 NO

17872 NO

SEQUENCE# APP

-

---

---

17873 NO

17874 NO

17875 NO

17876 NO

17877 NO

17878 NO

17879 NO <--- Gap here

17889 NO

17890 NO

17891 NO

17892 NO

...............

SEQUENCE# APP

-

---

---

18067 NO

18068 NO

18069 NO

18070 NO

18071 NO

18072 NO

4032 rows selected.

--> alert log of primary

Sun Jan 30 20:00:03 2011

Incremental checkpoint up to RBA [0x45c8.1f6c6.0], current log tail at RBA [0x45c8.1f7f5.0]

Sun Jan 30 20:05:04 2011

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

Sun Jan 30 20:30:11 2011

Incremental checkpoint up to RBA [0x45c8.201e2.0], current log tail at RBA [0x45c8.20215.0]

Sun Jan 30 20:51:12 2011

Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED

******************************************************************

LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2

******************************************************************

LNSb started with pid=50, OS id=1792

Sun Jan 30 20:51:19 2011

Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

Beginning log switch checkpoint up to RBA [0x45c9.2.10], SCN: 276988485

Sun Jan 30 20:51:20 2011

Thread 1 advanced to log sequence 17865 (LGWR switch)

Current log# 17 seq# 17865 mem# 0: F:\ORACLE\A30\ORIGLOGB\LOG_G17_M1.DBF

Current log# 17 seq# 17865 mem# 1: E:\ORACLE\A30\MIRRLOGB\LOG_G17_M2.DBF

Sun Jan 30 20:51:20 2011

ARC1: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2

Sun Jan 30 20:51:25 2011

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

Sun Jan 30 20:56:35 2011

Completed checkpoint up to RBA [0x45c9.2.10], SCN: 276988485

[Former Member · Accepted solution]

Fetching gap sequence in thread 1, gap sequence 17857-17863

This is not an error, As per my understanding your sync process got disturbed and restarted.

After restarting data guard re-started the shipping process from primary to secondary (Normally it never takes the sequence and takes some time to shipp all the available logs from primary).

Regards,

Nick Loy

[Former Member · Accepted solution]

Latest output:

SQL> select sequence#,applied from v$archived_log order by sequence#;

SEQUENCE# APP

---

17861 YES

17862 YES

17863 YES

17864 NO

17864 YES

17865 NO

17866 NO

17867 NO

17868 NO

17869 NO

17870 NO

...........

SEQUENCE# APP

---

17902 NO

17903 NO

17904 NO

17905 NO <----

---

Gap Here!

17933 NO

17934 NO

17935 NO

17936 NO

17937 NO

17938 NO

17939 NO

..........

SEQUENCE# APP

---

18095 NO

18096 NO

18133 NO

18134 NO

18135 NO

18136 NO

4032 rows selected.

SQL> select * from v$archive_gap;

no rows selected

SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

-

---

-

---

-

---

ARCH CONNECTED 0 0 0 0

ARCH CONNECTED 0 0 0 0

MRP0 WAIT_FOR_GAP 1 17865 0 0

RFS IDLE 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 1 18137 119498 1

RFS IDLE 0 0 0 0

7 rows selected.

The last log applied was 17864. When I execute ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;", it shipped the new log 18133 instead from Primary to Standby. I also put the missing log 17865 onwards in archive log directory of Primary but log 17865 still have the status of not applied in Standby. How do I instruct Oracle to ship and apply log 17865 onwards?

[Former Member · Accepted solution]

Hi,

Since you have already transfered the archive logs at the OS level,

There are 2 ways of doing it...

1.) Issue alter database register logfile '/oracle/sid/oraarch/sidarch.....dbf'; and issue alter database recover managed standby database disconnect..

or

2.) Recover the database manually using recover standby database.....

Let me know how it goes..

Thanks,

Salman

[mohammed_anish]

Hi,

I've seen that, if the following message persists for a long time, then it is an ERROR.

FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 17857-17863 DBID 3371425568 branch 681431456 FAL[client]: All defined FAL servers have been attempted.

The possibility is that log shipping of 17857 is stuck. It is not stuck because of any network disconnect, as it is in the case you mentioned. It is probably due to the following.

The parameter LOG_ARCHIVE_DEST_1 on standby is set as L:\ORACLE\A30\ORAARCH\A30ARCH\ARC and STANDBY_ARCHIVE_DEST is also set to L:\ORACLE\A30\ORAARCH\A30ARCH\ARC. When a GAP is identified, standby database tries to pull the missing files FROM PRIMARY, and the in turn when you enable standby destination on primary (log_archive_dest_state_2 = enabled), it starts shipping logfiles on its OWN.

When such a situation occurs in a system when archived logs are generated at a high rate, it can happen that the file which standby is trying to pull is being pushed/already pushed by primary database. Then a conflict arise, and standby deletes the file and tries to pull it again...this may go on in a loop. (because the locations are SAME).

When i had this issue, I maintained different locations for LOG_ARCHIVE_DEST_1 and STANDBY_ARCHIVE_DEST on standby database. After this is done, you will find that the logs shipped by primary are written in STANDBY_ARCHIVE_DEST. The file written in this location will be used by the standby database only when you do a managed recovery. If you are doing a manual recovery by issuing the command RECOVER STANDBY DATABASE; then, the stanbdby database will check if the file exists in LOG_ARCHIVE_DEST_1.

The point is, when there is BIG gap, then you have the option of doing the following procedure.

1. On the primary database keep LOG_ARCHIVE DESTSTATE_2 = enable

2. On the standby ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL.

3. Copy the missing archive logs sequence from the primary to standby's LOG_ARCHIVE_DEST_1 and perform manual recovery on standby untill the gap is over.

4. issue the command ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT to start managed recovery. ( This will use the files present in STANDBY_ARCHIVE_DEST ).

Hope the above procedure helps.

Note: If the GAP is not that big and you see that last applied log is advancing, then conflict is not there...standby will automatically fill the GAP. But it is advised to maintain different location for the parameters i mentioned.

Regards,

Anish

Edited by: Mohammed Anish on Feb 10, 2011 6:48 AM

[Former Member]

Can someone advise how do I maintain the text spacing so that the post is more readable?

[Former Member]

See if this helps

Thanks,

Salman

[Former Member]

Hi,

Post the alert log & arc trace of primary

Thanks,

Salman

[Former Member]

Just realized the post is difficult to read.

How do I retain the spaces and not have all the characters "joined" as above?