Oracle Database Recovery various cases and solution

Oracle Database administrator has to prepare for the possibility of hardware, software, network, process, or system failure. If such a failure affects the operation of a database system, he must usually recover the database and return to normal operation as quickly as possible.So database recovery is very important for the organization

Oracle Database Recovery should protect the database and associated users from unnecessary problems and avoid or reduce the possibility of having to duplicate work manually.

Database Recovery processes vary depending on the type of failure that occurred, the structures affected, and the type of recovery that you perform.Here are few important cases of database recovery  and there solutions. We are assuming here the database is in archivelog mode and regular backup are being taken

1) Loss of a single control file

Impact: Database becomes Unavailable

Shutdown Instance ( abort )
Change Init.ora file to remove the lost controlfile or copy the existing controfile to that location
startup the database

2) Loss of all control files

Impact: Database becomes Unavailable


a)Shutdown Instance (abort)
b) If the controfile backup is available ,then restore it from backup or if you have got the controlfile information in trace using alter database backupcontrolfile to trace
c) Once the controlfile is created, recover database using backup controlfile. You will need to apply the redo logs to complete the recovery
d) alter database open resetlogs;

Related: How to recover database using RMAN
We can avoid the resetlogs by using the steps below
1) After the database is mounted with restore of controlfile from backup,create a trace of the controlfile using the command below
alter database backup controlfile to trace;
2) Now take out the create controlfile statement from the trace. Chosse the NORESETLOG portion
3) Recreate the controlfile using the above portion.
4) Do recover database
5) alter database open

3) Loss of one and not all of the online redo log members of a particular group.

Impact: Database remains available as LGWR can still write to one available member we will start seeing the errors in the alert log

1)Drop log file member
2)Add log file member
3) Check the error again in alert log

4) Loss of inactive archived redo log group

Impact: Database remains available

1)Drop the problem redo log
2)Add new log file group.

Related: How to recreate redo logfiles
5) Loss of an inactive redo log group that has not been archived

Impact: Database will need to be bring down to resolve the issue

1)Shutdown abort
2)Startup mount
3) Put the database in noarchive logmode
Alter database noarchivelog
4) Drop the problem redo log
5) Alter database open
6)Shutdown normal
7) Startup mount
8) Again enable the archive log mode
Alter database archivelog
9) Alter database open
6) Loss of current log or Primary Online Redo Log Corruption

Impact: Database will remain down


We will have to restore the database and perform incomplete recovery till the last log avaible

1) Shutdown abort
2) Remove the current datafiles
3) restore databse
4) recover database until sequence <>
5) Alter database open resetlogs
7) Loss or Corruption of system tablespace data file/Loss or corruption of rollback segment data file

Impact : Database will remain down


We have restore the lost datafile and perform complete recovery

1) Shutdown abort
2) startup mount
3) Restore the system or undo datafile
4) recover the datafile
5) alter database open

8) Loss of datafile or tablespace other than system or undo

Impact: Database will remain functional,only the data in the lost datafile or tablespace will remain unavailable


The lost datafile will be offline .If it is not ,make it offline

1) Restore the datafile
2) Recover the datafile
3) alter database datafile <> online

Incase whole tabespace gone
1) alter tablesspace USERS offline
2) restore tablespace
3) Recover tablespace
4) alter tablespace USERS online
9) Database logical error or corruption


Restore and recover the database to the point in time before logical error or corruption happened

1) shutdown abort
2) startup mount
3) restore database
4) recover databas until time <>
5) alter database open resetlogs

Related : How to recover corrupt blocks using RMAN
10). Loss Of Index datafile with no backup available


We need to find out all the indexes which belongs to the datafile. Drop them and then recreate them
11) Loss Of data datafile with no backup available


We need to find out all the tables which belongs to the datafile. Drop them and then recreate them from other instance

12) Loss of archived log files

Impact: There is no impact to Primary. But standby needs to be checked

Solution: Take full backup of database after the archive loss ocurred

Related: Recovering a standby database from a missing archivelog

13) Loss of Temporary tablespace or tempfiles

Impact: Database will remain up only the sorting or operation which uses temporary tablespace will fail


1. Drop current temporary tablespace.
2. Create another temporary tablespace preferably with the same name
If another temporary tablespace is present,
1. Run alter user scripts to switch to new temporary tablespace.

14) Loss of table due to mistake


1) Restore database to another server(auxilary instance) . We can do the partial restore of only those user datafile where data is required
2) Recover to the point before the drop of table
3)alter database open resetlogs
4) Export the table from auxilary instance and import back in Primary database
We can either use RMAN or manual method for these scenerio