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

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

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

Tuesday, November 1, 2016

Register missing archivelogs on 11g standby database

0 comments:

Post a Comment