Tuesday, November 1, 2016

Register missing archivelogs on 11g standby database

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

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.gz
Uploading TS0001_0000032651_STANDBY0814361045.ARC.gz to /opt/oracle/ARCH/standby/TS0001_0000032651_STANDBY0814361045.ARC.gz
TS0001_0000032651_STANDBY0814361045.ARC.gz                                       100%  278KB 278.1KB/s   00:0

Similarly, all……………….

Once copied then STOP Recover process on standby:


Note: Here in my case I have archive logs exists in “gzip” format  on my prod server, if you don’t have then restore from tape or whatever place they stored in.

If you don’t have archivelogs on the server and exists in backup tapes then 

RMAN> connect target /

connected to target database: PROD (DBID=551231613)

RMAN> run {
allocate channel t1 type 'sbt_tape';
set archivelog destination to '/exp/prod/arch';
restore archivelog from logseq 32651 until logseq 32656;
release channel t1;
}


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