Archive Gap on Standby Database

Issue:

Common issue of large archive gap on standby database , the concerned archives are compressed on primary so gap wasn’t resolved.  We can do incremental backup and recovery on standby to resolve the issue quickly but wanted to resolve the issue without restore.

Standby Alert log:

Fetching gap sequence in thread 1, gap sequence 115308-115344
Thu Aug 27 12:06:47 2015
Fetching gap sequence in thread 1, gap sequence 115308-115344
Thu Aug 27 12:06:58 2015
Fetching gap sequence in thread 1, gap sequence 115308-115344
Thu Aug 27 12:07:09 2015
Fetching gap sequence in thread 1, gap sequence 115308-115344

Primary:
Though I have uncompressed the respective archives on primary , but the standby is still not able to fetch the archives that are in gap sequence

[oracle.prima]: ls -ltr *11531*
-rw-r—– 1 oracle dba 240601600 Aug 23 03:03 prima_115310_1_796216110.arc
-rw-r—– 1 oracle dba 240602624 Aug 23 03:03 prima_115311_1_796216110.arc
-rw-r—– 1 oracle dba 240598016 Aug 23 03:03 prima_115312_1_796216110.arc
-rw-r—– 1 oracle dba 221271040 Aug 23 03:07 prima_115313_1_796216110.arc
-rw-r—– 1 oracle dba 214846464 Aug 23 03:08 prima_115314_1_796216110.arc
-rw-r—– 1 oracle dba 240604672 Aug 23 03:08 prima_115315_1_796216110.arc
-rw-r—– 1 oracle dba 231251456 Aug 23 03:08 prima_115316_1_796216110.arc
-rw-r—– 1 oracle dba 235036672 Aug 23 03:09 prima_115317_1_796216110.arc
-rw-r—– 1 oracle dba 215901184 Aug 23 03:09 prima_115318_1_796216110.arc
-rw-r—– 1 oracle dba 238041088 Aug 23 03:09 prima_115319_1_796216110.arc
[.oracle.prima]: ls -ltr *11530*
-rw-r—– 1 oracle dba 236550144 Aug 23 03:01 prima_115300_1_796216110.arc
-rw-r—– 1 oracle dba 240603648 Aug 23 03:01 prima_115301_1_796216110.arc
-rw-r—– 1 oracle dba 239972864 Aug 23 03:01 prima_115302_1_796216110.arc
-rw-r—– 1 oracle dba 240598528 Aug 23 03:01 prima_115303_1_796216110.arc
-rw-r—– 1 oracle dba 240598016 Aug 23 03:02 prima_115304_1_796216110.arc
-rw-r—– 1 oracle dba 240599552 Aug 23 03:02 prima_115305_1_796216110.arc
-rw-r—– 1 oracle dba 232717824 Aug 23 03:02 prima_115306_1_796216110.arc
-rw-r—– 1 oracle dba 240599040 Aug 23 03:02 prima_115307_1_796216110.arc
-rw-r—– 1 oracle dba  230410240 Aug 23 03:03 prima_115308_1_796216110.arc
-rw-r—– 1 oracle dba  240602112 Aug 23 03:03 prima_115309_1_796216110.arc

Standby Alertlog:

Fetching gap sequence in thread 1, gap sequence 115308-115344
Thu Aug 27 12:24:47 2015
Fetching gap sequence in thread 1, gap sequence 115308-115344
Thu Aug 27 12:24:58 2015
Fetching gap sequence in thread 1, gap sequence 115308-115344
Thu Aug 27 12:24:09 2015
Fetching gap sequence in thread 1, gap sequence 115308-115344

Reason:

The issue is primary control file lost the information related to archives missing as nightly backup has script to delete obsolete/expired backups/archives.

RMAN log for the deleted archives as they are marked expired due to zipping.

deleted archived log
archived log file name=/u1385/u08/oracle/admin/prima/arch/prima_115308_1_796216110.arc RECID=117130 STAMP=888462183
deleted archived log
archived log file name=/u1385/u08/oracle/admin/prima/arch/prima_115309_1_796216110.arc RECID=117132 STAMP=888462196
deleted archived log
archived log file name=/u1385/u08/oracle/admin/prima/arch/prima_115310_1_796216110.arc RECID=117133 STAMP=888462207
deleted archived log
archived log file name=/u1385/u08/oracle/admin/prima/arch/prima_115311_1_796216110.arc RECID=117135 STAMP=888462222
deleted archived log
archived log file name=/u1385/u08/oracle/admin/prima/arch/prima_115312_1_796216110.arc RECID=117136 STAMP=888462233

Solution:

We can have two options 1) To register the archives again on primary 2) Copy the archives to standby and register directly in standby. I have used second option below , I tested first option as well and it worked fine.

[oracle.prima]: scp prima_1153*arc oracle.standby:/u1385/u08/oracle/admin/prima/arch

To catalog large number of archives using “alter database register logfile” is time consuming, so we can catalog all archives using rman.
RMAN> catalog start with ‘/u1385/u08/oracle/admin/prima/arch_missing/’;

using target database control file instead of recovery catalog
searching for all files that match the pattern /u1385/u08/oracle/admin/prima/arch_missing/

List of Files Unknown to the Database
=====================================
File Name: /u1385/u08/oracle/admin/prima/arch_missing/prima_115357_1_796216110.arc

Standby able to resolve the gap issue.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s