The Geek Diary

How to duplicate a Oracle Database to a previous Incarnation

by

NOTE: In 11.2, there is a new option to duplicate syntax, INCARNATION, which allows you to duplicate the database to a previous incarnation using the RMAN duplicate command. For example:

run {
allocate auxiliary channel ch1 type 'SBT_TAPE' parms'ENV=(NB_ORA_CLIENT=xxx.com,NB_ORA_SERV=xxx.com)';
allocate auxiliary channel ch2 type 'SBT_TAPE' parms'ENV=(NB_ORA_CLIENT=xxx.com,NB_ORA_SERV=xxx.com)';
allocate auxiliary channel ch3 type 'SBT_TAPE' parms'ENV=(NB_ORA_CLIENT=xxx.com,NB_ORA_SERV=xxx.com)';
duplicate database PROD DBID 1453775480 incarnation 132726542 to TEST until time "to_date('03-JAN-2015 01:36:00','dd-mon-YYYY HH24:MI:SS')";
release channel ch1 ;
release channel ch2 ;
release channel ch3 ;
}

In 11.2, you can run targetless duplicate in which RMAN will duplicate from a set of backups from any incarnation regardless of the current incarnation of the target.

Prior to 11.2, you cannot use the RMAN duplicate command to duplicate a database to a target’s previous incarnation. This is a restriction to the duplicate database. Attempting to do so may result in the following errors 

executing command: SET until clause

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 12/09/2004 11:14:58
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog

base incarnation is not current in recovery catalog

NOTE: starting with 10.2, if you can shutdown the target, you can reset the target database to the previous incarnation and proceed with the duplicate.

There are two ways to accomplish this duplicate, outlined below:

Workaround 1

NOTE: This is only possible if there is an OS backup of the controlfile before the resetlogs is issued on the target database. An RMAN backup of the controlfile or a backup using ‘alter database backup controlfile to trace’ will result in the following error: 

Starting Duplicate Db at 11-DEC-04

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/11/2004 05:06:38
RMAN-05513: Cannot duplicate, controlfile is not current or backup

STEPS:

  1. Copy the target parameter file and modify the parameter control_files to point to the OS backup of the controlfile.
  2. Startup mount target database with parameter file from step #1
  3. Startup the Auxiliary instance
  4. In RMAN, connect to catalog, target, auxiliary instance
  5. Reset database to previous incarnation.
  6. Run RMAN duplicate command
  7. Shutdown target instance
  8. Startup target instance with original parameter file
  9. In RMAN connect to catalog and target
  10. reset database to current incarnation.

Workaround 2

In this workaround, you are restoring the original database to a new location. Then the NID utility can be used to rename this new database and change it’s database id.

For clarity, the original database will be referred to as ‘orig_database’ and the restored database to the new location will be referred to as ‘dummy_database’. Note, both databases have the same name until step 8.

NOTE: If you use a catalog for your target, do NOT connect to the catalog for this procedure. Otherwise, the original database’s information within that catalog schema will be compromised.

STEPS:

1. Copy target parameter file and modify control_files to point to location for the duplicate ‘dummy_database’ database.

2. Startup nomount database (‘dummy_database’) with parameter file from step #1

3. In RMAN, connect target (‘dummy_database’) and restore a controlfile:

RMAN>  restore controlfile from '/location and name of controlfile backup/';

NOTE: You can use the original database to identify the controlfile backup needed for the incarnation you are attempting to duplicate. For example:

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 801701888 bytes

Fixed Size 2257520 bytes
Variable Size 494931344 bytes
Database Buffers 301989888 bytes
Redo Buffers 2523136 bytes

RMAN> restore controlfile from '/u01/backup/V1124/cf_scs12lrh_1_1';

Starting restore at 07-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/V1124/control01.ctl
Finished restore at 07-APR-17

4. reset database to previous incarnation.

5. restore and recover target database (‘dummy_database’) using ‘set newname’ to point to duplicate database location. Specify a desired ‘SET UNTIL’, either ‘until time’, ‘until SCN’, or ‘until sequence).

NOTE: If restoring to the same server, be sure to specify a new location for online logs which will be created with the ‘alter database open resetlogs’. Otherwise, the target database’s online redo log files will be overwritten.

For example: 

RMAN> run
{
set until logseq 5 thread 1;
set newname for datafile 1 to 'C:\ORACLE\ORADATA\CLON\SYSTEM01.DBF';
set newname for datafile 2 to 'C:\ORACLE\ORADATA\CLON\UNDOTBS01.DBF';
set newname for datafile 3 to 'C:\ORACLE\ORADATA\CLON\USERS01.DBF';
set newname for datafile 4 to 'C:\ORACLE\ORADATA\CLON\TOOLS01.DBF';
restore controlfile;
alter database mount;
restore database;
switch datafile all;
recover database;
sql "alter database rename file ''c:\oracle\ORADATA\V92\REDO01.LOG'' to ''c:\oracle\ORADATA\clon\REDO01.LOG''";
sql "alter database rename file ''c:\oracle\ORADATA\V92\REDO02.LOG'' to ''c:\oracle\ORADATA\clon\REDO02.LOG''";
sql "alter database rename file ''c:\oracle\ORADATA\V92\REDO03.LOG'' to ''c:\oracle\ORADATA\clon\REDO03.LOG''";
alter database open resetlogs;
}

6. Shutdown target (‘dummy_database’).

7. Startup mount target database (‘dummy_database’) with parameter file from step #1

/* Now we will rename DUMMY database and change it’s database id using NID */

8. On OS, issue NID to change the dbid and database name. The name here is the name you want for the ‘dummy_database’For example:

% nid TARGET=system/manager@V92 DBNAME=CLON

9. Shutdown target (‘dummy_database’)

/* Now we will startup the NEW database (CLON) with it’s correct name */

10. Startup duplicate database (‘dummy_database’) with it’s own parameter file (DB_NAME must be new database name, i.e., CLON). This would be the same parameter file as would be created for an auxiliary instance needed for an RMAN duplicate command.

Conclusion

Both the above options require the target database be shutdown if being restored to the same server. Often this is not feasible. However, if the duplicate database is on a different server, the target database does NOT have to be shutdown on the original server. Instead, create the dummy target instance on the same server where which the duplicate will reside.

If both duplicate and target databases must reside on the same server, you can use the LOCK_NAME_SPACE option to bring up the dummy instance while the original database is running. However, take extreme caution when using this parameter and be sure the ORACLE_SID is set to a different value.

In 10g, the LOCK_NAME_SPACE parameter has been deprecated, you should use DB_UNIQUE_NAME. Any RMAN operations should be suspended on ‘orig_database’ during this procedure. Otherwise, they will fail.

If executing rman remotely, tnsnames.ora (and other necessary sql*net configurations) must be performed. Take extreme caution when using this parameter as this may affect connection to the original target database (‘orig_database’).

You May Also Like