Roll Foward a Physical Standby on 11gR2

Previous posts showed how to do roll forward 10gR2 and 11gR1 physical standby databases.
This shows how to roll forward a 11gR2 physical standby that's in RAC configuration setup earlier.

In this case archive log files were deleted on primary and there's no other way to bring the standby up to date with the primary.
Current dataguard configuration is

DGMGRL> show configuration

Configuration - rac11g2_dgb

  Protection Mode: MaxPerformance
  Databases:
    rac11g2  - Primary database
    rac11g2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

1. Identify the archive gap

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         2           901            901

2. Stop all but one standby RAC instances

srvctl stop instance -d rac11g2s -i rac11g2s2

3. Stop log apply on the standby

DGMGRL> edit database rac11g2s set state='APPLY-OFF';
Succeeded.

4. Find the current SCN on the standby

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
   45716408

5. Using the SCN identified in the above step create an incrimental backup in the primary database

RMAN> BACKUP INCREMENTAL FROM SCN 45716408 DATABASE FORMAT '/home/oracle/forstandby%U' tag 'forstandby';

Starting backup at 20-OCT-11

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 instance=rac11g21 device type=DISK
backup will be obsolete on date 27-OCT-11
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=00002 name=+DATA/rac11g2/datafile/sysaux.257.740770047
input datafile file number=00001 name=+DATA/rac11g2/datafile/system.256.740770045
input datafile file number=00006 name=+DATA/rac11g2/datafile/undotbs3.276.745423577
input datafile file number=00005 name=+DATA/rac11g2/datafile/undotbs2.264.740770355
input datafile file number=00003 name=+DATA/rac11g2/datafile/abc.280.755611691
input datafile file number=00004 name=+DATA/rac11g2/datafile/users.259.740770049
channel ORA_DISK_1: starting piece 1 at 20-OCT-11
channel ORA_DISK_1: finished piece 1 at 20-OCT-11
piece handle=/home/oracle/forstandby2fmpj6s7_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

using channel ORA_DISK_1
backup will be obsolete on date 27-OCT-11
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 20-OCT-11
channel ORA_DISK_1: finished piece 1 at 20-OCT-11
piece handle=/home/oracle/forstandby2gmpj6tb_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-OCT-11

6. Copy the created backup files to standby using scp, ftp or any other file transfer utility.

7. Catalog backup files on standby

RMAN> catalog start with '/home/oracle/forstand';

using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/forstand

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/forstandby2fmpj6s7_1_1
File Name: /home/oracle/forstandby2gmpj6tb_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: /home/oracle/forstandby2fmpj6s7_1_1
File Name: /home/oracle/forstandby2gmpj6tb_1_1

8. Data guard concept and administration guide states "Connect to the standby database as the RMAN target and execute the REPORT SCHEMA statement to ensure that the standby database site is automatically registered and that the files names at the standby site are displayed". Doesn't say registered in what, but safe to assume talking about the recovery catalog. In this case recovery catalog is not used but run the command and make a note of the values as these will be used later on.

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RAC11G2S

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    720      SYSTEM               ***     +DATA/rac11g2s/datafile/system.258.754586581
2    1290     SYSAUX               ***     +DATA/rac11g2s/datafile/sysaux.259.754586467
3    10       ABC                  ***     +DATA/rac11g2s/datafile/abc.287.755615113
4    5        USERS                ***     +DATA/rac11g2s/datafile/users.276.754586727
5    150      UNDOTBS2             ***     +DATA/rac11g2s/datafile/undotbs2.261.754586713
6    600      UNDOTBS3             ***     +DATA/rac11g2s/datafile/undotbs3.262.754586657

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/rac11g2s/tempfile/temp.288.755615251

9. Restore the standby controlfile using the backup file (shown in bold on the backup output earlier) and mount the database

RMAN> restore standby controlfile from '/home/oracle/forstandby2gmpj6tb_1_1';

Starting restore at 20-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 instance=rac11g2s1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/rac11g2s/controlfile/current.257.754586439
output file name=+FLASH/rac11g2s/controlfile/current.482.754586439
Finished restore at 20-OCT-11

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

10. Run a report schema again and see that datafile names reported are the ones of the primary databae

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RAC11G2S

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     +DATA/rac11g2s/datafile/system.256.740770045
2    0        SYSAUX               ***     +DATA/rac11g2s/datafile/sysaux.257.740770047
3    0        ABC                  ***     +DATA/rac11g2s/datafile/abc.280.755611691
4    0        USERS                ***     +DATA/rac11g2s/datafile/users.259.740770049
5    0        UNDOTBS2             ***     +DATA/rac11g2s/datafile/undotbs2.264.740770355
6    0        UNDOTBS3             ***     +DATA/rac11g2s/datafile/undotbs3.276.745423577

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/rac11g2s/tempfile/temp.263.740770177

11. Catalog the datafiles to the original standby datafile names

RMAN> catalog start with '+DATA/rac11g2s/datafile';

searching for all files that match the pattern +DATA/rac11g2s/datafile

List of Files Unknown to the Database
=====================================
File Name: +data/RAC11G2S/DATAFILE/SYSAUX.259.754586467
File Name: +data/RAC11G2S/DATAFILE/SYSTEM.258.754586581
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS3.262.754586657
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS2.261.754586713
File Name: +data/RAC11G2S/DATAFILE/USERS.276.754586727
File Name: +data/RAC11G2S/DATAFILE/ABC.287.755615113

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/RAC11G2S/DATAFILE/SYSAUX.259.754586467
File Name: +data/RAC11G2S/DATAFILE/SYSTEM.258.754586581
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS3.262.754586657
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS2.261.754586713
File Name: +data/RAC11G2S/DATAFILE/USERS.276.754586727
File Name: +data/RAC11G2S/DATAFILE/ABC.287.755615113

12. Run switch database to copy to update the control file with the changes

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/rac11g2s/datafile/system.258.754586581"
datafile 2 switched to datafile copy "+DATA/rac11g2s/datafile/sysaux.259.754586467"
datafile 3 switched to datafile copy "+DATA/rac11g2s/datafile/abc.287.755615113"
datafile 4 switched to datafile copy "+DATA/rac11g2s/datafile/users.276.754586727"
datafile 5 switched to datafile copy "+DATA/rac11g2s/datafile/undotbs2.261.754586713"
datafile 6 switched to datafile copy "+DATA/rac11g2s/datafile/undotbs3.262.754586657"

13. Recover the database with no redo

RMAN> recover database noredo;

Starting recover at 20-OCT-11
using channel ORA_DISK_1
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: +DATA/rac11g2s/datafile/system.258.754586581
destination for restore of datafile 00002: +DATA/rac11g2s/datafile/sysaux.259.754586467
destination for restore of datafile 00003: +DATA/rac11g2s/datafile/abc.287.755615113
destination for restore of datafile 00004: +DATA/rac11g2s/datafile/users.276.754586727
destination for restore of datafile 00005: +DATA/rac11g2s/datafile/undotbs2.261.754586713
destination for restore of datafile 00006: +DATA/rac11g2s/datafile/undotbs3.262.754586657
channel ORA_DISK_1: reading from backup piece /home/oracle/forstandby2fmpj6s7_1_1
channel ORA_DISK_1: piece handle=/home/oracle/forstandby2fmpj6s7_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished recover at 20-OCT-11

14. At this stage should have cleared the log files as in step 10 herebut still without clearing the old logs when log apply is enabled new log files will be created automatically. Old log files will remain in the ASM taking up space and could be manually removed using asmcmd rm.

15. Enable log apply on the standby and verify log files are getting applied

DGMGRL> edit database rac11g2s set state='APPLY-ON';

   THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
         2        907 YES
         2        908 YES
         2        909 YES
         2        910 YES
         2        910 YES

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.

Roll Foward a Physical Standby on 11gR1

Incremental backups could be used to roll forward a physical standby that lags far behind the primary or has archivelog gaps that cannot be resolved manually (archive log could be already deleted). The steps on this are documented on Oracle Data Guard Concepts and Administration. But this doesn't work exactly as it is. Also these steps are bit different from the original 10gR2 documentation which only specified taking a incremental backup and doing a noredo recovery. There was no need to restore the control file. But in this 11gR1 documents as well as current 10gR2 documents it's mentioned to restore the standby controlfile.

It is assumed there's a valid data guard configuration and it's functioning properly. Then a missing archive log situation is created by shutting down the standby DB,defering the log shipping and deleting the logs created using rman.

1. Once the standby is started after this and log shipping is enabled, expect to see a archive log gap.

SEQUENCE# APPLIED
---------- ---------
      1187 NO
      1179 YES
      1178 YES
      1177 YES
      1176 YES
      1175 YES
      1174 YES
      1173 YES
      1172 YES

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1          1179           1186

2. Cancel the log apply service and get the current scn of the standby database.

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
   12197632

3. Create an incremental backup on the standby starting with the scn obtain above. It helps to have automatic controlfile backup on at this stage.

RMAN> BACKUP INCREMENTAL FROM SCN 12197632 DATABASE FORMAT '/home/oracle/forstandby%U' tag 'forstandby';

Starting backup at 21-Jan-2011 14:45:59

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1056 device type=DISK
backup will be obsolete on date 28-Jan-2011 14:46:00
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=00006 name=/data/oradata/ENT11G1/datafile/o1_mf_ravelbo_5zb2lltr_.dbf
input datafile file number=00007 name=/data/oradata/ENT11G1/datafile/o1_mf_bxindex_5zb2lzvc_.dbf
input datafile file number=00008 name=/data/oradata/ENT11G1/datafile/o1_mf_bxlobs_5zb2mf60_.dbf
input datafile file number=00002 name=/data/oradata/ENT11G1/datafile/o1_mf_sysaux_5q7x746p_.dbf
input datafile file number=00001 name=/data/oradata/ENT11G1/datafile/o1_mf_system_5q7x745y_.dbf
input datafile file number=00003 name=/data/oradata/ENT11G1/datafile/o1_mf_undotbs1_5q7x746t_.dbf
input datafile file number=00004 name=/data/oradata/ENT11G1/datafile/o1_mf_users_5q7x747g_.dbf
input datafile file number=00005 name=/data/oradata/ENT11G1/datafile/o1_mf_example_5q7xg0kk_.dbf
channel ORA_DISK_1: starting piece 1 at 21-Jan-2011 14:46:00
channel ORA_DISK_1: finished piece 1 at 21-Jan-2011 14:47:45
piece handle=/home/oracle/forstandby12m2m3t8_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/data/oradata/ENT11G1/datafile/o1_mf_tbx2ktbs_5zb2t0jn_.dbf
channel ORA_DISK_1: starting piece 1 at 21-Jan-2011 14:47:45
channel ORA_DISK_1: finished piece 1 at 21-Jan-2011 14:47:48
piece handle=/home/oracle/forstandby13m2m40h_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

using channel ORA_DISK_1
backup will be obsolete on date 28-Jan-2011 14:47:48
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 21-Jan-2011 14:47:49
channel ORA_DISK_1: finished piece 1 at 21-Jan-2011 14:47:50
piece handle=/home/oracle/forstandby14m2m40k_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-Jan-2011 14:47:50

4. Copy the incrmental backup files from the primary db server to standby db server and catalog them by running the following command on standby.

RMAN> catalog start with '/home/oracle/forstand';

Starting implicit crosscheck backup at 21-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1069 device type=DISK
Finished implicit crosscheck backup at 21-JAN-11

Starting implicit crosscheck copy at 21-JAN-11
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 21-JAN-11

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/forstand

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/forstandby14m2m40k_1_1
File Name: /home/oracle/forstandby13m2m40h_1_1
File Name: /home/oracle/forstandby12m2m3t8_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: /home/oracle/forstandby14m2m40k_1_1
File Name: /home/oracle/forstandby13m2m40h_1_1
File Name: /home/oracle/forstandby12m2m3t8_1_1

5. Start the standby database in nomount mode

RMAN> startup force nomount;

Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2166536 bytes
Variable Size                788529400 bytes
Database Buffers            3472883712 bytes
Redo Buffers                  12201984 bytes

6. The next step on the oracle documentation is to restore the standby controlfile but this will not work (unless connected to recovery catalog)

RMAN> RESTORE STANDBY CONTROLFILE FROM TAG 'forstandby';

Starting restore at 21-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1088 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/21/2011 15:13:58
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

7. Create the standby controlfile from one of the incrimental backup that was identified earlier as the one carrying the controlfile backup

RMAN> restore standby controlfile from '/home/oracle/forstandby14m2m40k_1_1';

Starting restore at 21-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/oradata/STDBY/controlfile/control01.ctl
Finished restore at 21-JAN-11

8. Mount the database and run the recover noredo command. In cases with OMF or ASM useage it might require datafiles to be renamed using alter database rename file ...

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database noredo;

Starting recover at 21-JAN-11
Starting implicit crosscheck backup at 21-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1075 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 21-JAN-11

Starting implicit crosscheck copy at 21-JAN-11
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 21-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_21/o1_mf_1_1176_6mm828b5_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1179_6mm829sr_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1178_6mm8293o_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1172_6mm7rk5o_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1173_6mm7rkhd_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1174_6mm7rp6j_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1187_6mm8d368_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1175_6mm827ww_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1177_6mm828oz_.arc
File Name: /data/flash/STDBY/archivelog/2011_01_21/o1_mf_1_1186_6mm8f5jb_.arc

using channel ORA_DISK_1
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: /data/oradata/STDBY/datafile/o1_mf_system_5q7x745y_.dbf
destination for restore of datafile 00002: /data/oradata/STDBY/datafile/o1_mf_sysaux_5q7x746p_.dbf
destination for restore of datafile 00003: /data/oradata/STDBY/datafile/o1_mf_undotbs1_5q7x746t_.dbf
destination for restore of datafile 00004: /data/oradata/STDBY/datafile/o1_mf_users_5q7x747g_.dbf
destination for restore of datafile 00005: /data/oradata/STDBY/datafile/o1_mf_example_5q7xg0kk_.dbf
destination for restore of datafile 00006: /data/oradata/STDBY/datafile/o1_mf_ravelbo_5zb2lltr_.dbf
destination for restore of datafile 00007: /data/oradata/STDBY/datafile/o1_mf_bxindex_5zb2lzvc_.dbf
destination for restore of datafile 00008: /data/oradata/STDBY/datafile/o1_mf_bxlobs_5zb2mf60_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/forstandby12m2m3t8_1_1
channel ORA_DISK_1: piece handle=/home/oracle/forstandby12m2m3t8_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
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 00009: /data/oradata/STDBY/datafile/o1_mf_tbx2ktbs_5zb2t0jn_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/forstandby13m2m40h_1_1
channel ORA_DISK_1: piece handle=/home/oracle/forstandby13m2m40h_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 21-JAN-11

9. At the end of the recovery enable log apply and check the apply status of the archive logs

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# APPLIED
---------- ---------
      1188 IN-MEMORY
      1187 NO
      1186 NO
      1179 NO
      1178 NO
      1177 NO
      1176 NO
      1175 NO
      1174 NO
      1173 NO
      1172 NO

10. Run several log switches on the primary and check the log apply status on the standby to confrim logs are applied

SQL> alter system switch logfile;

System altered.    

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

 SEQUENCE# APPLIED
---------- ---------
      1193 IN-MEMORY
      1192 YES
      1191 YES
      1190 YES
      1189 YES
      1188 YES
      1187 NO
      1186 NO
      1179 NO
      1178 NO
      1177 NO

More on metalink note Roll Forward a Physical Standby Database Using RMAN Incremental Backups "Recover Database Noredo" Does Nothing [ID 841765.1]