Friday, September 23, 2016

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

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


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
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 ….!!


0 comments:

Post a Comment

Auto Scroll Stop Scroll