Clone refresh a database using RMAN from tape backup

Posted: August 26, 2014 in Backup and Recovery

Good morning, Oracle fans!

I need to give credit to the Oracle DBA’s at DaVita Inc for this process. Some of the authors/contributors of this adaptation of “duplicate database” include Robert Levi, Karen Laine, Charlie Calloway and Greg Owens but there may be others. The only change I have made is to substitute “set until scn” from the original “set until time”. Please be sure to credit the author/s if you use this in your environments. In the original posting of this article, I failed to give proper credit. I need to be better in this regard. I greatly cherished my experience with the data architects and DBAs at DaVita and I hope they will forgive this lack of forethought and proofreading.

Performing clone refreshes from production down to test/development environments should be a regular part of keeping your databases in sync with one another. It is also an excellent test to see if your backups can be used as a restore in the case of an emergency. Don’t take this as the authoritative, official way to refresh a database but I have used this successfully in the past.


Create two scripts:

  • vi cloneDBNAME.rcv
run {
set until time "to_date('2012-02-26 01:00:00', 'yyyy-mm-dd hh24:mi:ss')";
allocate auxiliary channel t1 type 'sbt_tape';
duplicate target database to DEVDBNAME;
run {
set until scn <scn#>;
allocate auxiliary channel t1 type 'sbt_tape';
duplicate target database to DEVDBNAME;

The timestamp on the set_until_time is very important. My experience has been to set this to the exact end time of the Database Backup. This can be found in your RMAN log or, if your business uses Commvault, in the Commvault job details. If you set this to a time before the backup is completed or too far after the completion, the RMAN script may go looking for a different backup set that may or may not be in your media library. Alternatively, you can utilize set until scn. This is less problematic but takes a little digging. It is displayed on the details pane in Commvault. Otherwise, you can query RMAN backups with the PREVIEW Utility.

  • vi cloneDBNAME.ksh
rman target sys\/syspwd@PRODDB catalog rman_user/rmanpwd@RMANDB auxilliary sys\/syspwd@DEVDB cmdfile /some/directory/cloneDBNAME.rcv msglog /some/directory/cloneDBNAME.rcv.log

Steps to Clone a database using the duplicate command.

  • Ensure the backup you want to restore with is held in the media library. Once a backup to tape completes, the backup administrator will typically store it somewhere offsite. You need it in the media library before you begin your clone refresh.
  • Check to see if anyone is logged in and tell the users that the database is coming down.
  • Verify oratab entry for destination/auxiliary database on destination machine
  • Record the archive log destination so that you can delete the old archive logs when you delete the datafiles, contolfiles, tempfiles and redo logs. In addition to, record the database log mode and Automatic archival values. Your cloned database may need to have these values reset after the restore.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /apps/oracle/product/9.2.0/dbs/arch
  • Get file names from destination database so that you can remove the old files: Don’t run this script until after you have preserved passwords, dblinks, and user privilieges!!
spool rmfiles.ksh
select 'rm '|| file_name from dba_data_files;
select 'rm '|| member from v$logfile;
select 'rm '|| file_name from dba_temp_files;
select 'rm '|| NAME from v$controlfile;
  •  Preserve all of the user passwords from the destination database before it’s removed.

For databases prior to 11g, use…

spool resetpw.sql
select 'alter user ' || username || ' identified by values ''' || password ||''';' from dba_users;

For 11g databases, use…

spool resetpw.sql
select 'alter user '||name||' identified by values '''||password||''';' from user$ where name in (select username from dba_users);
  • Preserve all of the dblinks from the destination database
spool newlinks.sql
select 'create ' || || ' DATABASE LINK "' || || '"',
' CONNECT TO "' || l.userid || '" IDENTIFIED BY VALUES ''' || l.passwordx || ''' USING ''' || || ''';'
from$ l, sys.user$ u
where l.owner# = u.user#
order by,;

Note: is included so the DBA knows who owns the dblink. It should be stripped out before running this script later unless it is a public dblink.

  • Preserve all of the System, Role and object privileges from the destination database.  – Optional
spool priv.sql
SELECT 'grant '|| PRIVILEGE ||' on '||OWNER||'.'||TABLE_NAME|| ' to ' || GRANTEE ||';' from dba_tab_privs
where owner!='SYS' and grantee !='SYS'
order by owner, grantee, privilege;
SELECT 'grant '|| PRIVILEGE ||' to ' || GRANTEE ||';' from dba_sys_privs;
SELECT 'grant '|| GRANTED_ROLE ||' to ' || GRANTEE ||';' from dba_role_privs;
spool off
  • shutdown destination database
sqlplus "/ as sysdba"
shutdown immediate
  •  Remove old database files from destination server using script created earlier
  • Remove the archive log files from the destination captured above.
  • Ensure that JOB_QUEUE_PROCESSES=0 in the init.ora of the clone environment
  • Verify that DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are correct in the init.ora file of the destination database. If not, insert the needed parameters and settings



    • Startup destination database in nomount mode:
sqlplus "/ as sysdba"
startup nomount
  •  Query/Prep source environment/database

1. Make sure the destination instance is in tnsnames on the server of the source database

view /etc/tnsnames.ora

 2. On target machine (DB to be cloned) set $ORACLE_SID to source SID.

3.  Run initial RMAN Command to connect to both the source catalog and the destination database. If this RMAN command fails everything is not setup right. Fill in appropriate source database, destination database and rman catalog.

rman target / catalog RMAN_CATALOG/rman@rmandb auxiliary sys/syspwd@DEV_DB
  •  Execute duplicate command

1. Find the best time to restore the database. Log into Backup Software and find the database backup that you want to restore.  To get the exact time to use in the restore,  look for the “Server End” time of the “Database Backup” entry.  By using this exact time you should be able to restore the database and use just the archive logs that were backed up with the database backup.  No additional tapes and/or logs should be needed.

/* then set the recovery time in the script and run it */
run {
set until time "to_date('Restore to: Year Month Time', 'yyyy mon dd hh24:mi:ss')";
allocate auxiliary channel t1 type 'sbt_tape' parms="ENV=(NB_ORA_CLIENT=Source Server)";
duplicate target database to Destination Database;
run {
set until time "to_date('2008 Jan 16 03:35:45', 'yyyy mon dd hh24:mi:ss')";
allocate auxiliary channel t1 type 'sbt_tape' parms="ENV=(NB_ORA_CLIENT=sea-ha0001)";
duplicate target database to DAFYQA;

 Note: If you created this script in the pre-steps above, all you will need to change is the set until time.  

Commands to run on destination database after refresh (database should already be open)

1. Update Global Name

Database altered.

 2. Apply the passwords you saved earlier.

 3. Make sure that the DBSNMP user’s account is unlocked and the password matches its previous name.

 4. Take database out of archive log mode (only needed if dest database is not backed up hot) Otherwise, leave it in archivelog mode.

shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
archive log list;

 5. Add temporary tablespace to new database (not needed for 10.2+ database clones)

alter tablespace TEMP add tempfile 'location of tempfile' size size of tempfile M;

 6. Check to make sure there aren’t any other temporary tablespaces and add tempfiles to them if there are.


Select tablespace_name from dba_tablespaces where CONTENTS = 'TEMPORARY';
example: alter tablespace TABLESPACE_NAME  add tempfile '/db15/oradata/DEVDB/tablespace_name_01.dbf' size 2048M;

 7. If the cloned database is being backed up then register it in the proper rman catalog

rman catalog RMAN_CATALOG_NAME /rman@rmancat_pwd target /
register database;

 8. Drop old database links:

select 'DROP PUBLIC DATABASE LINK ' || db_link || ';' from dba_db_links where owner = 'PUBLIC';

 9. Create new database links. Run the script created from the previous database incarnation.  If non PUBLIC database links exist, you will have to log in as link owner and create the link. Strip out the “” in the create script.


 10. Before executing this step, ensure you have dropped database links that came over from production and created the non-production links.

Set job_queue_processes=10 in the init.ora and then restart the database.

 11. Communicate with users that clone is complete.

 Good Luck!!



  1. […] If you are seriously considering this step, you are likely at your last resort. If you are not at your last resort, please review my previous three posts on restoring logs, datafiles and tablespaces. In nearly six years of working with Oracle Databases, I have yet to exercise this option live. I have restored maintenance using a clone from production. (See ) […]

  2. I have edited the original post to include proper credit to the authors of this process. Please see the first paragraph for details.

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