Recovering a Standby database from a missing archivelog

Hi friends,
today i came across one issue recovering a standby database from a missing archivelog files.

on primary database

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archive
Oldest online log sequence 16
Next log sequence to archive 18
Current log sequence 18

on standby database
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archive
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 18

i tried to solve the problem using shutdownabort.com document

Register a missing log file

alter database register physical logfile '';alter database register physical logfile '<fullpath/filename>'; 

If FAL doesn't work and it says the log is already registered

alter database register or replace physical logfile '';


If that doesn't work, try this...

shutdown immediate
startup nomount
alter database mount standby database;
alter database recover automatic standby database;

wait for the recovery to finish - then cancel
shutdown immediate
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect; shutdown immediate startup nomount alter database mount standby database; alter database recover managed standby database disconnect;
Check which logs are missing

Run this on the standby...

select local.thread#
,      local.sequence# from
     (select thread#
     ,       sequence#
     from    v$archived_log
     where dest_id=1) local
where  local.sequence# not in
     (select sequence#
     from v$archived_log
     where dest_id=2 and
     thread# = local.thread#)
/
 THREAD#  SEQUENCE#
---------- ----------
       1          9
       1         10
       1         11
       1         12
       1         13
       1         14
       1         15


still i the archive logs are not applied to the standby database.

finally i tried recovering a standby database using rman , el-caro blog document 
i got a solution, now my primary and standby database has equal archives.

A Physical Standby database relies on continuous application of
archivelogs from a Primary Database to be in synch with it. In Oracle
Database versions prior to 10g in the event of an archivelog gone
missing or corrupt you had to rebuild the standby database from scratch.

In
10g you can use an incremental backup and recover the standby using the
same to compensate for the missing archivelogs as shown below

In
the case below archivelogs with sequence numbers 137 and 138 which are
required on the standby are deleted to simulate this problem.

Step 1: On the standby database check the current scn.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   548283

Step 2: On the primary database create the needed incremental backup from the above SCN

login to primary database rman target /

RMAN> backup device type disk incremental from scn 548283 database format '/u01/backup/bkup_%U';

Starting backup at 28-DEC-09

using channel ORA_DISK_1
backup will be obsolete on date 04-JAN-10
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/demo1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/demo1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/demo1/rman01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/demo1/rman02.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/demo1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/demo1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-DEC-09
channel ORA_DISK_1: finished piece 1 at 28-DEC-09
piece handle=/u01/backup/bkup_07l21ukv_1_1 tag=TAG20091228T143302 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:24:19

using channel ORA_DISK_1
backup will be obsolete on date 04-JAN-10
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 28-DEC-09
channel ORA_DISK_1: finished piece 1 at 28-DEC-09
piece handle=/u01/backup/bkup_08l2202v_1_1 tag=TAG20091228T143302 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 28-DEC-09

RMAN> 

Step 3: Cancel managed recovery at the standby database

SQL>recover managed standby database cancel;
Media recovery complete.

Move the backup files to a new folder called new_incr so that they are the only files in that folder.

Step 4: Catalog the Incremental Backup Files at the Standby Database

[oracle@rac1 bin]$ . oraenv
ORACLE_SID = [RAC1] ? stby
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@rac1 bin]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Mon Dec 28 15:01:33 2009

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

connected to target database: DEMO1 (DBID=3710229940, not open)

RMAN> catalog start with '/u01/backup/new_incr';

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/backup/new_incr

List of Files Unknown to the Database
=====================================
File Name: /u01/backup/new_incr/bkup_08l2202v_1_1
File Name: /u01/backup/new_incr/bkup_07l21ukv_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backup/new_incr/bkup_08l2202v_1_1
File Name: /u01/backup/new_incr/bkup_07l21ukv_1_1


Step 5: Apply the Incremental Backup to the Standby Database

RMAN> recover database noredo;

Starting recover at 28-DEC-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=141 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/stby/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stby/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stby/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stby/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stby/rman01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/stby/rman02.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/new_incr/bkup_07l21ukv_1_1
channel ORA_DISK_1: piece handle=/u01/backup/new_incr/bkup_07l21ukv_1_1 tag=TAG20091228T143302
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished recover at 28-DEC-09

RMAN>



Step 6: Put the standby database back to managed recovery mode.

SQL> recover managed standby database nodelay disconnect;
Media recovery complete.

From the alert.log you will notice that the standby database is still looking for the old log files

*************************************************
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 137-137
DBID 768471617 branch 600609988
**************************************************

This is because the controlfile has not been updated.
Hence the standby controlfile has to be recreated

On the primary DATABASE

SQL> alter database create standby controlfile as
2  '/u01/control01.ctl';

Copy the standby control file to the standby site and restart the standby database in managed recovery mode...

NOW CHECK THE ARCHIVE LOG LIST ON BOTH PRIMARY AND STANDBY DATABASE,
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/archive
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence           22


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/oradata/archive
Oldest online log sequence     20
Next log sequence to archive   0
Current log sequence           22
SQL>

regards,
rajeshkumar g