Restore Back PRODUCTION TO DEV Instance.

Oracle backup restore production copy of backup files in PROD server to Development environment

Restore Prod backup database to different existing database instance system from PROD
Source: SERVERPROD Database: ORAPROD
Destination: SERVERDEV Database: ORADEV

Pre-Conditions:
File structure on both system should be same.
Backup of ORAPROD is already in tsm server.

For PROD Admin:
PROD Admin needs to reconfigure setting to point backup path of ORAPROD from serverdev, so that ORADEV can access the
backup of ORAPROD in the PROD server.

1. connect to serverprod and rman to find DBID

>rman target /

2. connect to serverdev and shut down oradev.

>rman target /
rman> shutdown immediate;

3. remove all the database datafiles(like undofiles,system files and datafiles)

$rm /mount1/ORADEV/* $rm /mount2/ORADEV/* ..

4. Start up instance with nomount and set dbid of ORAPROD.

rman> startup nomount; rman> set dbid XXXXXXXXXXXX;

5. Restore parameter file SPFILE to temporary location from PROD.

RMAN> run { allocate channel ch2 type ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/usr/tivoli/PROD/client/oracle/bin64/tdpo.opt)’; RESTORE SPFILE TO ‘/tmp/spfileTEMP.ora’ from autobackup;
}

6.Once server parameter file is restored, shutdown the instance and startup the instance with the newly restored server parameter file.

rman> shutdown immediate; rman> startup nomount;

7. Restore controlfile from PROD server.

rman> set dbid XXXXXXXXXXXXX; rman> run { allocate channel ch2 type ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/usr/tivoli/PROD/client/oracle/bin64/tdpo.opt)’; restore controlfile from autobackup; }

8. Change the database name in parameter file. RMAN> alter database mount; RMAN>sql ‘alter system set db_name=ORAPROD scope=spfile’;

9. Shutdown the server and change ORACLE_SID to ORAPROD.
rman> shutdown immediate; $export ORACLE_SID= ORAPROD

10. Change name spfileORADEV.ora to spfileORAPROD.ora in $ORACLE_HOME/dbs
$mv spfileORADEV.ora spfileORAPROD.ora

11. Start up instance with mount database and restore and recover database.
SQL> startup nomount; SQL> alter database mount; $rman target / rman>run { allocate channel ch2 type ‘SBT_TAPE’ PARMS ‘ENV=(TDPO_OPTFILE=/usr/tivoli/PROD/client/oracle/bin64/tdpo.opt)’; RESTORE database; RECOVER database;
}
12. Reset logs and shutdown
  sql> alter database open resetlogs; sql> shutdown immediate;

13. start database normal. Database ORAPROD will open normal in SERVERDEV.

SQL> startup

14. Change the database name from ORAPROD to ORADEV

sql> shutdown immediate sql> startup mount
$nid TARGET=SYS DBNAME=ORADEV
sql> alter system set db_name=ORADEV scope=spfile;
SQL>SHUTDOWN IMMEDIATE
$mv spfileORAPROD.ora spfileORADEV.ora
SQL>STARTUP MOUNT
SQL> alter database open resetlogs;

15. Database ORADEV started with ORAPROD data. Here comes ORADEV refresh completes..