Database refresh using cold backup

Sometime, you will be asked to refresh an UAT database using an coldbackup of production one.  Here are the steps :

1.  Prepare controlfile creation script.  This can be done by

alter database backup controlfile to trace as '/export/home/oracle/ctlfile.sql';

2.  View the edit ctlfile.sql, so that it should contain the following

CREATE CONTROLFILE SET DATABASE "UATDB" RESETLOGS NOARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 1
  MAXLOGHISTORY 453
LOGFILE
  GROUP 1 '/u01/oradata/UATDB/redo01.log' SIZE 200M,
  GROUP 2 '/u02/oradata/UATDB/redo02.log' SIZE 200M,
  GROUP 3 '/u03/oradata/UATDB/redo03.log' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/UATDB/system01.dbf',
  '/u01/oradata/UATDB/undotbs01.dbf',
  '/u01/oradata/UATDB/users01.dbf',
  '/u01/oradata/UATDB/data01.dbf',
  '/u01/oradata/UATDB/tools01.dbf',
  '/u01/oradata/UATDB/perfstat01.dbf',
  '/u01/oradata/UATDB/sysaux01.dbf'
CHARACTER SET WE8ISO8859P1
;

3. Gernerate script to create TEMP tablespace

set long 2000
select DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') from dual;

4. Shutdown UATDB cleanly

shutdown immediate

5. Copy the cold backup files from production to UAT box.

6. Start the database in nomount state
 
startup nomount

7. Run the create control file script

@/export/home/oracle/ctlfile.sql

8. Open the database with resetlogs

alter database open resetlogs;

9. Create temp tablespace from the script generated from step 3:

CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE
'/u01/oradata/UATDB/TEMP01.dbf' SIZE 5242880000
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576