In plain english for a someone whose DBA role is a third or seventh hat, and rarely has to touch Oracle, how do I use RMAN to create a test database from a full recovery backup dataset?

I've kept my whining at the end of the question ...


I have a complete RMAN backup set of a production database, taken as a copy of the flash recovery directory, from which I want to build a test database.

I have started to glean an understanding of what I need to do from this post: Oracle 11 rman db restore file corruption.

What I'm not getting is where some of the "alphabet soup" in his example script comes from.

Example script (with annotations):

rman target /
set DBID ...    // Where does this come from?  The existind DB I plan to overlay, or some magic spot in the RMAN backup set?
startup nomount
RESTORE CONTROLFILE FROM 'file destination' // is this my flash recovery directory?
startup mount;

// Is it right to assume that this is the directory containing the backup?
// Or should I list every single backup file from the flash recovery area (ugh!)?
catalog backuppiece 'C:/BKUP/AL_2851492942_20151016_893271615_PGQJSFHV_1_1';

rman:crosscheck backup; Run{
SET UNTIL SEQUENCE 50511;    // Where did this number come from?  Is it important?  Can I ignore this line since I have a complete recovery set?

sqlplus sys/sys as sysdba

shutdown immediate; startup mount;
alter database noarchivelog; // I see this is considered a bad practice elsewhere??

ALTER TABLESPACE mytempspace // what's this line about?  I just want to take a clone of the DB that was backed up elsewhere


I know that this is yet another noob question, and some of you experienced oracle DBA's will be rolling your eyes and thinking "RTFM". Unfortunately, the ease of management of SQL server is changing industry expectations, and anyone who can use a mouse is expected to be an instant DBA. That works with SQLServer, but not with Oracle.

In SQL server this is a simple task that comes down to pick your source backupset, pick your target database, click on the "replace database from backup", click "go", and then go for coffee.

In Oracle with rman, it looks like it should also be a simple task, but all the documents I have found appear to assume that the oracle DBA role is filled by a full time specialist in oracle who just needs a nudge in the right direction. While Tom Kite is really good, his page on this exact problem spends a lot of time babbling about transportable spaces and such, and quickly becomes indecipherable because of the assumptions of the depth of knowledge of the reader.

  • 3
    In SQL server this is a simple task that comes down to pick your source backupset, pick your target database, click on the "replace database from backup", click "go", and then go for coffee. I like your DBA job description! Any roles available in your company? :-) – Vérace Jul 29 '16 at 2:30
  • I hope I didn't offend you, this whine was only a description of the relative complexity of this particular common task (creating a test database from a backup of production), and how MS-SQL has changed industry expectations. If I thought that was all a DBA's role entailed, I wouldn't need to ask the question. I only included the whine section because I have seen others in this forum make the counter-whine "why are people who don't know the product trying to be DBA's" – pojo-guy Jul 29 '16 at 12:06
  • 4
    Not offended at all (you did notice the smiley?). I thought what you wrote was hilarious. I have come across self-important DBAs who tried to make one think that only their superb intellect and constant battling against man and machine was what was holding the company together! It's a case of Occam's razor vs. Einstein's maxim on simplicity, but you are correct, that there is noreason why a "point and click" approach is bad. 2 points. DBA should know what's behind the scenes. Virtually everything in Oracle can be scripted and is coffee compatible. – Vérace Jul 29 '16 at 19:46
  • So you have mean back of 11g database and want to create new 12c database using that backup right? Which architecture would you prefer CDB or non-CDB? – JSapkota Jul 30 '16 at 1:01
  • 2
    @pojo-guy: It may be possible to restore the old versions rman backup to new version but we need to upgrade the restored database before opening it. Here is a MOS Doc related to this issue-[Doc ID 369644.1]. It would be better to use Oracle Datapump to export the database and restore it in to 12c as you have said you are new to Oracle in some extent. – JSapkota Aug 1 '16 at 3:56

First place to check is Oracle official documentation. For example http://docs.oracle.com/cd/E36909_01/backup.1111/e10642/rcmdupdb.htm#autoId0 Depending of your database version(10g, 11g, 12c...). Just search documentation for duplicate database. I am not going to explain all questions from your script because it could only confuse you a little more.

Depending of prod db size you could use datapump export and import for example, or transportable tablespaces or like you tried RMAN. Your rman commands should be something like

-- scripts for restore using RMAN, 
-- This script is usable when the oracle versions are the same, 
-- or a direct upgrade is supported
-- prep database for rman import
rman target /
shutdown immediate
startup nomount

-- duplicate the database
rman auxiliary /
duplicate database to newdb
  backup location 'E:\Backup\oracle\flash_recovery_area\olddb\BACKUPSET\2016_07_29' nofilenamecheck 
  DB_FILE_NAME_CONVERT ('E:\app\oracle\oradata\olddb','E:\Oracle\oradata\newdb')
    'E:\Oracle\oradata\newdb\redo01.log' SIZE 2G,
    'E:\Oracle\oradata\newdb\redo02.log' SIZE 2G,
    'E:\Oracle\oradata\newdb\redo03.log' SIZE 2G;

duplicate database to testdb spfile backup location 'backup_path' nofilenamecheck;

When the databases are of different versions, use expdp/impdp:

-- Alternate procedure using using expdp / impdp
-- Export on source database
sqlplus: create or replace directory data_pump_directory as 'f:\dp';

cmd: expdp user/password@olddb schemas=SchemaToMigrate directory=data_pump_directory dumpfile=dbname.dmp logfile=expdp_dbname.log

-- generate script for generating user from old database
-- see http://www.oracle-scripts.net/generate-user-ddl/
-- copy output files from source system to destination system
-- Prep destination database for the import
-- use script generated above to create user/schema on destination database
sqlplus: create or replace directory data_pump_directory as 'f:\dp';

-- From bitter experience, this will not necessarily work with the user/password@newdb 
-- on the command line.  
-- If impdp chokes with ORA-39087:
-- 1. Confirm database is open (alter databaase open)
-- 2. Confirm the directory reference is created in the database
-- 3. Confirm the system and schema users are granted read and write on the directory reference
-- 4. Confirm the oracle service (OS) user has authority on the OS directory
-- 5. then try not supplying credentials until impdp asks for them (yeah,  a possible bug somewhere)
cmd: impdp user/password@newdb schemas=SchemaToMigrate directory=data_pump_directory dumpfile=dbname.dmp logfile=impdp_dbname.log table_exists_action=replace  remap_tablespace=oldts1:newts1,oldts2:newts2

This script can be written in many ways, depending if you want to recover to specific date and time, specific SCN, is test db the same structure(the same path for data and other files) or not, the same operating system or not, do you need to change some initialization parameters or not etc.

  • I'm not sure I could be confused much more (lol). I've actually figured out the answers to about half of my questions. Duplicate database would be great, except that the two databases are on different clouds with restricted communications between them. Test database should be the simplest setup possible since it's only going to be in use for about a month for evaluation purposes. It doesn't need to be as robust as the production database. – pojo-guy Jul 30 '16 at 15:01
  • As listed, I get this: RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is start ed with spfile cannot use SPFILE clause – pojo-guy Jul 30 '16 at 20:06
  • Removing the SPFILE from the definition, I get this: RMAN-05579: CONTROLFILE backup not found in C:\Backups\oracle\flash_recovery_area\MAX75P\BACKUPSET\2016_07_25\O1_MF_NNNDF_TAG20160725T231506_CSFOFX07_.BKP – pojo-guy Jul 30 '16 at 20:07
  • 1
    Duplicate with database version change isn't simple task. I saw just now that you want to restore data from 11g to 12c. How big is production database, maybe datapump export/import can do the job? – mutap Jul 31 '16 at 2:28
  • 1
    For that db size i would choose datapump export/import because it is easier then rman duplicate on different version ( there is document on MOS with complete procedure for rman 11g duplicate on 12c ). – mutap Jul 31 '16 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.