Oracle Physical Standby Archive Log Gap Resolution

Getting below in Alert log:

——————————————————————————————————-

SUCCESS: diskgroup VOL10 was dismounted
Media Recovery Waiting for thread 1 sequence 4709
Fetching gap sequence in thread 1, gap sequence 4709-4711
Fri Aug 22 13:00:33 2014
FAL[client]: Failed to request gap sequence
 GAP – thread 1 sequence 4709-4711

 DBID 238289769 branch 788617304
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 is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
————————————————————-
Fri Aug 22 13:00:56 2014
SUCCESS: diskgroup VOL10 was mounted
SUCCESS: diskgroup VOL10 was dismounted
SUCCESS: diskgroup VOL10 was mounted
Fri Aug 22 13:01:38 2014
RFS[3]: Archived Log: '+VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4711.358.856270857'
————————————————————————————————————————————————————-

Solution:

SQL>  select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
———- ————- ————–
         1          4708           4721


Restoring Archived Redo Logs .

–on primary

rman target /

RMAN> list archivelog from sequence 4708 until sequence 4721;

using target database control file instead of recovery catalog
specification does not match any archive log in the recovery catalog

RMAN> LIST BACKUP OF ARCHIVELOG from SEQUENCE 4708 until SEQUENCE 4721;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
12143   4.77G      DISK        00:00:49     18-AUG-14
        BP Key: 12135   Status: AVAILABLE  Compressed: NO  Tag: ARCHIVELOG_BK
        Piece Name: /u22/FPROD/backup/backup_t2pg7t6h_1_1

  List of Archived Logs in backup set 12143
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  —- ——- ———- ——— ———- ———
  1    4708    9742386455996 17-AUG-14 9742389836636 17-AUG-14

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
12144   4.72G      DISK        00:00:48     18-AUG-14
        BP Key: 12136   Status: AVAILABLE  Compressed: NO  Tag: ARCHIVELOG_BK
        Piece Name: /u22/FPROD/backup/backup_t3pg7t89_1_1

  List of Archived Logs in backup set 12144
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  —- ——- ———- ——— ———- ———
  1    4709    9742389836636 17-AUG-14 9742390027917 18-AUG-14
  1    4710    9742390027917 18-AUG-14 9742390166858 18-AUG-14
  1    4711    9742390166858 18-AUG-14 9742390377237 18-AUG-14
  1    4712    9742390377237 18-AUG-14 9742390380353 18-AUG-14
  1    4713    9742390380353 18-AUG-14 9742390380390 18-AUG-14
  1    4714    9742390380390 18-AUG-14 9742390380458 18-AUG-14

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
12161   6.72G      DISK        00:01:07     19-AUG-14
        BP Key: 12153   Status: AVAILABLE  Compressed: NO  Tag: ARCHIVELOG_BK
        Piece Name: /u22/FPROD/backup/backup_tkpgahi4_1_1

  List of Archived Logs in backup set 12161
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  —- ——- ———- ——— ———- ———
  1    4708    9742386455996 17-AUG-14 9742389836636 17-AUG-14
  1    4709    9742389836636 17-AUG-14 9742390027917 18-AUG-14
  1    4710    9742390027917 18-AUG-14 9742390166858 18-AUG-14
  1    4711    9742390166858 18-AUG-14 9742390377237 18-AUG-14
  1    4712    9742390377237 18-AUG-14 9742390380353 18-AUG-14
  1    4713    9742390380353 18-AUG-14 9742390380390 18-AUG-14
  1    4714    9742390380390 18-AUG-14 9742390380458 18-AUG-14
  1    4715    9742390380458 18-AUG-14 9742395342537 19-AUG-14

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
12162   3.90G      DISK        00:00:41     19-AUG-14
        BP Key: 12154   Status: AVAILABLE  Compressed: NO  Tag: ARCHIVELOG_BK
        Piece Name: /u22/FPROD/backup/backup_tlpgahkg_1_1

  List of Archived Logs in backup set 12162
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  —- ——- ———- ——— ———- ———
  1    4716    9742395342537 19-AUG-14 9742395522745 19-AUG-14
  1    4717    9742395522745 19-AUG-14 9742395692439 19-AUG-14
  1    4718    9742395692439 19-AUG-14 9742396255327 19-AUG-14
  1    4719    9742396255327 19-AUG-14 9742396258411 19-AUG-14
  1    4720    9742396258411 19-AUG-14 9742396258435 19-AUG-14
  1    4721    9742396258435 19-AUG-14 9742396258479 19-AUG-14

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
12179   5.83G      DISK        00:00:59     20-AUG-14
        BP Key: 12171   Status: AVAILABLE  Compressed: NO  Tag: ARCHIVELOG_BK
        Piece Name: /u22/FPROD/backup/backup_u6pgd5vl_1_1

  List of Archived Logs in backup set 12179
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  —- ——- ———- ——— ———- ———
  1    4715    9742390380458 18-AUG-14 9742395342537 19-AUG-14
  1    4716    9742395342537 19-AUG-14 9742395522745 19-AUG-14
  1    4717    9742395522745 19-AUG-14 9742395692439 19-AUG-14

BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
12180   5.64G      DISK        00:01:00     20-AUG-14
        BP Key: 12172   Status: AVAILABLE  Compressed: NO  Tag: ARCHIVELOG_BK
        Piece Name: /u22/FPROD/backup/backup_u7pgd61n_1_1

  List of Archived Logs in backup set 12180
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  —- ——- ———- ——— ———- ———
  1    4718    9742395692439 19-AUG-14 9742396255327 19-AUG-14
  1    4719    9742396255327 19-AUG-14 9742396258411 19-AUG-14
  1    4720    9742396258411 19-AUG-14 9742396258435 19-AUG-14
  1    4721    9742396258435 19-AUG-14 9742396258479 19-AUG-14
  
–Restore archive logs backup 

V01prod@FPROD-/u22/FPROD/archivelog]: rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Aug 22 12:58:22 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: FPROD (DBID=456281239)

RMAN> RUN
{
  —SET ARCHIVELOG DESTINATION TO '/u22/FPROD/archivelog'; ——( use as needed)
  RESTORE ARCHIVELOG FROM SEQUENCE 4708 UNTIL SEQUENCE 4721;
}2> 3> 4> 5>

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 22-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=484 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=481 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=472 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=431 devtype=DISK

channel ORA_DISK_1: starting archive log restore to user-specified destination
archive log destination=/u22/FPROD/archivelog
channel ORA_DISK_2: starting archive log restore to user-specified destination
archive log destination=/u22/FPROD/archivelog
channel ORA_DISK_3: starting archive log restore to user-specified destination
archive log destination=/u22/FPROD/archivelog
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4708
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=4715
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=4718
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4709
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=4716
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=4719
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4710
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=4717
channel ORA_DISK_2: reading from backup piece /u22/FPROD/backup/backup_u6pgd5vl_1_1
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=4720
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4711
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=4721
channel ORA_DISK_3: reading from backup piece /u22/FPROD/backup/backup_u7pgd61n_1_1
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4712
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4713
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4714
channel ORA_DISK_1: reading from backup piece /u22/FPROD/backup/backup_tkpgahi4_1_1
channel ORA_DISK_3: restored backup piece 1
piece handle=/u22/FPROD/backup/backup_u7pgd61n_1_1 tag=ARCHIVELOG_BK
channel ORA_DISK_3: restore complete, elapsed time: 00:00:07
channel ORA_DISK_2: restored backup piece 1
piece handle=/u22/FPROD/backup/backup_u6pgd5vl_1_1 tag=ARCHIVELOG_BK
channel ORA_DISK_2: restore complete, elapsed time: 00:03:43
channel ORA_DISK_1: restored backup piece 1
piece handle=/u22/FPROD/backup/backup_tkpgahi4_1_1 tag=ARCHIVELOG_BK
channel ORA_DISK_1: restore complete, elapsed time: 00:03:58
Finished restore at 22-AUG-14

–on primary and standby both check below

RMAN> list archivelog from sequence 4708 until sequence 4721;

using target database control file instead of recovery catalog

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
——- —- ——- – ——— —-
10302   1    4708    A 17-AUG-14 /u22/FPROD/archivelog/1_4708_788617304.dbf
10309   1    4709    A 17-AUG-14 /u22/FPROD/archivelog/1_4709_788617304.dbf
10310   1    4710    A 18-AUG-14 /u22/FPROD/archivelog/1_4710_788617304.dbf
10304   1    4711    A 18-AUG-14 /u22/FPROD/archivelog/1_4711_788617304.dbf
10293   1    4712    A 18-AUG-14 /u22/FPROD/archivelog/1_4712_788617304.dbf
10292   1    4713    A 18-AUG-14 /u22/FPROD/archivelog/1_4713_788617304.dbf
10290   1    4714    A 18-AUG-14 /u22/FPROD/archivelog/1_4714_788617304.dbf
10307   1    4715    A 18-AUG-14 /u22/FPROD/archivelog/1_4715_788617304.dbf
10308   1    4716    A 19-AUG-14 /u22/FPROD/archivelog/1_4716_788617304.dbf
10306   1    4717    A 19-AUG-14 /u22/FPROD/archivelog/1_4717_788617304.dbf
10294   1    4718    A 19-AUG-14 /u22/FPROD/archivelog/1_4718_788617304.dbf
10291   1    4719    A 19-AUG-14 /u22/FPROD/archivelog/1_4719_788617304.dbf
10289   1    4720    A 19-AUG-14 /u22/FPROD/archivelog/1_4720_788617304.dbf
10288   1    4721    A 19-AUG-14 /u22/FPROD/archivelog/1_4721_788617304.dbf

RMAN> list archivelog from sequence 4708 until sequence 4721;

using target database control file instead of recovery catalog

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
——- —- ——- – ——— —-
3749    1    4708    A 17-AUG-14 +VOL10/dg01rod_fprod/archivelog/2014_08_22/thread_1_seq_4708.352.856270767
3751    1    4709    A 17-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4709.385.856270977
3752    1    4710    A 18-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4710.365.856271011
3750    1    4711    A 18-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4711.358.856270857
3742    1    4712    A 18-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4712.359.856270737
3743    1    4713    A 18-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4713.340.856270737
3744    1    4714    A 18-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4714.339.856270737
3753    1    4715    A 18-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4715.273.856271057
3754    1    4716    A 19-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4716.421.856271107
3755    1    4717    A 19-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4717.420.856271161
3745    1    4718    A 19-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4718.349.856270737
3746    1    4719    A 19-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4719.423.856270739
3747    1    4720    A 19-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4720.422.856270739
3748    1    4721    A 19-AUG-14 +VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4721.364.856270739

–Check it will be applied automatically if not then follow below steps
–Copy it to standby archive location   

–Register the archived logs on the standby database using below command for all archives one by one:

SQL> alter database register physical logfile '+VOL10/dg01prod_fprod/archivelog/2014_08_22/thread_1_seq_4708.352.856270767';
Database altered.

SQL> select low_sequence#, high_sequence# from  v$archive_gap;
no rows selected       <– there is no gap now

–Alternatively :

–If you have many of archive log files to register, then you could simply use manual recovery without registering them. 
Crosscheck the archive log files are placed in the location specified by log_archive_dest_1 or standby_archive_dest. 
If not, then you would need to use from 'location' attribute in recovery command.

SQL> alter database recover managed standby database cancel; 
SQL> alter database recover automatic standby database; 
or 
SQL> alter database recover automatic from '/u20/dump/FPROD_archive' standby database;

–Now verify it :

select * from v$archive_gap;

SELECT THREAD#, SEQUENCE#,archived,APPLIED FROM gV$ARCHIVED_LOG ORDER BY SEQUENCE#;

SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,DELAY_MINS FROM V$MANAGED_STANDBY;

also check Alert logs


         

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply