Download PDF with Complete Steps
New Database Instance : test2
Backup Path : C:\backups_new
New Database Instance Path : C:\Database\test2
Recovery Destination Path : C:\Database\flashrecovery\test2
Oracle_sid : test2
Open Command Prompt
set oracle_sid=test2
oradim -NEW -SID test2 -STARTMODE auto -PFILE C:\app\Administrator\product\11.2.0\dbhome_1\database\inittest2.ora
Verify Backup Location
Restore SPFile
rman target /
startup nomount;
Restore spfile from ‘C:\backups_new\LEVEL0_SPFILE_0D364AFC_1_1_28_09_2024.BKP’;
exit;
Sqlplus / as sysdba
Create pfile from spfile;
Change Parameters in Pfiles
Default Path of Parameter File : C:\app\Administrator\product\11.2.0\dbhome_1\database
Normally need to change below Parameters
*.audit_file_dest='C:\app\Administrator\admin\test2\adump'
*.control_files='C:\Database\test2\control01.ctl','c:\Database\flashrecovery\test2\control02.ctl'
*.db_unique_name=test2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test2XDB)'
*.db_recovery_file_dest='\Database\flashrecovery'
Shutdown dummy instance
Sqlplus / as sysdba
Shutdown immediate;
Create Server Parameter File
sqlplus / as sysdba
Create spfile from pfile;
Startup Instance with New Server Parameter File
sqlplus / as sysdba
Startup nomount;
Create Directories
mkdir c:\database\test2
mkdir C:\Database\flashrecovery\test2
Restore Control file
Restore controlfile from ‘C:\backups_new\LEVEL0_DBCTL_0A364AF6_1_1_28_09_2024.BKP’;
Alter database mount;
Crosscheck backup;
Delete expired backup;
catalog start with ‘C:\backups_new’;
Restore Database
sqlplus / as sysdba
select 'SET NEWNAME FOR DATAFILE ' || file# || ' TO ' || '''' || name || '''' || ';' from v$datafile;
Rman target /
run{
SET NEWNAME FOR DATAFILE 1 TO 'C:\DATABASE\test2\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'C:\DATABASE\test2\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'C:\DATABASE\test2\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'C:\DATABASE\test2\USERS01.DBF';
restore database;
SWITCH DATAFILE ALL;
Recover database;
}
Change Path of Redo Log File
Note: be careful copy paste in windows and remove spaces
Sqlplus / as sysdba
select 'Alter database rename file ' || '''' || member || '''' || ' to ' || '''' || member || '''' || ';' from v$logfile;
Alter database rename file 'C:\DATABASE\CONVD\REDO03.LOG' to 'C:\DATABASE\TEST2\REDO03.LOG';
Alter database rename file 'C:\DATABASE\CONVD\REDO02.LOG' to 'C:\DATABASE\TEST2\REDO02.LOG';
Alter database rename file 'C:\DATABASE\CONVD\REDO01.LOG' to 'C:\DATABASE\TEST2\REDO01.LOG';
Select member from v$logfile;
Change Path of Temp Files
Note: be careful copy paste in windows and remove spaces
select 'Alter database rename file ' || '''' || name || '''' || ' to ' || '''' || name || '''' || ';' from v$tempfile;
Alter database rename file 'C:\DATABASE\CONVD\TEMP01.DBF' to ‘C:\DATABASE\TEST2\TEMP01.DBF';
Select name from v$tempfile;
Open Database
sqlplus / as sysdba
recover database using backup controlfile until cancel;
Note: Enter "Cancel" in above input and "Media Recovery Complete" should show for Complete Recovery else need to provide more archive files for recovery.
Alter database open resetlogs;
Select open_mode from v$database;
Select instance_name from v$instance;
Full Script
open command prompt
set oracle_sid=test2
oradim -NEW -SID test2 -STARTMODE auto -PFILE C:\app\Administrator\product\11.2.0\dbhome_1\database\inittest2.ora
rman target /
Restore spfile from ‘C:\backups_new\LEVEL0_SPFILE_0D364AFC_1_1_28_09_2024.BKP’;
exit
sqlplus / as sysdba
Create pfile from spfile;
exit;
Change Pfile Accordingly
sqlplus / as sysdba
shutdown immediate
exit
sqlplus / as sysdba
Create spfile from pfile;
exit
mkdir c:\database\test2
mkdir C:\Database\flashrecovery\test2
rman target /
Restore controlfile from 'C:\backups_new\LEVEL0_DBCTL_0A364AF6_1_1_28_09_2024.BKP’;
Crosscheck backup;
delete expired backup;
catalog start with 'C:\backups_new’;
exit;
sqlplus / as sysdba
select 'SET NEWNAME FOR DATAFILE ' || file# || ' TO ' || '''' || name || '''' || ';' from v$datafile;
exit
Make script of Restore and Change Like below
rman target /
run{
SET NEWNAME FOR DATAFILE 1 TO 'C:\DATABASE\TEST2\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'C:\DATABASE\TEST2\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'C:\DATABASE\TEST2\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'C:\DATABASE\TEST2\USERS01.DBF';
restore database;
SWITCH DATAFILE ALL;
Recover database;
}
exit
sqlplus / as sysdba
select 'Alter database rename file ' || '''' || member || '''' || ' to ' || '''' || member || '''' || ';' from v$logfile;
Alter database rename file 'C:\DATABASE\CONVD\REDO03.LOG' to 'C:\DATABASE\TEST2\REDO03.LOG';
Alter database rename file 'C:\DATABASE\CONVD\REDO02.LOG' to 'C:\DATABASE\TEST2\REDO02.LOG';
Alter database rename file 'C:\DATABASE\CONVD\REDO01.LOG' to 'C:\DATABASE\TEST2\REDO01.LOG';
Select member from v$logfile;
select 'Alter database rename file ' || '''' || name || '''' || ' to ' || '''' || name || '''' || ';' from v$tempfile;
Alter database rename file 'C:\DATABASE\CONVD\TEMP01.DBF' to 'C:\DATABASE\TEST2\TEMP01.DBF';
Select name from v$tempfile;
recover database using backup controlfile until cancel;
Enter "Cancel" " Media Recovery Cancel" should show.
Alter database open resetlogs;
Select open_mode from v$database;
Select instance_name from v$instance;