Pavan DBA's Blog

The DBA Knowledge Store

how to do complete recovery if controlfiles are lost

Posted by Pavan DBA on March 18, 2010

Lets see the steps to perform a complete recovery of database if we loose all the controlfiles

  1. Take the trace of controlfile using below command
    sql> alter database backup controlfile to trace;
    Note : The above command will work fortunately if you have database still up and running. If not, you need to have the latest controlfile trace. If not available and still you have all redolog and datafile information, then you can take trace of other database and modify the name, path and sizes of redolog files and datafiles
  2. From the controlfile trace, copy second CREATE CONTROLFILE command till characterset to another text file and save it with .sql extension (generally i will save it as create_control.sql)
  3. change RESETLOGS option to NORESETLOGS in that sql file.
  4. sql> shutdown immediate;
  5. sql> startup nomount;
  6. sql> @create_control.sql (your current directory should be the location of this file or you can give path also before file name)
    Note : This will create controlfile and will place the database in MOUNT state. If any errors, observed, we need to debug them.
  7. sql> alter database open;

Hope this helps you… 🙂

10 Responses to “how to do complete recovery if controlfiles are lost”

  1. Vamshi VA said

    thank you sir..

  2. chintan said

    its possible all controlfile missing and still Database open & runing as per my knowledge its not possible.
    plz if possible help me & overcome my confusion?

    • Pavan DBA said

      if u react immediately after controlfile loss, yes still ur db will be up and running. otherwise as i mentioned, u need to take trace from other db and modify it

  3. chintan said

    This is clear my concept(controlfile recovery)

  4. santhosh said

    Hi pavan,

    I am junior DBA working on oracle 10g. You have told the solution if database is running and is not shut. But, how to recover if we have shut down the database.

    • Hi, as mentioned in the post, if your database is not up and running, you need to use latest trace of the controlfile. If you don’t have latest trace and if you have all datafiles and redolog files (location and sizes) information, then you can take other database controlfile trace and do the changes accordingly.

      if you don’t have any of the options, then you cannot recover completely, only option is incomplete recovery

  5. jay said

    The only situation where you can open with noresetlogs is when there was a clean shutdown of database.
    But then when you do a clean shutdown. There may not be a situation to recreate a controlfile?..

    chEErs 🙂

    • sorry, but not true….you can still create controlfile even after clean shutdown. for example, controlfile recreation is one way to change the database name (apart from using dbnewid utility). in this case, we will do clean shutdown only….

      you can refer to my post on changing database name for steps….also you can try controlfile complete recovery steps toooo

  6. jay said

    You are doing a great job by sharing knowledge but, i completely disagree with above recovery process.

    You can never open a database with noresetlogs from a backup controlfile..
    If you don’t agree, let me know and i will explain. Or Contact Oracle support..


    • Hi Jay,

      Thanks for commenting.

      i think you got confused between complete and incomplete recovery. please see my steps again, you will understand what i am doing
      when you lost controlfile, you can go for either complete or incomplete recovery.
      In incomplete recovery, we will restore the file from backup so while recovery we use “using backup controlfile” clause.

      But in complete recovery, we are not restoring any file instead we are creating new one. so we can open the database with noresetlogs option in this case.

      atleast i had done this lot many times in my environments…..

      plz try these steps in your test environment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: