Rolling forward the standby database by using primary database incremental backups

Scenario :

The archive gap is noted in the standby database alertlog as below. The required archivelog for recovery is not found in the primary and is been deleted in both the primary and standby unfortunately.

In this case the below is the recovery needed to synchronize the standby with primary. This incremental recovery only works from 10g onwards.

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

Mon May 23 14:32:43 2011
FAL[client]: Failed to request gap sequence
 GAP - thread 1 sequence 174-174
 DBID 931617234 branch 747232149
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------

Check the current SCN in the standby, that will be needed to take backup from that SC Number.

---- standby ----

SQL> select distinct checkpoint_change# from v$datafile_header order by 1;

CHECKPOINT_CHANGE#
------------------
            739339


Take BACKUP of primary database from scn 739339 as below

---- primary ----


D:\oracle\product\10.2.0\db_1\BIN>rman target 'sys/****** as sysdba'

Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 23 14:44:30 2011

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

connected to target database: ORCL10G (DBID=931617234)

RMAN> backup incremental from scn 739339 database format 'd:\db\db_%U';

Starting backup at 23-MAY-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\USERS01.DBF
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\UNDOTBS01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 23-MAY-11
channel ORA_DISK_1: finished piece 1 at 23-MAY-11
piece handle=D:\DB\DB_0OMD2B30_1_1 tag=TAG20110523T144447 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-MAY-11
channel ORA_DISK_1: finished piece 1 at 23-MAY-11
piece handle=D:\DB\DB_0PMD2B77_1_1 tag=TAG20110523T144447 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAY-11

RMAN>


then copy the backup pieces to the standby server and catalog it and recover as below.


RMAN> catalog start with 'd:\db\';

searching for all files that match the pattern d:\db\

List of Files Unknown to the Database
=====================================
File Name: D:\DB\DB_0OMD2B30_1_1
File Name: D:\DB\DB_0PMD2B77_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: D:\DB\DB_0OMD2B30_1_1
File Name: D:\DB\DB_0PMD2B77_1_1

RMAN> recover database noredo;

Starting recover at 23-MAY-11
using channel ORA_DISK_1
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: D:\ORACLE\PRODUCT\10.2.0\ORADATA\STDBY10G\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\PRODUCT\10.2.0\ORADATA\STDBY10G\UNDOTBS01.DBF
destination for restore of datafile 00003: D:\ORACLE\PRODUCT\10.2.0\ORADATA\STDBY10G\SYSAUX01.DBF
destination for restore of datafile 00004: D:\ORACLE\PRODUCT\10.2.0\ORADATA\STDBY10G\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\DB\DB_0OMD2B30_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\DB\DB_0OMD2B30_1_1 tag=TAG20110523T144447
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished recover at 23-MAY-11

RMAN>

after recovery, alter the standby database to recovery mode and check the archive status

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

SQL> select sequence#,archived,applied,deleted from v$archived_log where completion_Time like '23-MAY-11';

 SEQUENCE# ARC APP DEL
---------- --- --- ---
       175 YES NO  NO
       176 YES NO  NO
       177 YES NO  NO
       178 YES NO  NO
       179 YES NO  NO
       180 YES NO  NO
       181 YES NO  NO
       182 YES NO  NO
       183 YES NO  NO
       184 YES NO  NO
       188 YES YES NO

 SEQUENCE# ARC APP DEL
---------- --- --- ---
       189 YES YES NO
       185 YES YES NO
       186 YES YES NO
       187 YES YES NO
       190 YES YES NO
       191 YES YES NO
       192 YES YES NO
       193 YES YES NO
       194 YES YES NO
       195 YES YES NO
       196 YES YES NO

 SEQUENCE# ARC APP DEL
---------- --- --- ---
       197 YES YES NO
       198 YES YES NO
       199 YES YES NO

25 rows selected.

now check the SCN number in the standby.

SQL> select distinct checkpoint_change# from v$datafile_header order by 1;

CHECKPOINT_CHANGE#
------------------
            747276

If the archive file is corrupt or lost we dont need to rebuild the standby in 10g now.

Regards,

Navaneeth