Duplicate Database from Active Database — Just Works!

Posted in: Technical Track

My friend Øyvind Isene suggested that I store a DUPLICATE RMAN script that works in a safe place in this tweet.

I couldn’t find a safer place for the script than the Pythian blog :). Here goes the DUPLICATE DATABASE … from an ACTIVE DATABASE script that works for me beautifully.

I like the fact that we don’t need to worry about any time-consuming tasks anymore. For example, you don’t need the init.ora parameter on the destination side. Oracle creates it all for us. If you want to change any parameters (e.g. reduce memory footprint), you just specify it within the DUPLICATE command (e.g. set sga_target=4G).


Version = on both sides
Source db = prod
Destination db = test

On the source (prod):
— tns aliases to be created to point to prod and test databases
On the destination (test):
— the same version of Oracle SW installed
— directory structure created
— copy $ORACLE_HOME/dbs/orapwprod (from prod) to $ORACLE_HOME/dbs/orapwtest
— configure static listener configuration (allow you to connect as sysdba from prod)
— start an empty test instance “export ORACLE_HOME=….; export ORACLE_SID=….; sqlplus => statup nomount;”

– No SPFILE is needed. It will be taken case of while running DUPLICATE.
– Prod db files are located under /u01/oradata/prod.
– Test db files to be located under /u02/oradata/test.
– It doesn’t matter from where you execute the command (prod or test).


cat run_active_duplicate_prod_test_01.sh
. prod.env
rman TARGET sys/[email protected] AUXILIARY sys/[email protected]
SPFILE parameter_value_convert 'prod','test'
set db_file_name_convert='u01/oradata/prod','u02/oradata/test'
set log_file_name_convert='u01/oradata/prod','u02/oradata/test'
set control_files='/u02/oradata/test/cntrl01.dbf';

Kick off

nohup ./run_active_duplicate_prod_test_01.sh 2>&1 \
1>./run_active_duplicate_prod_test_01.`date +%Y%m%d_%H%M%S`.log &
ls -lptr ./run_active_duplicate_prod_test_01.`date +%Y%m%d_%H%M%S`.log


tail -f ./run_active_duplicate_prod_test_01.*.log
contents of Memory Script:
Alter clone database open resetlogs;
executing Memory Script

database opened
Finished Duplicate Db at 2013/07/01 05:42:30


Recovery Manager complete.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Yury is a nice person who enjoys meeting and working with people on challenging projects in the Oracle space. He started to work as an Oracle DBA 14 years ago (1997). For the past 10 years (since 2001) his main area of expertise is Oracle e-Business Suite. Yury is an OCP 7,8,9,10g and OCM 9i,10g. He is a frequent presenter at Oracle related conferences such as Hotsos, UKOUG and AUOUG. Yury is a socially active person. Apart from Social Media (Twitter, Bloging, Facebook) he is the primary organizer of Sydney Oracle Meetup group (250 people). So if you happen to be in Sydney (Australia) drop Yury a message and stop by at one of his Meetups.

6 Comments. Leave new

Rene Antunez
July 2, 2013 9:20 pm

Great post Yury, the only thing that I would make an emphasis on whoever uses this method and is in a RAC environment, make sure to shutdown the static listener after and just use this for the DUPLICATE. I’ve seen in the past conflicts with this, as people leave the static listener and share the tns entry of the static listener, making connections to this , instead of the scan listener.

Yury Velikanov
July 3, 2013 1:07 am

Agree. RAC is slightly more complex case and need to bit more care.


Cool stuff Yury! Do we need leading ‘/’ here u01/oradata/prod ?

Yury Velikanov
July 3, 2013 1:07 am

Hi Greg,

Nope. There is no mistake. As both prod and test have leading “/” in the path we don’t need to specify it in convert parameters. it is enough to specify the patterns to be replaced only (different parts of the string).



Thank you Yury, i got enlightenment by read your post.


Oh.. another thing is when you get RMAN-05001 don’t panic. its not error, oracle RMAN just want to show you that your path for convert is wrong path for the source. follow path on message cause its the true path.

Thank you Yury, it does spent me a day for process duplication.


Leave a Reply

Your email address will not be published. Required fields are marked *