1. Dump a trace of the controlfile.
Use SQL*Plus (or Server Manager) and enter:
SQL> alter database backup controlfile to trace ;
2. Modify the created trace file (located in the user_dump_dest directory):
a) remove from the first line to the line before “STARTUP NOMOUNT”
b) remove line “ALTER DATABASE OPEN” to the end of the file
c) change value of maxdatafiles, maxlogfiles and other parameters as desired
d) rename the file to something .sql (such as cntl.sql)
3. Shutdown the database.
4. Backup the database.
5. Run Server Manager/SQLPLUS and do the following:
SVRMGR> connect internal or connect /as sysdba
SVRMGR> @cntl.sql (The SQL script that you created in step 2) or SQLPLUS>@cntl.sql
SVRMGR> alter database open noresetlogs ; or SQLPLUS> alter database open noresetlogs
SQLPLUS if you are using 9i SVRMGRL if you are using 8i or below.
Also Please read the following article from metalink…
Doc ID: Note:1012929.6
Subject: How to Recreate the Controlfile
Content Type: TEXT/PLAIN
Creation Date: 04-OCT-1995
Last Revision Date: 14-JAN-2005
This article describes how you can recreate your controlfile.
SCOPE & APPLICATION
For DBAs who need to recreate the controlfile.
You should only need to recreate your control file under very special
– All current copies of the control file have been lost or are corrupted.
– You need to change a “hard” database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,
– You are restoring a backup in which the control file is corrupted or
– Oracle Customer Support advises you to do so.
– 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.