Archive:ORACLE Duplicate Database

This article describes briefly the duplication of the database SOURCE to the database RESTORE.

Note: A detailed description is given in the article Oracle Restore in Instance.

Prerequisites

• Both databases are located on the same ORACLE host under separate instances SOURCE and RESTORE with different paths or on separate hosts.
• The instance for RESTORE must be set up.
  • Operating system authentication or password files.
  • Initialization parameter files PFILE or SPFILE (init....ora file for instance RESTORE).
  • The necessary directories for the datafiles, archived redo logs and dump destinations must be existing.
  • The new instance RESTORE must be started: startup nomount.
• The connectivity to the new instance must be set up either via environment variable ORACLE_SID=RESTORE or
  • $ORACLE_HOME/network/admin/listener.ora with an entry for RESTORE.
  • $ORACLE_HOME/network/admin/tnsnames.ora with an entry for RESTORE.
  • The SQL*Net listener must be reloaded, e.g. by the command 'lsnrctl reload'.
• Successful executed backups of database SOURCE must exist with RMAN backup catalog or with storing backup history to database SOURCE's controlfile (RMAN nocatalog).
  • Full backup of datafiles with controlfile (rman 'CONFIGURE CONTROLFILE AUTOBACKUP ON')
  • Archived redo log backup

Steps to duplicate a database from backup

• A RMAN command file for the duplicating process must be created (e.g. 'duplicate_SOURCE.rman')
  • The type must be set to 'SBT' (or 'SBT_TAPE') and the 'parms ENV=(...)' must be set to the SESAM backup task specific values.
  • The keyword 'AUXILIARY' in the allocate channel command specifies a connection between RMAN and the new instance RESTORE.
  • 'SET NEWNAME' is used to rename datafiles and tempfiles during the restore. To produce a list of file ids and filenames, you may use the output of RMAN report schema.
  • The syntax for the logfile options is the same used in the create database command.

Example:

run {
allocate auxiliary channel t1 type 'SBT' parms
'ENV=(SESAM_SERVER=qsstor,SESAM_JOB=ORACLE_SOURCE,SESAM_DRIVE=,SESAM_POOL=7days,SOB_TRACE=0,SOB_LOGFILE=/tmp/restore.log)';
set newname for datafile 1 to '/opt/oracle/restore/RESTORE/system01.dbf';
set newname for datafile 2 to '/opt/oracle/restore/RESTORE/sysaux01.dbf';
set newname for datafile 3 to '/opt/oracle/restore/RESTORE/undotbs01.dbf';
set newname for datafile 4 to '/opt/oracle/restore/RESTORE/users01.dbf';
set newname for datafile 5 to '/opt/oracle/restore/RESTORE/example01.dbf';
set newname for datafile 6 to '/opt/oracle/restore/RESTORE/sesamdata.dbf';
ser newname for tempfile 1 to '/opt/oracle/restore/RESTORE/tempfile01.dbf'; 
duplicate target database to 'RESTORE'
logfile
'/opt/oracle/restore/RESTORE/redo01.log' SIZE 50M,
'/opt/oracle/restore/RESTORE/redo02.log' SIZE 50M,
'/opt/oracle/restore/RESTORE/redo03.log' SIZE 50M;
}

• Invoke RMAN to duplicate the database. RMAN must get the metadata from either SOURCE or the RMAN catalog and must be connected to the new database RESTORE. There are several ways to get the metadata depending on the backup strategy (here: ORACLE_SID is set to 'RESTORE'):
  • Target, but no catalog. Metadata comes from target database controlfile: $ rman TARGET sys/password@SOURCE AUXILIARY /
  • Catalog, but no target. Metadata comes from the RMAN catalog: $ rman CATALOG rman/password@rman-catalog AUXILIARY /
  • Target and catalog. Metadata can come from the target controlfile or the catalog: $ rman TARGET sys/password@SOURCE CATALOG rman/password@rman-catalog AUXILIARY /
  • No target or catalog. Metadata comes from backups: $ rman AUXILIARY /
• The script must be executed from rman: @duplicate_SOURCE.rman

Hint: To duplicate a database for standby the 'DUPLICATE' command must be used with the 'FOR STANDBY' clause, in which case RMAN does not create a unique DBID and does not open the database.