Restore n Recover Production DB to Point In Time on new machine

1.Install Oracle RDBMS Server Software on destination host.

2.Patch it to same release which is installed on source host.

3.Copy Datafile Backups , Archivelog Backups , Controlfile backup and spfile backup on to destination host ( test2 ) to same location as source host ( test1 )

4.Restore spfile from autobackup using following command.

$export ORACLE_SID=DEV
$export ORACLE_HOME=/apps1/oracle01/u11/app/oracle/product/10.2.0/db_1
$rman target / nocatalog
RMAN>set DBID=00000000 ( You can get DB ID for source db by querying v$database view )
RMAN>startup nomount;
RMAN>restore spfile from '/var/backups1/DEV/autobackup/2009_10_04/o1_mf_s_699336061_5dj3zg47_.bkp';

Spfile will be restored in $ORACLE_HOME/dbs folder.

RMAN>shutdown immediate;
$sqlplus / as sysdba
SQL>create pfile from spfile;

pfile will be restored in $ORACLE_HOME/dbs folder.

Open pfile ( initDEV.ora ) and modify adump,bdump,cdump,udump and db_recovery_file_dest parameter.Also modify control_files parameter according to new path.

$sqlplus / as sysdba
SQL>startup nomount pfile='$ORACLE_HOME/dbs/initDEV.ora'
SQL>create spfile from pfile;
SQL>startup nomount force;

5.Restore controlfile from autobackup using following command.

$rman target / nocatalog
RMAN>set DBID=00000000
RMAN>restore controlfile from '/var/backups1/DEV/autobackup/2009_10_04/o1_mf_s_699336061_5dj3zg47_.bkp';
RMAN>alter database mount;

6.Restore datafiles.
If the directory structure is different than you need to use "set newname" command to change file name location else its not required.

Create RMAN script for restoring DB.

RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/apps1/DEV/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/apps1/DEV/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/apps1/DEV/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/apps1/DEV/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/apps1/DEV/cs_dev_owner_01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/apps1/DEV/admin_01.dbf';
SET UNTIL TIMEA = "to_date('2019-11-03:41:54:00','YYYY-MM-DD:HH24:MI:SS')";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}
EXIT

7.Recover database to point in time.

$rman target / nocatalog
RMAN>recover database until time "to_date(''2019-11-03:41:54:00'','YYYY-MM-DD:HH24:MI:SS')";

8.Rename redo logfile name before opening DB with resetlogs option.

$sqlplus / as sysdba
SQL>alter database rename file '/apps1/u14/oradata/DEV/redo01a.log' to '/apps1/DEV/redo01a.log';

Do same for all the redo files.( run select member from v$logfile to get list of redo logs )

9.Open DB with resetlogs option.

$sqlplus / as sysdba
SQL>alter database open resetlogs;

10.Create TEMP tablespace.

$sqlplus / as sysdba
SQL>create temporary tablespace temp1 tempfile '/apps1/DEV/temp.dbf' size 10M;
SQL>alter database default temporary tablespace temp1;