Archive missing ? Recover standby using roll forward standby with incremental backup

Here is the step by step document for recovering the standby if archive is missing and not recoverable, using a feature called rollforward standby using incremental backup introduced in 10g.

1) After recovery in step above login to sqlplus and get the current scn from standby database;

SQL> col CURRENT_SCN format 999999999999

SQL> select current_scn from v$database;

CURRENT_SCN

-------------

87261128900

SQL>

2) Take a incremental backup in production using above scn

RMAN> backup incremental from scn 87261128900 database format ‘/DBBACKUP/SCPRD/RMAN/incr_t%t_s%s_p%p' tag 'forstandby1'

3) Take a current control file backup also,

RMAN> backup current controlfile for standby format '/DBBACKUP/SCPRD/RMAN/Standbyctrl.ctl';

4) Moved backups to Standby using scp

5) catalog the new backups to standby database; At this moment your standby database is in mount mode;

RMAN> catalog start with '/DBBACKUP/SCPRD/RMAN';

using target database control file instead of recovery catalog

searching for all files that match the pattern /DBBACKUP/SCPRD/RMAN

List of Files Unknown to the Database

=====================================

File Name: /DBBACKUP/SCPRD/RMAN/incr_t729927310_s7510_p1

File Name: /DBBACKUP/SCPRD/RMAN/incr_t729925053_s7509_p1

File Name: /DBBACKUP/SCPRD/RMAN/Standbyctrl.ctl

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

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /DBBACKUP/SCPRD/RMAN/incr_t729927310_s7510_p1

File Name: /DBBACKUP/SCPRD/RMAN/incr_t729925053_s7509_p1

File Name: /DBBACKUP/SCPRD/RMAN/Standbyctrl.ctl

6) Initiate the recovery for making database upto latest with latest incremental backups, note the recover with noredo as it not generate any redo while recovery, this is required

RMAN> recover database noredo;

Starting recover at 17-SEP-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=651 devtype=DISK

channel ORA_DISK_1: starting incremental datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /SYSCTRL/SCPRD/system01.dbf

destination for restore of datafile 00002: /UNDO/SCPRD/undotbs01.dbf

destination for restore of datafile 00003: /SYSCTRL/SCPRD/sysaux01.dbf

destination for restore of datafile 00004: /SCPRDDATA/SCPRD/data01.dbf

destination for restore of datafile 00005: /SCPRDDATA/SCPRD/data02.dbf

destination for restore of datafile 00006: /SCPRDINDEX/SCPRD/index01.dbf

destination for restore of datafile 00007: /SCPRDDATA/SCPRD/patrol01.dbf

destination for restore of datafile 00008: /TEMP/SCPRD/tools.dbf

destination for restore of datafile 00009: /TEMP2/SCPRD/audit.dbf

channel ORA_DISK_1: reading from backup piece /DBBACKUP/SCPRD/RMAN/incr_t729925

7) Shut down the standby database;

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

8) Startup in nomount

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 2085296 bytes

Variable Size 369102416 bytes

Database Buffers 146800640 bytes

Redo Buffers 6299648 bytes

SQL> exit

10) Restore controlfile from latest backup  and mount the database in standby mode

RMAN> restore controlfile from '/DBBACKUP/SCPRD/RMAN/Standbyctrl.ctl';

Starting restore at 17-SEP-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=651 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output filename=/REDOCTRL/SCPRD/control01.ctl

output filename=/SYSCTRL/SCPRD/control02.ctl

output filename=/TEMP2/SCPRD/control03.ctl

Finished restore at 17-SEP-10

RMAN>

SQLPLUS> alter database mount standby database;

Database mounted.

11) Initiate recovery on standby

SQL> recover standby database using backup controlfile;

ORA-00279: change 87262758883 generated at 09/17/2010 05:46:29 needed for

thread 1

ORA-00289: suggestion : /ARCHIVE1/SCPRD/arch221936138553801.arc

ORA-00280: change 87262758883 for thread 1 is in sequence #22193

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 87262778312 generated at 09/17/2010 06:27:42 needed for

thread 1

ORA-00289: suggestion : /ARCHIVE1/SCPRD/arch221946138553801.arc

ORA-00280: change 87262778312 for thread 1 is in sequence #22194

ORA-00278: log file '/ARCHIVE1/SCPRD/arch221936138553801.arc' no longer needed

for this recovery

ORA-00308: cannot open archived log '/ARCHIVE1/SCPRD/arch221946138553801.arc'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

At this moment, if you want you can copy the above archive log from production if its there and keep recovery going on

12) Keep the database in managed recovery mode 

SQL> alter database recover managed standby disconnect from session;

Database altered.

13) Just a check of standby scn and production scn.

Standby SCN

SQL> select current_scn from v$database; SQL> select current_scn from v$database;

CURRENT_SCN

------------ -----------

87262780632

Production SCN

SQL> col current_scn format 99999999999 SQL> col current_scn format 99999999999

CURRENT_SCN

-----------------

87262778311 à Almost nearer