RMAN 'duplicate database' feature of Oracle9i, Oracle10g and Oracle 11G

posted Mar 16, 2011, 12:18 PM by Sachchida Ojha
You can use the RMAN DUPLICATE command to create a duplicate database from target database backups while still retaining the original target database.  The duplicate database can be either identical to the original database or contain only a subset of the original tablespaces.

A RAC TARGET database can be duplicated as well. The procedure is the same as below.If the auxiliary instance needs to be a RAC-database aswell, than start the duplicate procedure for to a single instance and convert the auxiliary to RAC after the duplicate has
succeeded.

To prepare for database duplication, you must first create an auxiliary instance.  For the duplication to work, you must connect RMAN to both the target (primary) database  and an auxiliary instance started in NOMOUNT mode.

You must have at least one auxiliary channel allocated on the auxiliary instance.  The principal work of the duplication is performed by the auxiliary channel,  which starts a server session on the auxiliary host.  This channel then restores the necessary backups of the primary database, uses them to create the duplicate database, and initiates recovery.

As part of the duplicating operation, RMAN manages the following:

   1. Restores the target datafiles to the duplicate database and performs incomplete recovery by using all available incremental backups and archived logs.
   2. Shuts down and starts the auxiliary database.
   3. Opens the duplicate database with the RESETLOGS option after incomplete recovery to create the online redo logs.
   4. Generates a new, unique DBID for the auxiliary database.


During duplication, RMAN must perform incomplete recovery because the online  redo logs in the target are not backed up and cannot be applied to the auxiliary database. The farest that RMAN can go in recovery of the duplicate database isthe most recent redo log archived by the target database.

When duplicating a database, you can do the following:

   1. Run the DUPLICATE command with or without a recovery catalog
   2. Skip read-only tablespaces with the SKIP READONLY clause. Read-only tablespaces are included by default.  If you omit them, then you can add them later.
   3. Exclude tablespaces from the auxiliary database with the SKIP TABLESPACE clause. You cannot skip the SYSTEM tablespace or tablespaces containing rollback or undo segments.
   4. Create the auxiliary database in a new host. If the directory structure is the same on the new host, then you can specify the NOFILENAMECHECK option and reuse the target datafile filenames for the auxiliary datafiles.
   5. Use the SET UNTIL command or DUPLICATE command with the UNTIL clause when creating the auxiliary database to recover it to a noncurrent time. By default, the DUPLICATE command creates the database by using the most recent backups of the target database and then performs recovery to the most recent consistent point contained in the incremental backups and archived logs.


Register the auxiliary database in the same recovery catalog as the target database. This option is possible because RMAN gives the duplicate database a new DBID during duplication.

Preparing the Auxiliary Instance for Duplication:
The used instance name for the AUXILIARY instance, in this example, is AUX but can be any other valid instancename.
Basic Steps
Perform these tasks before performing RMAN duplication:

Task 1: setenv ORACLE_SID AUX
Create an Oracle Password File for the Auxiliary Instance

Task 2: Ensure SQL*Net Connectivity to the Auxiliary Instance.
A SQL*Net connection to the Auxilary instance is not required, but recommended. As the setup of the SQL*Net connection is easier done for the auxiliary instance than for the TARGET.RMAN is connecting to the target and auxiliary instance with SYSDBA privileges. A SQL*Net connection using SYSDBA privileges requires a password file.

Task 3: Create an Initialization Parameter File for the Auxiliary Instance
Mandatory initialization parameter settings for the auxiliary database:

db_block_size = <same size as the target>
DB_NAME=AUX
compatible = 9.2.0.0 /* should be the same as the target
CONTROL_FILES=(/dup/oracle/oradata/trgt/control01.ctl,
/dup/oracle/oradata/trgt/control02.ctl)
#DB_FILE_NAME_CONVERT=('/oracle/oradata/trgt/','/dup/oracle/oradata/trgt/')
#LOG_FILE_NAME_CONVERT=('/oracle/oradata/trgt/redo','/dup/oracle/oradata/trgt/redo')


Task 4: Start the Auxiliary Instance NOMOUNT

SQL> CONNECT SYS/oracle@aux AS SYSDBA
     STARTUP FORCE NOMOUNT


Task 5: Mount or Open the Target Database
Task 6: Make Sure You Have the Necessary Backups and Archived Redo Logs
Task 7: Allocate Auxiliary Channels if Automatic Channels Are Not Configured

Start RMAN with a connection to the target database, the auxiliary database, and (if you use one) the recovery catalog database. You can start the RMAN executable on any host so long as it can connect to all the instances.

Note: If the auxiliary instance requires a client-side initialization parameter file, then this file must exist on the same host that runs the RMAN executable.

If automatic channels are not configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN block.  If the backups reside on disk, then the more channels you allocate, the faster the duplication will be.  For tape backups, limit the number of channels to the number of devices available for the operation.

This example assumes the following:

    * Using a recovery catalog.
    * The target database is on host1 and contains eight datafiles.
    * Duplicate the target to database aux on the same host.
    * Store the datafiles for AUX under /export/home/oracle/AUX/ subdirectory.
    * Exclude tablespace INDX from the duplicate database, but keep all of the other tablespaces.
    * Restore the duplicate db at a noncurrent time.
    * Two online redo logs groups, each with two members of size 10 Mb.
    * Configured the default device to disk.
    * The auxiliary instance AUX has initialization parameter file in the default location (so the PFILE parameter is not necessary on the DUPLICATE command).
    * Start RMAN from the AUX site.


RMAN> CONNECT TARGET system/system@R920.ca.oracle.com;
      CONNECT CATALOG rman/rman@T920.ca.oracle.com;
      CONNECT AUXILIARY SYS/oracle;

      CONFIGURE CHANNEL DEVICE TYPE disk CLEAR;
      CONFIGURE DEFAULT DEVICE TYPE TO disk;
      CONFIGURE DEVICE TYPE disk PARALLELISM 3;

# note that a RUN command is necessary because you can only execute SET NEWNAME
# within a RUN command
RUN {
# the DUPLICATE command uses an automatic disk channel
  set until time "to_date('Jan 29 2003 10:50:00','Mon DD YYYY HH24:MI:SS')";
  SET NEWNAME FOR DATAFILE 1 TO '/export/home/oracle/AUX/system01.dbf';
  SET NEWNAME FOR DATAFILE 2 TO '/export/home/oracle/AUX/undotbs01.dbf';
  SET NEWNAME FOR DATAFILE 3 TO '/export/home/oracle/AUX/example01.dbf';
  SET NEWNAME FOR DATAFILE 5 TO '/export/home/oracle/AUX/tools01.dbf';
  SET NEWNAME FOR DATAFILE 6 TO '/export/home/oracle/AUX/users01.dbf';
  SET NEWNAME FOR DATAFILE 7 TO '/export/home/oracle/AUX/logmnrts.dbf';
  SET NEWNAME FOR DATAFILE 8 TO '/export/home/oracle/AUX/marius01.dbf';

  DUPLICATE TARGET DATABASE TO AUX
  SKIP TABLESPACE indx
  LOGFILE
  GROUP 1 ('/export/home/oracle/AUX/redo01a.log',
           '/export/home/oracle/AUX/redo01b.log') SIZE 10M REUSE,
  GROUP 2 ('/export/home/oracle/AUX/redo02a.log',
           '/export/home/oracle/AUX/redo02b.log') SIZE 10M REUSE;
}



And this is what is going on:

executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting Duplicate Db at 31-JAN-03
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
Datafile 4 skipped by request

printing stored script: Memory Script
{
set until scn 5968565;
set newname for datafile 1 to
"/export/home/oracle/AUX/system01.dbf";
set newname for datafile 2 to
"/export/home/oracle/AUX/undotbs01.dbf";
set newname for datafile 3 to
"/export/home/oracle/AUX/example01.dbf";
set newname for datafile 5 to
"/export/home/oracle/AUX/tools01.dbf";
set newname for datafile 6 to
"/export/home/oracle/AUX/users01.dbf";
set newname for datafile 7 to
"/export/home/oracle/AUX/logmnrts.dbf";
set newname for datafile 8 to
"/export/home/oracle/AUX/marius01.dbf";
restore
check readonly
clone database
skip tablespace INDX ;
}
executing script: Memory Script

executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 31-JAN-03

using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /export/home/oracle/AUX/system01.dbf
restoring datafile 00003 to /export/home/oracle/AUX/example01.dbf
restoring datafile 00007 to /export/home/oracle/AUX/logmnrts.dbf
channel ORA_AUX_DISK_2: starting datafile backupset restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /export/home/oracle/AUX/undotbs01.dbf
restoring datafile 00005 to /export/home/oracle/AUX/tools01.dbf
restoring datafile 00006 to /export/home/oracle/AUX/users01.dbf
restoring datafile 00008 to /export/home/oracle/AUX/marius01.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/admin/R920/backup/DB_R920_01ee3suk_1_1 tag=TAG2003129T104747 params=NULL
channel ORA_AUX_DISK_1: restore complete
channel ORA_AUX_DISK_2: restored backup piece 1
piece handle=/u01/app/oracle/admin/R920/backup/DB_R920_02ee3suk_1_1 tag=TAG2003129T104747 params=NULL
channel ORA_AUX_DISK_2: restore complete
Finished restore at 31-JAN-03
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ( '/export/home/oracle/AUX/redo01a.log', '/export/home/oracle/AUX/reo01b.log' ) SIZE 10m REUSE,
GROUP 2 ( '/export/home/oracle/AUX/redo02a.log', '/export/home/oracle/AUX/reo02b.log' ) SIZE 10m REUSE
DATAFILE
'/export/home/oracle/AUX/system01.dbf'
CHARACTER SET WE8ISO8859P1


printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=484764999 filename=/export/home/oracle/AUX/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=484764999 filename=/export/home/oracle/AUX/example01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=3 stamp=484764999 filename=/export/home/oracle/AUX/toors01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=4 stamp=484764999 filename=/export/home/oracle/AUX/users01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=5 stamp=484764999 filename=/export/home/oracle/AUX/logmnrts.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=6 stamp=484764999 filename=/export/home/oracle/AUX/marius01.dbf

printing stored script: Memory Script
{
set until time "to_date('Jan 29 2003 10:50:00','Mon DD YYYY HH24:MI:SS')";
recover
clone database
delete archivelog
;
}
executing script: Memory Script

executing command: SET until clause

Starting recover at 31-JAN-03
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
datafile 4 not processed because file is offline

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=43
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/admin/R920/backup/AL_R920_03ee4185_1_1 tag=TAG2003129T120109 params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=/u01/app/oracle/product/9.2.0/dbs/arch1_43.dbf thread=1 seuence=43
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/9.2.0/dbs/arch1_43.dbf recid=1 stap=484765017
media recovery complete
Finished recover at 31-JAN-03

printing stored script: Memory Script
{
shutdown clone;
startup clone nomount ;
}
executing script: Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 52167600 bytes

Fixed Size 730032 bytes
Variable Size 50331648 bytes
Database Buffers 819200 bytes
Redo Buffers 286720 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 ( '/export/home/oracle/AUX/redo01a.log', '/export/home/oracle/AUX/re
o01b.log' ) SIZE 10m REUSE,
GROUP 2 ( '/export/home/oracle/AUX/redo02a.log', '/export/home/oracle/AUX/re
o02b.log' ) SIZE 10m REUSE
DATAFILE
'/export/home/oracle/AUX/system01.dbf'
CHARACTER SET WE8ISO8859P1


printing stored script: Memory Script
{
catalog clone datafilecopy "/export/home/oracle/AUX/undotbs01.dbf";
catalog clone datafilecopy "/export/home/oracle/AUX/example01.dbf";
catalog clone datafilecopy "/export/home/oracle/AUX/tools01.dbf";
catalog clone datafilecopy "/export/home/oracle/AUX/users01.dbf";
catalog clone datafilecopy "/export/home/oracle/AUX/logmnrts.dbf";
catalog clone datafilecopy "/export/home/oracle/AUX/marius01.dbf";
switch clone datafile all;
}
executing script: Memory Script

cataloged datafile copy
datafile copy filename=/export/home/oracle/AUX/undotbs01.dbf recid=1 stamp=48475037
cataloged datafile copy
datafile copy filename=/export/home/oracle/AUX/example01.dbf recid=2 stamp=48475038
cataloged datafile copy
datafile copy filename=/export/home/oracle/AUX/tools01.dbf recid=3 stamp=48476539
cataloged datafile copy
datafile copy filename=/export/home/oracle/AUX/users01.dbf recid=4 stamp=48476539
cataloged datafile copy
datafile copy filename=/export/home/oracle/AUX/logmnrts.dbf recid=5 stamp=48476039
cataloged datafile copy
datafile copy filename=/export/home/oracle/AUX/marius01.dbf recid=6 stamp=48476039
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=484765037 filename=/export/home/oracle/AUX/undotbs01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=484765038 filename=/export/home/oracle/AUX/example01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=3 stamp=484765039 filename=/export/home/oracle/AUX/tools01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=4 stamp=484765039 filename=/export/home/oracle/AUX/users01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=5 stamp=484765039 filename=/export/home/oracle/AUX/logmnrts.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=6 stamp=484765039 filename=/export/home/oracle/AUX/marius01.dbf

printing stored script: Memory Script
{
Alter clone database open resetlogs;
}
executing script: Memory Script

database opened

printing stored script: Memory Script
{
# drop offline and skipped tablespaces
sql clone "drop tablespace INDX including contents";
}
executing script: Memory Script

sql statement: drop tablespace INDX including contents
Finished Duplicate Db at 31-JAN-03

RMAN>
Comments