Skip to content

Oracle cold backup database refresh

October 11, 2010
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
Advertisements

From → Oracle DBA

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: