Duplicate a database using RMAN

Like I said before, RMAN is a great tool. It is possible to backup the database and recover with simple commands or create a duplicate database using that tool.

I’ve been trying the duplicate command recently.

You will need this duplication ability if you got a database for training purpose. This database must be fresh to train your users with the latest customers information, products etc… and if it’s not possible to perform a cold backup because your system is critical.

To help yourself, use this document: Backup and Recovery Advanced User’s Guide

I’m going to call the live db’s host PRIMARY and the duplicated database’s host AUXILIARY. Those are the terms used by RMAN, so let’s get used to it.

To make things simple, it is a good idea to use the same server architecture on both server. This will avoid the renaming of files.

Below is a list of things to do:

1)On the PRIMARY host

  • add the option UR=A in the CONNECT_DATA part of your tnsnames.ora entry to connect to your auxiliary database. It should look like this:


  • Perform a backup with RMAN using the following command: backup database plus archivelog not backed up 1 times;
  • Move the backup folder from PRIMARY  to AUXILIARY host. Use the same folder name as on the PRIMARY host.

2) On the AUXILIARY host

RMAN does not recreate the folder structure as it is on production automatically (sadly) so it has to be done manually. They are:

  • Datafiles folders
  • Control files folders
  • Traces folder
  • Archives log folder
  • pfile (if you do it like me, you got a pfile folder where the SPFILE is located. Then in oracle_home\database you got a file called initSID.ora and inside this file the following line: SPFILE=[SPFILE location\SPFILESID.ora]

The oracle service must be created on first hand. So, copy the SPFILE from PRIMARY to AUXILIARY and move it in the same location as on your PRIMARY server.

Then create the service using oradim command:

If you want to create a new password for the sys user the use the -syspwd option as in this example. Otherwise, just copy the password file from PRIMARY. It’s located in [ORACLE_HOME]\database

C:\>oradim -NEW -SID [as on PRIMARY] -syspwd “acomplexepassword” -startmode auto

Create your listener using net services. This is pretty straight forward, so I won’t document it here.

Create your tnsnames.ora and sqlnet.ora for this new database. If you want to use implicit connection when logging in locally, use the following in your sqlnet.ora:


With the BEQ option, it is possible if logging in on windows with a user which belongs to ora_dba group, to connect locally without providing usernames and passwords.

When this is done it is possible to connect to the database with SQLplus and start the database in nomount mode.

C:\set oracle_sid=SameAsPRIMARY

C:\sqlplus / as sysdba (that’s thanks to BEQ in sqlnet.ora)

SQL>startup nomount

if the the database won’t start with an error telling it’s not finding the spfile, then point to it

SQL>startup nomount spfile=path\SPFILESID.ora

3) Go back to PRIMARY

If everything has been prepared carefully, you should be able to connect to PRIMARY and AUXILIARY with RMAN from PRIMARY

C:\set oracle_sid=PRIMARY

C:\rman target / auxiliary sys/acomplexepassword@tnsentry.world

As you can see, we are connecting to PRIMARY without username and password but because we connect to AUXILIARY remotely, username and password is required.


With this command we allocate 3 auxiliary channels which will be used for the duplication.

If you have been using the same SID for AUXILIARY as the one from PRIMARY, then it must be a bit scary and confusing (hence the SameAsPRIMARY naming). It’s reassuring to know that the duplication won’t work if the database is open. RMAN will just say that to run this command the database must be in nomount mode and return an error. Plus the command will connect only to auxiliary (specified at connection time) using the tnsnames value used in the connection.

Since RMAN is using the production repository to duplicate the database, it will try to recover the data with the latest archive logs created on production. But they have been moved manually and not existing in the AUXILIARY environment. If RMAN could move the archive logs continuously it would be a standby DB. So RMAN sends an error saying that it’s not finding a couple of archive logs.

The duplicate command must be run with the UNTIL TIME option.

I got a lot of trouble using my own dates because it has to fit the NLS_DATE_FORMAT parameter. I’ve tried to play with the NLS_DATE_FORMAT to fit my format but it wouldn’t work anyway.

The best way I’ve found is to specify UNTIL TIME sysdate – X.

sysdate -1 is one day before. The duplication would be one day old. 1/24 = 0.04 would be one hour before current state of PRIMARY database. I haven’t tried the 1/24 in my UNTIL TIME option. If someone try this UNTIL TIME sysdate – 1/24, let me know the result. I would be interested.


8 Responses to “Duplicate a database using RMAN”

  1. March 16, 2007 at 2:02 am

    I’ve been reported the following error:

    RMAN-02002: unexpected end of input file reached
    Cause: This is probably caused by failure to supply the closing quote for a quoted string.

    Action: Correct the input

    This was due to a missing ‘ in the script above at the UNTIL command. Make sure that you got all the quotes required.

  2. April 4, 2007 at 7:23 am

    The command UNTIL sysdate – 0.04 goes back 1 hour before system date so if your backup is 4 hours old, it might be a good idea to do sysdate – 0.2 (5 hours sooner) instead… Otherwise your database will be recovered but fail to go to the end of the duplication because archivelogs are missing (it’s using production repository). It’s still ok to open the database but it has to be done manually with the resetlogs command

    alter database open resetlogs;

  3. November 19, 2007 at 8:16 am

    Follow these guidelines and you will build that new home with little, or no, problems. four season sun room can help…

  4. 4 raeqwest
    April 30, 2008 at 8:51 pm

    my question is, I cannot connect to my auxiliary database, since my listener is blocking all connections, what to do..

  5. May 26, 2009 at 8:37 pm


    I’ve been trying to clone an existing database in the same host with a different name with no luck.

    I’ve tried what you explain but there is one big issue which I don’t know how to solve.


    When I tried to restores the controlfile, obviously failed.

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of alter db command at 05/26/2009 14:51:15
    ORA-01103: database name ‘OLD_DB_NAME’ in control file is not ‘NEW_DB_NAME’.

    I just want to clone a database with a different name, in the same way we used to do it with ALTER DATBASE BACKUP CONTROLFILE TO TRACE –> COPY DB_FILES –> RECREATE CONTROLFILES WITH A DIFFERENT DATABASE NAME.

    Isn’t there any way to do this with RMAN?


  6. September 19, 2013 at 4:33 pm

    I think the admin of this site is truly working hrd in support of his web site, since
    here everyy stuftf iis quality based data.

  7. July 9, 2014 at 3:48 am

    certainly like your web site but you have to test the spelling
    on quite a few of your posts. A number of them
    are rife with spelling issues and I find it very troublesome to tell the truth however I’ll
    surely come back again.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: