Archive for October, 2006


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/

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.