When to Create Controlfile
No one should not create control file until he is not suppose to do it. You should only need to recreate your control file under very special circumstances:
1)All current copies of the control file have been lost or are corrupted.
2)You need to change a “hard” database parameter such as MAXDATAFILES, MAXLOGFILES, MAXLOGHISTORY, etc. Though after 10.2g it is handled by database.
3)You are restoring a backup in which the control file is corrupted or missing.
4)If you are moving your database to another machine which is running the same operating system but the location of the datafiles, logfiles is not the same.
How I will create a new control file:
In this case I may have to face two scenarios.
Scenario 1:CREATING A NEW CONTROL FILE FROM THE EXISTING CONTROL FILE:
1.Create a control file trace
SQL> conn / as sysdba
SQL>alter database backup controlfile to trace as ‘file.txt’;
2.Modify the trace file ‘file.txt’ and change the required parameter in it.
3.Shutdown the database. shutdown immediate;
4.Take a full database backup.
5.Rename/move the existing database controlfiles to a backup.
6.Create the new controlfile.
7.Take backup of full database.
CREATING A NEW CONTROL FILE WITHOUT AN EXISTING CONTROL FILE:
1. Take a full backup of the database, including all datafiles and redo log files.
2.Do a startup nomount.
3.Issue the create controlfile statement.
4. Perform media recovery on the database. recover database.
5.Open the database. Alter database open.
6. At the first opportunity, shut the database down and take a full cold backup.
Here is one script of how you can create a new controlfile.
CREATE CONTROLFILE REUSE DATABASE “database_name_here” NORESETLOGS NOARCHIVELOG
GROUP 1 ‘path of redo log member here’ SIZE 500K,
GROUP 2 ‘path of redo log member here’ SIZE 500K
‘list of datafile name here’,
CHARACTER SET WE8DEC