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 archive-log mode and regular backup are being taken

(1) Loss of a single control file

Impact: Database becomes Unavailable

Solution:
Shutdown Instance ( abort )

Change Init.ora file to remove the lost controlfile or copy the existing controlfile to that location
startup the database

(2) Loss of all control files

Impact: Database becomes Unavailable

Solution:

a)Shutdown Instance (abort)
b) If the control-file backup is available ,then restore it from backup or if you have got the controlfile information in trace using alter database backup controlfile 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. Choose 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

Solution
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

Solution
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

Solution
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

Solution:

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

1) Shutdown abort
2) Remove the current datafiles
3) restore database
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

Solution:

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

Solution:

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

In-case whole tablespace gone
1) alter tablespace USERS offline
2) restore tablespace
3) Recover tablespace
4) alter tablespace USERS online

Please read below article for command on altering tablespace

What you need to know about create tablespace statement in Oracle

(9) Database logical error or corruption

Solution:

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

Solution:

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

Solution:

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 temp-files

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

Solution:

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

(14) Loss of table due to mistake

Solution:

1) Restore database to another server(auxiliary 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 reset-logs
4) Export the table from auxiliary instance and import back in Primary database
We can either use RMAN or manual method for these scenario

If we can flashback database enable in the database, we can use that also to recover the loss of the table.

You may want to read out below flashback database

Recover drop/truncate/delete table on primary using flashback on standby database
Flashback Database explained and limitation
Top 10 Useful Queries for Flashback Database
how to Flashback when we have dataguard