Oracle complete recovery using backup control file

September 29, 2009 by 2 Comments 

I was just called into an Oracle database recovery issue. The situation was trivial for an Oracle DBA – a media failure damaged just one DB file on the Oracle database operating in ARCHIVELOG mode.

…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.

Enjoyed this article? Please share it with others using the social site of your choice:


2 Responses to “Oracle complete recovery using backup control file”
  1. Denis says:

    If I lost all controlfiles and some (or all) of my data files, but online redo logs are intact. I have backup of control files and datafiles, can I do a complete recovery? Oracle manual indicate swe can, but I don’t know how?

    Your post title indicate you’ve done a compete recovery after lost all contorl files, but I failed to understand how. Can you elaborate?



  2. admin says:

    The bottom line of that post was: It’s impossible doing complete recovery once you tried a clause …using backup control file.
    In your case I would try to recreate your control files synchronizing them with your current online redo logs.
    Then restore missing database files and proceed with complete recovery.
    If you need a more detail description and support please request a quote via Contact form.

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email