Oracle complete recovery using backup control file
…but the guys have already issued the following recovery statement by mistake:
sql> recover database using backup control file;
When media failure occurs in a database operating in ARCHIVELOG mode, an Oracle DBA needs the following to recover completely up to the time of failure:
- A valid backup containing the lost or damaged datafiles taken after the database was set in ARCHIVELOG mode
- All archived logs from the time of the backup that you are using through the present time
- The redo log files that contain transactions not yet archived
An Oracle DBA can encounter the following error trying to open a DB:
ERROR at line 1:
ORA-01113: file 35 needs media recovery
ORA-01110: data file 35: ‘/oradb/oradata1//TEST.dbf’
Here is a quick summary of what I would to do to fix the Oracle database recovery problem in normal circumstances:
1. Identify datafiles needing recovery, and from where recovery needs to start following:
sql> select * from v$recover_file;
2. Locate archived log files, view V$ARCHIVED_LOG for all archived log files or V$RECOVERY_LOG for archived log files needed during recovery:
sql> select * from v$recovery_log;
3. Make sure that datafiles for restore are offline.
4. Restore only lost or damaged datafiles (do not restore the control files, redo log files, password files, or parameter files).
5. Recover the datafiles using one of the following:
sql> recover datafile <filenumber>;
sql> recover tablespace <ts name>;
sql> recover database;
6. Done: alter tablespace test online;
But, none of this worked! Why? Due to the wrong usage of the ‘using backup control file’ SQL clause for that recovery scenario.
That SQL clause tells Oracle DB «My control files are old, they are from backup => thus, they are not synchronized with my oneline redologs => thus, complete recovery is not possible.» As a result Oracle flags your current control files as backup ones making impossible complete DB recovery. So in that case you would NOT be able to finish your recovery process as you can not open the database. RESETLOGS option does not work complaining that SYSTEM tablespace file is not old enough. You are stuck!
So, that is the story. Below is a solution.
The only chance here to recover that Oracle DB was to do an incomplete recovery with, unfortunately, data lost of current online redolog file. So, here we go:
1. Shutdown the DB
2. Restore ALL datafiles (do not restore the control files, redo log files, password files, or parameter files).
3. Recover the database
sql> recover database;
4. Make sure all archive logs are applied during the recover.
5. Startup the DB with RESETLOGS option:
sql> alter database open resetlogs;
Lesson learned on this – as I always suggest Oracle DBAs, do an offline DB backup before any recovery activities. That will take time but you are safe having a chance of backing out in case something goes wrong.