Restore Oracle hot backup

Consider any worst database scenario of your life, let say we got HDD corruption and some online datafile got corrupted and not accessible due to any reason. Now we have to restore database from hot backup, as follows:

Step 1>>

Copy back all the online datafile from backup location to the actual datafile location:

cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/users01.dbf
cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/undotbs01.dbf
cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/sysaux01.dbf
cp /u01/bkup/manual_online_hot/users01.dbf /u01/app/oracle/oradata/RTS/system01.dbf

Step 2>>

Copy back control file to all the controlfile locations, refer controlfile locations from parameter file, i.e. initRTS.ora file.

cp /u01/bkup/manual_online_hot/hot_bkup_control.ctl /u01/app/oracle/oradata/RTS/control01.ctl
cp /u01/bkup/manual_online_hot/hot_bkup_control.ctl /u01/app/oracle/oradata/RTS/control02.ctl

Step 3>>

Copy back pfile or spfile file to its location:

cp /u01/bkup/manual_online_hot/initRTS.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
cp /u01/bkup/manual_online_hot/spfileRTS.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step 4>>

Startup database in mount mode:

SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.

Step 5>>

Now, recover database with the help of following command and specify log: AUTO

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1117103 generated at 07/12/2017 04:39:47 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_90_910140016.dbf
ORA-00280: change 1117103 for thread 1 is in sequence #90
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Step 6>>

Open database with resetlogs:

SQL> alter database open resetlogs;

Cheers!! Hot backup restored and recovered successfully.

If you are facing following error then kindly visit my blog for the solution: ORA-01194: file 1 needs more recovery to be consistent

SQL> alter database open resetlogs;
alter database open resetlogs
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'

Thanks, Stay Tune. 🙂

Leave a Reply

%d bloggers like this: