How to use RMAN to create a test database from a full recovery backup dataset

oracleoracle-12crman

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 …

Scenario:

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?
shutdown;
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?
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE; 
}


sqlplus sys/sys as sysdba

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

ALTER DATABASE OPEN;
ALTER TABLESPACE mytempspace // what's this line about?  I just want to take a clone of the DB that was backed up elsewhere
ADD TEMPFILE 'c:\oracle............dbf' SIZE 1G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Whining:

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.

Best Answer

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
exit


-- 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')
  LOGFILE
    '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.

Related Question