About Me

My Photo
Bangalore, India
I am an Oracle Certified Associate working in Wells Fargo as DBA Specialist. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com or +919560515533

Sunday, October 14, 2012

How to recreate a control file in Oracle Database

We should only recreate our control file when we are in certain circumstances :-

1. All copies of control files present in database are lost or corrupted.
2. We are restoring a backup in which control file is corrupted or missing.
3. We need to change a hard limit database parameter in the control file.
4. If we are moving our database to another server are files are present in different location.
5. Oracle customer support advices us to do so.

Recreating a control file of that database which is able to mount or open.

1. First we have to generate a ascii dump of the control file.

When database is mounted or open :-
SQL> alter database backup controlfile to trace;

          Database altered


Trace file will be generated in User_dump directory.
SQL> show parameter user_dump_dest

NAME                                  TYPE           VALUE
------------------------                  ---------          ----------------------------
user_dump_dest                  string            /u04/app/cognos/diag/rdbms/orc
                                                                 l/orcl/trace


Navigate to this directory and locate the latest trace file by using ls -ltr
[cognos@rac1 ~]$ cd /u04/app/cognos/diag/rdbms/orcl/orcl/trace/
[cognos@rac1 trace]$ ls -ltr


2. Create control file creation script from backup trace file :-

open the trace file named like _orc_1234.trc. It appears like an ordinary trace file but we are interested in the part having create control file script. Modify the trace file, delete every thing above the "CREATE CONTROLFILE" and after the "CHARACTER SET" option.

3. Shut down your database with immediate option.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


4. Startup the database in nomount mode.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
SQL>


5. Take the control file script and use it to create the control file of the database.
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+AMIT_R1/orcl/onlinelog/group_1.262.790789015',
    '+AMIT_R1/orcl/onlinelog/group_1.263.790789019'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+AMIT_R1/orcl/onlinelog/group_2.264.790789023',
    '+AMIT_R1/orcl/onlinelog/group_2.265.790789025'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '+AMIT_R1/orcl/onlinelog/group_3.266.790789027',
    '+AMIT_R1/orcl/onlinelog/group_3.267.790789029'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+AMIT_R1/orcl/datafile/system.256.790788811',
  '+AMIT_R1/orcl/datafile/sysaux.257.790788811',
  '+AMIT_R1/orcl/datafile/undotbs1.258.790788813',
  '+AMIT_R1/orcl/datafile/users.259.790788813',
  '+AMIT_R1/orcl/datafile/example.269.790789095',
  '+AMIT_R1/orcl/datafile/amit.271.795743985'
CHARACTER SET AL32UTF8
;
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29

Control file created
.

Once the control file successfully created database is automatically mounted.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED


6. Once the database is mounted open the database with reset logs option.

SQL> alter database open resetlogs;

Database altered.


We have started the database with resetlogs so it's important to take a backup immediately.

7. After the database is open add the existing temp file

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+AMIT_R1/orcl/tempfile/temp.268.790789087' size 1429M REUSE;

Tablespace altered.


NOTE :- If we are using this control file creation script for a new database then some change have to be made in control file creation script, instead of "CREATE CONTROLFILE REUSE" we have to use "CREATE CONTROLFILE SET" and instead of "NORESETLOGS" we have to use "RESETLOGS".

Recreating a control file of that database which is not able to mount.

In this scenario when we donot have a control file then :-

1. Restore control file from backup.
                         OR
    Create a script of control file from beginning

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG

  Follow the format listing :
  1. location of redo logs.
  2. Location of Datafiles.
  3. Specify the Characterset.

Once all things are listed correctly, use this to receate your control file.

SQL> startup nomount;

Create your control file from the script created earlier.

I hope this article helped you.

Regards,
Amit Rath

4 comments:

  1. Hi,
    I have lost my DB control file. And my DB is in nomount state.
    I am trying to run "SQL> alter database backup controlfile to trace;"
    out put is:
    "alter database backup controlfile to trace
    *
    ERROR at line 1:
    ORA-01507: database not mounted


    SQL> alter database backup controlfile to trace;
    alter database backup controlfile to trace
    *
    ERROR at line 1:
    ORA-01507: database not mounted
    "
    Ho do I proceed?

    ReplyDelete
    Replies
    1. Hi Swapnil,

      Thanx for writing in. PFB steps :-

      1. If you have backup available, try to restore your control file from backup and then start your database in mount mode. After that do recovery and open database with resetlogs option.

      2. If backup is not available, then manually create your controlfile as mentioned in the blog and then mount your database , after that open database with resetlogs option.

      Amit Rath

      Delete
  2. I tried the above in oracle 10g .Everything was fine until I run command ALTER DATABASE OPEN RESETLOGS where I got the error ORA-01139 RESET LOG OPTION ONLY VALID AFTER AN INCOMPLETE DATABASE RECOVERY.why I cant run ALTER DATABASE OPEN RESET LOGS successfully?

    ReplyDelete
    Replies
    1. Hi ,

      Kindly use RESETLOGS option in your create control file creation script. If you lost all your controlfiles then please look at below link for recovery procedures available
      http://amit7oracledba.blogspot.in/2012/10/ora-00205-error-in-identifying-control.html

      Thanks
      Amit Rath

      Delete