Popular Posts

Wednesday, January 14, 2009

clone Oracle database using rman

Clone an Oracle database using RMAN duplicate (same server)
tnsManager - Distribute tnsnames the easy way and for free!

This procedure will clone a database onto the same server using RMAN duplicate.

* 1. Backup the source database.
To use RMAN duplicate an RMAN backup of the source database is required. If there is already one available, skip to step 2. If not, here is a quick example of how to produce an RMAN backup. This example assumes that there is no recovery catalog available:

rman target sys@ nocatalog

backup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s';

This will backup the database and archive logs. The format string defines the location of the backup files. Alter it to a suitable location.

* 2. Produce a pfile for the new database
This step assumes that the source database is using a spfile. If that is not the case, simply make a copy the existing pfile.

Connect to the source database as sysdba and run the following:

create pfile='init.ora' from spfile;

This will create a new pfile in the $ORACLE_HOME/dbs directory.

The new pfile will need to be edited immediately. If the cloned database is to have a different name to the source, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary.

Because in this example the cloned database will reside on the same machine as the source, Oracle must be told how convert the filenames during the RMAN duplicate operation. This is achieved by adding the following lines to the newly created pfile:


Here is an example where the source database scr9 is being cloned to dg9a. Note the trailing slashes and lack of quotes:


* 3. Create bdump, udump & cdump directories
Create bdump, udump & cdump directories as specified in the pfile from the previous step.

* 4. Add a new entry to oratab, and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.

Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:


If this doesn't output the new database sid go back and investigate why not.

* 5. Create a password file
Use the following command to create a password file (add an appropriate password to the end of it):

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=

* 6. Duplicate the database
From sqlplus, start the instance up in nomount mode:

startup nomount

Exit sqlplus, start RMAN and duplicate the database. As in step 1, it is assumed that no recovery catalog is available. If one is available, simply amend the RMAN command to include it.

rman target sys@ nocatalog auxiliary /

duplicate target database to ;

This will restore the database and apply some archive logs. It can appear to hang at the end sometimes. Just give it time - I think it is because RMAN does a 'shutdown normal'.

If you see the following error, it is probably due to the file_name_convert settings being wrong. Return to step 2 and double check the settings.

RMAN-05001: auxiliary filename '%s' conflicts with a file used by the target database

Once the duplicate has finished RMAN will display a message similar to this:

database opened
Finished Duplicate Db at 26-FEB-05


Exit RMAN.

* 7. Create an spfile
From sqlplus:

create spfile from pfile;

shutdown immediate

Now that the clone is built, we no longer need the file_name_convert settings:

alter system reset db_file_name_convert scope=spfile sid='*'

alter system reset log_file_name_convert scope=spfile sid='*'

* 8. Optionally take the clone database out of archive log mode
RMAN will leave the cloned database in archive log mode. If archive log mode isn't required, run the following commands from sqlplus:

shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

* 9. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.