How to recreate a control file in Oracle Database

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/orcl/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> shutdown 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


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;


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.

Note: 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 don't 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.