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