Oracle DBA – A lifelong learning experience

  • Meta

  • Categories

  • Blog Stats

    • 1,653,093 hits
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 377 other followers

  • Advertisements

Cloning a database (ASM to ASM)

Posted by John Hallas on April 2, 2009

Cloning production ASM (RAC)  to ASM (non RAC)

In this document I will be cloning database SOURCEDB ( RAC) to TARGETDB ( single instance). There will be no downtime on the source database and minimal impact.

Source server source_server  150Gb data ASM storage used

Target server target_server

Pre-requirements -ensure that you have backed up the source database and are comfortable that you are covered in case anything goes wrong

200Gb of disk space

NFS mount between the 2 servers. In this case I created a directory /app/oradata/rman_backup_dir and arranged for a NFS mount to the same filesystem on target_server


403701760 42657229 338479316   11% /app/oradata/rman_backup_dir

The steps we will follow will be :-

  • Prepare database on target server
  • Backup source database
  • Run duplicate database scripts

Note 1

RMAN uses a terminology of target as being the source database and auxiliary as the duplicated database. In this document I am using the terms source and target for the two databases. However the RMAN script will use target and auxiliary instead out of necessity.

Note 2

Initially I wanted to use active duplicate database however whilst it did work once it failed many more times and there is an Oracle bug Bug 6603587 – CREATION OF SECOND STANDBY USING RMAN DUPLICATE FAILS WITH RMAN-20242

I then tried using non-active database and with a database backup. This time I hit Bug.5653255: PHSB: RMAN CAN\’T RESTORE DATAFILES CORRECTLY ON ASM USING IMAGE COPIES. The workaround to this was to use a backupset..

Prepare target database

I am assuming that we have an existing database built on the target server and we are just performing a refresh from production. However for completeness this section shows how to prepare the spfile and startup nomount the target database.

If this process is a refresh rather than new build then go to here

Create a tnsnames.ora entry on the source server

Note that we are using port 1524 whereas the source database uses port 1522




(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))







Create a listener on the target server

Note that we are using port 1524 whereas the source database uses port 1522




(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))







(ORACLE_HOME = /app/oracle/product/11.1.0/db_1/)




Start the listener

lsnrctl start listenerclone

On source_server

create pfile=\’/home/oracle/initTARGETDB.ora\’ from spfile;

Create pfile – target_server. Edit to remove references to RAC (threads etc). As I am moving from DATA/FRA diskgroup to DATA/FRA diskgroup I have no need to use any covert file_name parameters. Edit the spfile to replace the source SID name with the new SID

Edit /etc/oratab to add database entry

Copy the pfile from source_server


scp oracle@source_server:/home/oracle/initTARGETDB.ora .

Create audit dest on targetserver

mkdir -p /app/oracle/admin/TARGETDB/adump

Copy passwordfile from source_server

scp oracle@source_server:/app/oracle/product/11.1.0/db_1/orapwSOURCEDB /app/oracle/product/11.1.0/db_1/orapwTARGETDB

Startup standby database from targetserver

sqlplus  / as sysdba

create spfile from pfile;

startup nomount


Start the clone listener on target_server.

This is required as you cannot connect to a database that is unavailable with the asm listener

lsnrctl start listenerclone

From the primary database server source_server test the connection to the target database using the command:

sqlplus sys/password@TARGETDB_CLONE as sysdba

Prepare refreshed database

sqlplus  / as sysdba

shutdown immediate;

startup nomount;


Backup source database

rm /app/oradata/rman_backup_dir/*


rman target /



allocate channel d1 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d2 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d3 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d4 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d5 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

backup as backupset database plus archivelog;


This takes around 10 minutes. Make a note of the finish time (which is why the NLS_DATE_FORMAT variable was set)

Duplicate database

2 scripts have been created in /home/oracle on pebor02 which are pasted here


# Set the Oracle Environment


export ORACLE_HOME= /app/oracle/product/11.1.0/db_1/



export NLS_LANG=american





# Run the RMAN Command


nohup  $ORACLE_HOME/bin/rman target sys/password nocatalog auxiliary sys/password@TARGETDB_clone @rman_duplicate_from_backup.cmd log=/home/oracle/rman_duplicate_from_backup.log  &




set until scn 9660022443946;

allocate auxiliary channel d1 device type disk;

allocate auxiliary channel d2 device type disk;

allocate auxiliary channel d3 device type disk;

allocate auxiliary channel d4 device type disk;

allocate auxiliary channel d5 device type disk;

duplicate target database to TARGETDB;


Run the shell script  ./ then it will fail with an invalid SCN, however  the output will provide the latest SCN that can be recovered until. Edit the rman_duplicate_from_backup.cmd file and replace the SCN and rerun.

You can get the SCN from the timestamp by this way you are letting Oracle do the work for you..

Restart the shell script and monitor the log file specified in the shell script.

Tidy up

Stop the listener_clone and start a listener for the newly refreshed database.

Remove the files from /app/oradata/rman_backup_dir

Notify the Apps team that the database has been refreshed and the time that the backup finished.


6 Responses to “Cloning a database (ASM to ASM)”

  1. […] Cloning database ASM to ASM […]

  2. Hello.

    Thanks for the post. It’s quite simple, however I’m getting this error message.

    RMAN-05001: auxiliary filename +DATA/old_database/small_data01_01.dbf conflicts with a file used by the target database

    Any clue on how to fix this?

  3. Hey!

    I could fix it adding the following parameters to the init file.


    Thanks for the post, it worked perfect.

  4. John Hallas said

    Hi Alex,
    Are you calling the new database ‘old_database’ i.e. the same name as target because that is what it looks like
    1) Is the auxillary SID started up correctly (nomount with the new SID)
    2) Have you used db_file_name_convert or are you relying on ASM to do that (as I do in my example

    I am going with 1) as the problem.

    good luck and please feedback with any fix you find


  5. […] Cloning RAC ASM to non-RAC ASM […]

  6. manu said

    ORA-19505: failed to identify file “+INTEM_FR_01/intem_aemidbp/backupset/2011_08_24/nnndf0_intem-bkup-bcomp_0.1688.760046745”
    ORA-17503: ksfdopn:2 Failed to open file +INTEM_FR_01/intem_aemidbp/backupset/2011_08_24/nnndf0_intem-bkup-bcomp_0.1688.760046745
    ORA-15001: diskgroup “INTEM_FR_01” does not exist or is not mounted
    ORA-15001: diskgroup “INTEM_FR_01” does not exist or is not mounted

    I am getting above error while following steps mentioned above..
    seems rman is looking for the backupsets from source db ..while i have copied to NFS mount and given proper permissons for auxilary db to acesss those files.

Leave a Reply

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

You are commenting using your 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: