How To Create New Control File

As you know control file records the information about the physical structure of the database.It is very crucial file it is veru small binary file, So thats why we create multiplexing of control file and we back up control file time to time.But sometime when all control file have been damaged then we need to create new control file as follow:

CREATE CONTROLFILE
   SET DATABASE luck
   LOGFILE GROUP 1 ('/opt/luck/redo01.log',
                  '/opt/luck/redo02.log'),
           GROUP 2 ('/opt/luck/gro/redo01.log',
                     '/u01/luck/gro/redo02.log'),
   NORESETLOGS
   DATAFILE '/opt/luck/system.dbf' SIZE 3M,
             '/opt/luck/sysaux.dbs' SIZE 5M,
                   '/opt/luck/user.dbs' SIZE 5M,
                   '/opt/luck/temp.dbs' SIZE 5M

   MAXLOGFILES 30
   MAXLOGMEMBERS 2
   MAXLOGHISTORY 200
   MAXDATAFILES 150
   MAXINSTANCES 6
   ARCHIVELOG;

Here are some steps, how to create new control file or open your database when your all control files have been damaged :

1) Make a list of all datafiles and online redo log files of the database. Execute the following statements which will provide you information about your datafiles and online redo log files:
Select member from v$logfile;
select name from v$datafile;
Selecr value from v$parameter where name='CONTROL_FILES';

2) Shutdown the database.

3) Back up all datafiles and online redo log files of the databsae.

4) Start up a new instance at nomount stage

5) Create a new control file as we done above in this post.
In above as you know we used NORESETLOGS in control file creation. When creating a new control file select RESETLOGS option if you lost any online redo log group and with this option you will need to recover of the redo logs. Otherwise, select the NORESETLOGS option.

6) Store a backup of the new control file.

7) Edit the control_files intialization parameter for the database to indicate control file now part of your database as created in step5

8) Recover the database if you are using RESETLOGS option.

9) Open the database
Alter database open;

Posted in: Control file