Roll Forward a Physical Standby 10gR2

This a follow up to the 11gR1 physical standby roll forward. The 10gR2 physical standby roll forward documentation has changed where as in the old one when there's a huge gap or missing archive logs which cannot be applied manually, creating a incremental backup and doing a database recover with noredo was sufficient to get the standby up to date.
There was no mention of restoring controlfile. (Source Oracle Data Guard Concepts and Administration 10g Release 2 (10.2) B14239-04 March 2006). If memory is correct this was even used to bring a production physical standby up to date (10.2.0.3).
But it appears this behavior has changed and few additional steps are required to bring the physical standby up to date according to the new documentation.

1. It is assumed there's working standby configuration and archive logs get apply without a problem

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APP
---------- ---
        87 YES
        88 YES
        89 YES
        90 YES
        91 YES
        92 YES

2. Shutdown the standby database and listener and run few log switches on the primary and then delete the archive logs to create a archive log gap between standby and primary

SEQUENCE# APP
---------- ---
        87 YES
        88 YES
        89 YES
        90 YES
        91 YES
        92 YES
       101 NO
       103 NO
       102 NO

3. Get the current scn on the standby

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    1569995

4. Create a incremental backup from the primary starting with the scn obtained above

RMAN> run {
2> allocate channel d1 device type disk format '/tmp/backup2%U';
3> allocate channel d2 device type disk format '/tmp/backup2%U';
4> BACKUP INCREMENTAL FROM SCN 1569995 database tag 'forstandby';
5> }

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: sid=1080 devtype=DISK

allocated channel: d2
channel d2: sid=1064 devtype=DISK

Starting backup at 25-Jan-2011 10:47:19
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00002 name=/data/oradata/ENT10G2/datafile/o1_mf_undotbs1_5q87v6wj_.dbf
input datafile fno=00003 name=/data/oradata/ENT10G2/datafile/o1_mf_sysaux_5q87v6v9_.dbf
channel d1: starting piece 1 at 25-Jan-2011 10:47:19
channel d2: starting full datafile backupset
channel d2: specifying datafile(s) in backupset
input datafile fno=00001 name=/data/oradata/ENT10G2/datafile/o1_mf_system_5q87v6v6_.dbf
input datafile fno=00005 name=/data/oradata/ENT10G2/datafile/o1_mf_example_5q87wcj2_.dbf
input datafile fno=00004 name=/data/oradata/ENT10G2/datafile/o1_mf_users_5q87v6wn_.dbf
channel d2: starting piece 1 at 25-Jan-2011 10:47:19
channel d1: finished piece 1 at 25-Jan-2011 10:47:20
piece handle=/tmp/backup21rm307dn_1_1 tag=FORSTANDBY comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 25-Jan-2011 10:47:20
channel d2: finished piece 1 at 25-Jan-2011 10:47:20
piece handle=/tmp/backup21sm307dn_1_1 tag=FORSTANDBY comment=NONE
channel d2: backup set complete, elapsed time: 00:00:01
channel d2: starting full datafile backupset
channel d2: specifying datafile(s) in backupset
channel d1: finished piece 1 at 25-Jan-2011 10:47:20
piece handle=/tmp/backup21tm307do_1_1 tag=FORSTANDBY comment=NONE
channel d1: backup set complete, elapsed time: 00:00:00
including current SPFILE in backupset
channel d2: starting piece 1 at 25-Jan-2011 10:47:20
channel d2: finished piece 1 at 25-Jan-2011 10:47:21
piece handle=/tmp/backup21um307do_1_1 tag=FORSTANDBY comment=NONE
channel d2: backup set complete, elapsed time: 00:00:01
Finished backup at 25-Jan-2011 10:47:21
released channel: d1
released channel: d2

5. Copy the backup files to the standby and catalog them

RMAN> CATALOG START WITH  '/tmp/backup2';

using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/backup2

List of Files Unknown to the Database
=====================================
File Name: /tmp/backup21um307do_1_1
File Name: /tmp/backup215m2mc3j_1_1
File Name: /tmp/backup21rm307dn_1_1
File Name: /tmp/backup21tm307do_1_1
File Name: /tmp/backup216m2mc3q_1_1
File Name: /tmp/backup21sm307dn_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: /tmp/backup21um307do_1_1
File Name: /tmp/backup215m2mc3j_1_1
File Name: /tmp/backup21rm307dn_1_1
File Name: /tmp/backup21tm307do_1_1
File Name: /tmp/backup216m2mc3q_1_1
File Name: /tmp/backup21sm307dn_1_1

6. Recover the standby database with noredo option

RMAN> recover database noredo;

Starting recover at 25-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1080 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 00002: /data/oradata/STDBY/datafile/o1_mf_undotbs1_6mx9srfw_.dbf
destination for restore of datafile 00003: /data/oradata/STDBY/datafile/o1_mf_sysaux_6mx9srh8_.dbf
channel ORA_DISK_1: reading from backup piece /tmp/backup21rm307dn_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/backup21rm307dn_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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: /data/oradata/STDBY/datafile/o1_mf_system_6mx9srfz_.dbf
destination for restore of datafile 00004: /data/oradata/STDBY/datafile/o1_mf_users_6mx9srk6_.dbf
destination for restore of datafile 00005: /data/oradata/STDBY/datafile/o1_mf_example_6mx9srj6_.dbf
channel ORA_DISK_1: reading from backup piece /tmp/backup21sm307dn_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/backup21sm307dn_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished recover at 25-JAN-11

7. According to old data guard documents this is the last step and you could start log apply and data guard configuration would start to work again. This was tested but it appeared this no longer works (DB version 10.2.0.5). To test enable log apply on standby

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Run few log switches on primary and observed the archive log apply status

 SEQUENCE# APP
---------- ---
        87 YES
        88 YES
        89 YES
        90 YES
        91 YES
        92 YES
       101 NO
       103 NO
       102 NO
       104 NO
       105 NO

Even though logs are shipped they do not get applied.

8. Rest of the steps on the new document was carried out after this. Cancel the log apply on standby and create a standby controlfile on primary. On standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On primary

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

Starting backup at 25-Jan-2011 10:51:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1074 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 25-Jan-2011 10:51:09
channel ORA_DISK_1: finished piece 1 at 25-Jan-2011 10:51:10
piece handle=/tmp/ForStandbyCTRL.bck tag=TAG20110125T105109 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-Jan-2011 10:51:10

9. Copy the controlfile created above to the standby and restore it and mount the database

SQL> shutdown immediate;
SQL> startup nomount;  
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

Starting restore at 25-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1091 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/data/flash/STDBY/controlfile/o1_mf_5q87w5tj.ctl
output filename=/data/oradata/STDBY/controlfile/o1_mf_5q87w5ts.ctl
Finished restore at 25-JAN-11

RMAN> alter database mount;

10. Catalog the datafiles and switch the standby database to newly catalog datafiles

RMAN> CATALOG START WITH '/data/oradata/STDBY/datafile';

Starting implicit crosscheck backup at 25-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1091 devtype=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 25-JAN-11

Starting implicit crosscheck copy at 25-JAN-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JAN-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /data/flash/STDBY/archivelog/2011_01_25/o1_mf_1_92_6mxcbrk0_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_25/o1_mf_1_104_6mxd4gl9_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_25/o1_mf_1_103_6mxcx355_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_25/o1_mf_1_91_6mxc8wj0_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_25/o1_mf_1_101_6mxcqwqp_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_25/o1_mf_1_102_6mxcx34s_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_25/o1_mf_1_105_6mxd4gnv_.arc
File Name: /data/flash/STDBY/control01.ctl
File Name: /data/flash/STDBY/backupset/2011_01_25/o1_mf_ncnnf_TAG20110125T110046_6mxccg2q_.bkp
File Name: /data/flash/STDBY/backupset/2011_01_25/o1_mf_nnndf_TAG20110125T110046_6mxcbytb_.bkp
File Name: /data/flash/STDBY/backupset/2011_01_25/o1_mf_annnn_TAG20110125T110044_6mxcbxk8_.bkp
File Name: /data/flash/STDBY/backupset/2011_01_25/o1_mf_ncnnf_TAG20110125T105531_6mxc142l_.bkp

searching for all files that match the pattern /data/oradata/STDBY/datafile

List of Files Unknown to the Database
=====================================
File Name: /data/oradata/STDBY/datafile/o1_mf_example_6mx9srj6_.dbf
File Name: /data/oradata/STDBY/datafile/o1_mf_users_6mx9srk6_.dbf
File Name: /data/oradata/STDBY/datafile/o1_mf_sysaux_6mx9srh8_.dbf
File Name: /data/oradata/STDBY/datafile/o1_mf_undotbs1_6mx9srfw_.dbf
File Name: /data/oradata/STDBY/datafile/o1_mf_system_6mx9srfz_.dbf

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

List of Cataloged Files
=======================
File Name: /data/oradata/STDBY/datafile/o1_mf_example_6mx9srj6_.dbf
File Name: /data/oradata/STDBY/datafile/o1_mf_users_6mx9srk6_.dbf
File Name: /data/oradata/STDBY/datafile/o1_mf_sysaux_6mx9srh8_.dbf
File Name: /data/oradata/STDBY/datafile/o1_mf_undotbs1_6mx9srfw_.dbf
File Name: /data/oradata/STDBY/datafile/o1_mf_system_6mx9srfz_.dbf    

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "/data/oradata/STDBY/datafile/o1_mf_system_6mx9srfz_.dbf"
datafile 2 switched to datafile copy "/data/oradata/STDBY/datafile/o1_mf_undotbs1_6mx9srfw_.dbf"
datafile 3 switched to datafile copy "/data/oradata/STDBY/datafile/o1_mf_sysaux_6mx9srh8_.dbf"
datafile 4 switched to datafile copy "/data/oradata/STDBY/datafile/o1_mf_users_6mx9srk6_.dbf"
datafile 5 switched to datafile copy "/data/oradata/STDBY/datafile/o1_mf_example_6mx9srj6_.dbf"

11. Clear the standby redo log files

SQL> alter database clear logfile group 1;
Database altered.

SQL> alter database clear logfile group 2;
Database altered.

SQL> alter database clear logfile group 3;
Database altered.

12. Clear flash back logs by turning flash back off and on

SQL> ALTER DATABASE FLASHBACK OFF;
Database altered.

SQL> ALTER DATABASE FLASHBACK ON;
Database altered.

12. Start the log apply and observe the archive log apply status

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.     

SQL> select sequence#,applied from v$archived_log order by 1 desc;

 SEQUENCE# APP
---------- ---
       105 YES
       104 YES
       103 YES
       102 NO
       101 NO
        92 NO
        91 NO

13. After few log switches on primary

SQL> alter system archive log current;

System altered.

SEQUENCE# APP
---------- ---
       107 YES
       106 YES
       105 YES
       104 YES
       103 YES
       102 NO
       101 NO
        92 NO
        91 NO

Standby is up to date with primary.