My Oracle Blog: Refresh Test Database From Prod backup

SCENARIO

We have a PROD DB 'PROD' and we have taken backup from tape and sent it over to the

TEST site where the TEST DB 'TEST' runs.

We want to replace the database currently running on TEST with the backup we have taken at PROD.

DUPLICATE is ruled out because we can't connect to both PROD and TEST and there is no Recovery Catalog.

Backup files have been transferred to : /home/oracle/bkps directory on TEST

STEPS

1. Drop the existing TEST database

SQL> shutdown immediate

SQL> startup mount exclusive restrict

SQL> drop database

This would drop all the datafiles, spfile, controlfiles, and probly archivelogs and backups too.

2. Restore SPFILE

RMAN> startup nomount

This will start the instance with a dummy SPFILE. THe isntance name would be as mentioned in the ORACLE_SID

RMAN> restore spfile from '/home/oracle/bkps/controlfileautobkp.bkp';

RMAN> shutdown immediate;

Create Pfile and edit it to change ORACLE_SID entries.

Also create directories or change parameter values to mirror existing directory structure.

RMAN> create PFILE from SPFILE;

Then recreate SPFILE

RMAN> create SPFILE from PFILE;

3. Restore CONTROLFILE

RMAN> startup nomount;

RMAN> restore controlfile from '/hom/oracle/controlfileautobkp.bkp';

RMAN> startup mount;

4. Catalog the backups

RMAN> CATALOG START WITH '/home/oracle/bkps';

5. Restore and recover DATABASE

RMAN> restore database;

RMAN> recover database;

In case the database throws any errors while recovery in terms of archivelogs missing,

try this

RMAN> recover database NOREDO;

6. Open database

Based on complete/incomplete recovery,

RMAN> alter database open;

RMAN> alter database open RESETLOGS;

My Oracle Blog