Steps to perform for Rolling forward a standby database using RMAN Incremental Backup

Steps to perform for Rolling forward a standby database using RMAN Incremental Backup:

SOLUTION

1) On the standby database, stop the managed recovery process (MRP)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2) On the STANDBY DATABASE, find the SCN which will be used for the incremental backup at the primary database:

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> select min(fhscn) from x$kcvfh;

In ideal situation the above 2 queries will return the almost same SCN. However if there is huge difference its better to take backup using the SCN from second query (lesser SCN), as one of the datafile may be behind.

CURRENT_SCN 

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

3162298

3) In RMAN, connect to the PRIMARY database and create an incremental backup from the SCN derived in the previous step:

RMAN> BACKUP INCREMENTAL FROM SCN 3162298 DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';

4) Transfer all backup sets created on the primary system to the standby system.

scp /tmp/ForStandby_* standby:/tmp

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

using target database control file instead of recovery catalog 

searching for all files that match the pattern /tmp/ForStandby 

List of Files Unknown to the Database 

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

File Name: /tmp/ForStandby_2lkglss4_1_1 

File Name: /tmp/ForStandby_2mkglst8_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/ForStandby_2lkglss4_1_1 

File Name: /tmp/ForStandby_2mkglst8_1_1

5) Recover the STANDBY database with the cataloged incremental backup:

RMAN> RECOVER DATABASE NOREDO; 

starting recover at 03-JUN-09 

allocated channel: ORA_DISK_1 

channel ORA_DISK_1: sid=28 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: +DATA/mystd/datafile/system.297.688213333 

destination for restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335 

destination for restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333 

channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1 

channel ORA_DISK_1: restored backup piece 1 

piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY 

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

Finished recover at 03-JUN-09

6) In RMAN, connect to the PRIMARY database and create a standby control file backup:

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

7) Copy the standby control file backup to the STANDBY system. 

scp /tmp/ForStandbyCTRL.bck standby:/tmp

8) We now need to refresh the standby controlfile from primary controlfile (for standby) backup. However, since the datafile names are likely different than primary, let's save the name of datafiles on standby first, which we can refer after restoring controlfile from primary backup to verify if any discrepancy. So, run below query from Standby and save results for further use.

spool datafile_names_step8.txt

set lines 200

col name format a60

select file#, name from v$datafile order by file# ;

spool off

9) From RMAN, connect to STANDBY database and restore the standby control file:

RMAN> SHUTDOWN;

RMAN> STARTUP NOMOUNT; 

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck'; 

Starting restore at 03-JUN-09 

using target database control file instead of recovery catalog 

allocated channel: ORA_DISK_1 

channel ORA_DISK_1: sid=36 devtype=DISK 

channel ORA_DISK_1: restoring control file 

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

output filename=+DATA/mystd/controlfile/current.257.688583989 

Finished restore at 03-JUN-09

10) Shut down the STANDBY database and startup mount:

RMAN> SHUTDOWN; 

RMAN> STARTUP MOUNT;

11) Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.

Perform the below step  in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

RMAN> CATALOG START WITH '+DATA/mystd/datafile/'; 

List of Files Unknown to the Database 

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

File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773 

File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773 

File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775 

File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333 

File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333 

File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335 

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/mystd/DATAFILE/SYSTEM.297.688213333 

File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333 

File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335

If any datafiles have been added to Primary AFTER scn 3162298 they will also have to be restored to the standby host (see Note 1531031.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary) and cataloged as shown above before doing the switch.    To determine if any files have been added to Primary since the standby current scn:

SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298

 Otherwise:

RMAN> SWITCH DATABASE TO COPY; 

datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333" 

datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335" 

datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"

Note:

At this point, you can compare the query output from step 8) for any discrepancy (other than newly added datafiles)  by running the same query as in Step 8) to ensure we have all the datafiles added in standby.

12) If the STANDBY database needs to be configured for FLASHBACK use the below step to enable.

SQL> ALTER DATABASE FLASHBACK OFF; 

SQL> ALTER DATABASE FLASHBACK ON;

13) On standby database, clear all standby redo log groups:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

....

14) On the STANDBY database, start the MRP

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

**************************************************************

Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary 

************************************************************

FIX

1) On the standby database, stop the managed recovery process (MRP)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2) On the STANDBY DATABASE, find the SCN which will be used for the incremental backup at the primary database:

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> select min(checkpoint_change#) from v$datafile_header;

CHECKPOINT_CHANGE#

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

3162298

In ideal situation the above 2 queries will return the almost same SCN. However if there is huge difference its better to take backup using the SCN from second query (lesser SCN), as one of the datafile may be behind.

3)  In sqlplus, connect to the PRIMARY database and identify datafiles added :

SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > <SCN_NUMBER_FROM_STEP 2>;

4) Using rman, create backup of missing datafiles and an incremental backup using the SCN derived in the previous step:

RMAN> backup datafile #, #, #, # format '/tmp/ForStandby_%U' tag 'FORSTANDBY';

RMAN> backup incremental from SCN 3162298 database format '/tmp/ForStandby_%U' tag 'FORSTANDBY';

RMAN> backup current controlfile for standby format '/tmp/ForStandbyCTRL.bck';

 5) Transfer all backup sets created on the primary system to the standby system.

scp /tmp/ForStandby_* standby:/tmp

 6) restore new controlfile and catalog the backup transfered in step #5:

RMAN> shutdown;

RMAN> startup nomount;

RMAN> restore standby controlfile from '/tmp/ForStandbyCTRL.bck';

RMAN> alter database mount;

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

using target database control file instead of recovery catalog 

searching for all files that match the pattern /tmp/ForStandby 

List of Files Unknown to the Database 

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

File Name: /tmp/ForStandby_2lkglss4_1_1 

File Name: /tmp/ForStandby_2mkglst8_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/ForStandby_2lkglss4_1_1 

File Name: /tmp/ForStandby_2mkglst8_1_1

 7) restore missing datafiles:

run

{

set newname for datafile X to '+DISKGROUP';

set newname for datafile Y to '+DISKGROUP';

set newname for datafile Z to '+DISKGROUP';

etc.

restore datafile x,y,z,....;

}

8) Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.

Perform the below step  in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

RMAN> CATALOG START WITH '+DATA/mystd/datafile/'; 

List of Files Unknown to the Database 

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

File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773 

File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773 

File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775 

File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333 

File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333 

File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335 

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/mystd/DATAFILE/SYSTEM.297.688213333 

File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333 

File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335

 Once all files have been cataloged, switch the database to copy:

RMAN> SWITCH DATABASE TO COPY; 

datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333" 

datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335" 

datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"

9) Recover the STANDBY database with the cataloged incremental backup:

RMAN> RECOVER DATABASE NOREDO; 

starting recover at 03-JUN-09 

allocated channel: ORA_DISK_1 

channel ORA_DISK_1: sid=28 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: +DATA/mystd/datafile/system.297.688213333 

destination for restore of datafile 00002: +DATA/mystd/datafile/undotbs1.268.688213335 

destination for restore of datafile 00003: +DATA/mystd/datafile/sysaux.267.688213333 

channel ORA_DISK_1: reading from backup piece /tmp/ForStandby_2lkglss4_1_1 

channel ORA_DISK_1: restored backup piece 1 

piece handle=/tmp/ForStandby_2lkglss4_1_1 tag=FORSTANDBY 

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

Finished recover at 03-JUN-09

10)  Refresh the controlfile from the PRIMARY database again.  In primary create a standby control file backup:

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

11) Copy the standby control file backup to the STANDBY system. 

scp /tmp/ForStandbyCTRL.bck standby:/tmp

12) From RMAN, connect to STANDBY database and restore the standby control file:

RMAN> SHUTDOWN;

RMAN> STARTUP NOMOUNT; 

RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck'; 

Starting restore at 03-JUN-09 

using target database control file instead of recovery catalog 

allocated channel: ORA_DISK_1 

channel ORA_DISK_1: sid=36 devtype=DISK 

channel ORA_DISK_1: restoring control file 

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

output filename=+DATA/mystd/controlfile/current.257.688583989 

Finished restore at 03-JUN-09

Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.

Perform the below step  in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

RMAN> CATALOG START WITH '+DATA/mystd/datafile/'; 

List of Files Unknown to the Database 

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

File Name: +data/mystd/DATAFILE/SYSTEM.309.685535773 

File Name: +data/mystd/DATAFILE/SYSAUX.301.685535773 

File Name: +data/mystd/DATAFILE/UNDOTBS1.302.685535775 

File Name: +data/mystd/DATAFILE/SYSTEM.297.688213333 

File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333 

File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335 

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/mystd/DATAFILE/SYSTEM.297.688213333 

File Name: +data/mystd/DATAFILE/SYSAUX.267.688213333 

File Name: +data/mystd/DATAFILE/UNDOTBS1.268.688213335

 Once all files have been cataloged, switch the database to copy:

RMAN> SWITCH DATABASE TO COPY; 

datafile 1 switched to datafile copy "+DATA/mystd/datafile/system.297.688213333" 

datafile 2 switched to datafile copy "+DATA/mystd/datafile/undotbs1.268.688213335" 

datafile 3 switched to datafile copy "+DATA/mystd/datafile/sysaux.267.688213333"

 13) Shut down the STANDBY database and startup mount:

RMAN> SHUTDOWN; 

RMAN> STARTUP MOUNT;

 Note:

At this point, you can compare the query output from step 8) for any discrepancy (other than newly added datafiles)  by running the same query as in Step 8) to ensure we have all the datafiles added in standby.

14) If the STANDBY database needs to be configured for FLASHBACK use the below step to enable.

SQL> ALTER DATABASE FLASHBACK OFF; 

SQL> ALTER DATABASE FLASHBACK ON;

 15) On standby database, clear all standby redo log groups:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1; 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2; 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

....

 16) On the STANDBY database, start the MRP

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

*************************************************************