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 ….!!
No comments:
Post a Comment