Oracle: FAL[client]: Failed to request gap sequence

Primary and standby DB : 11.2.0.3 on RHEL 6

Primary DB: PROD

Standby DB: STANDBY

We got an alert for one of our production standby database is out of sync

Standby Alert log :

FAL[client]: Failed to request gap sequence

GAP - thread 1 sequence 32651-32656

DBID 3477518933 branch 814361045

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's sufficiently large

enough to maintain adequate log switch information to resolve

archivelog gaps.

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

Fri Sep 23 15:43:48 2016

db_recovery_file_dest_size of 25600 MB is 0.00% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Fri Sep 23 15:54:41 2016

Then checked for the archive gap….

standby_server:STANDBY:/opt/oracle/ARCH/standby $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 23 15:29:26 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.Sfference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HIS WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 32687 32650 37 <<----- 37 arch logs gap

SQL> select message, timestamp from v$dataguard_status where severity in ('Error','Fatal')

order by timestamp;

no rows selected

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

PROCESS STATUS

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

ARCH CONNECTED

MR(fg) WAIT_FOR_GAP --->> Recovery says WAITING FOR GAP

RFS IDLE

NOTE : Before going for any type of troubleshooting for standby databases first thing we need to check is where standby have required archivelogs or not.

standby_server:STANDBY:/opt/oracle/ARCH/standby $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 23 16:14:25 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /opt/oracle/ARCH/standby/

Oldest online log sequence 32685

Next log sequence to archive 0

Current log sequence 32690

SQL> SELECT * FROM V$ARCHIVE_GAP;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

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

1 32651 32656

Check last sequence applied:

SQL> SELECT thread#, Max(sequence#) "Last Standby Seq Applied" FROM v$archived_log WHERE applied = 'YES' GROUP BY thread# ORDER BY 1;

THREAD# Last Standby Seq Applied

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

1 32650

$ cd /opt/oracle/ARCH/standby

standby_server:STANDBY:/opt/oracle/ARCH/standby $ ls -lrth

-rw-r----- 1 oracle dba 2.4M Sep 21 19:11 TS0001_0000032647_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 29M Sep 21 23:22 TS0001_0000032648_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 15K Sep 21 23:22 TS0001_0000032649_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 78K Sep 21 23:29 TS0001_0000032650_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 38M Sep 23 12:51 TS0001_0000032657_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 39M Sep 23 12:51 TS0001_0000032658_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 66M Sep 23 12:52 TS0001_0000032659_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 52M Sep 23 12:52 TS0001_0000032660_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 34M Sep 23 12:52 TS0001_0000032661_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 38M Sep 23 12:52 TS0001_0000032662_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 66M Sep 23 12:53 TS0001_0000032663_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 85M Sep 23 12:53 TS0001_0000032664_STANDBY0814361045.ARC

-rw-r----- 1 oracle dba 29M Sep 23 12:53 TS0001_0000032665_STANDBY0814361045.ARC

From the above output we can clearly see that from Seq# 32651 - seq# 32656 are MISSING, uummmm now we know what need to be done to resolve

NOTE: Luckily i have archivelogs exists in primary side if doesn't exists any more then you need to rebuild standby again(ex: rman incremental backups,etc.,)

Primary:

oracleprimaryserver:PROD:/opt/oracle/ARCH/Primary $ sftp oracle@standby_server:/opt/oracle/ARCH/standby

Connecting to standby_server...

oracle@standby_server's password:

Changing to: /opt/oracle/ARCH/standby

sftp> put TS0001_0000032651_STANDBY0814361045.ARC

Uploading TS0001_0000032651_STANDBY0814361045.ARC to /opt/oracle/ARCH/standby/TS0001_0000032651_STANDBY0814361045.ARC

TS0001_0000032651_STANDBY0814361045.ARC 100% 278KB 278.1KB/s 00:0

Similarly, all……………….

Once copied then STOP Recover process on standby:

Note: open another Terminal and watch the alert log

standby_server:STANDBY:/opt/oracle/ARCH/standby $ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 23 16:09:08 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Recover managed standby database cancel;

SQL> alter database register logfile '/FULL_PATH/TS0001_0000032651_STANDBY0814361045.ARC';

Database altered.

Similarly register all the 6 logfiles and start the recovery process

SQL> recover managed standby database;

Now check again the gap

SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

PROCESS STATUS

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

ARCH CONNECTED

MR(fg) APPLYING_LOG <<-- See recovery started applying

RFS IDLE

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 32689 32689 0

Hope this helps ….!!

Friday, September 23, 2016

FAL[client]: Failed to request gap sequence

0 comments:

Post a Comment