How to Recreate a Controlfile

	Doc ID:	735106.1	Type:	HOWTO
	Modified Date :	10-MAR-2009	Status:	PUBLISHED

In this Document
  Goal
  Solution

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.1.0.7
Information in this document applies to any platform.

Goal

This article describes how you can recreate your controlfile.

Solution

Warning

• You should only recreate your control file under circumstances.
• All current copies of the control file have been lost or are corrupted.
• You are restoring a backup in which the control file is corrupted or missing.
• You need to change a hard limit database parameter in the controlfile.
• .If you are moving your database to another server and files are located in a different location.
• Oracle Customer Support advises you to.

Creating a new Controlfile from an existing database that is mounted or open. 

First you must generate an ascii dump of the controlfile.

Whilst the database is mounted or open issue:

SQL> alter database backup controlfile to trace;

A trace file will be generated in the user_dump_destination directory.

SQL> show parameter user_dump_dest

NAME            TYPE         VALUE
--------------  ------       ------------------------------------------------
user_dump_dest  string       /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace


After navigating to the directory locate the latest trace file by date/time by issuing:   ls -ltr.

% cd /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace

% ls -ltr

Once you locate the file it will appear as an ordinary trace file:

Trace file /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace/V11_ora_31225.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/db_1
Once you locate the file it will appear as an ordinary trace file:

You are interested in the section that contains the create controlfile script.

Modify the trace file and use it as a script to create the control. 

          
CREATE CONTROLFILE REUSE DATABASE "V11" NORESETLOGS ARCHIVELOG  
MAXLOGFILES 16  
MAXLOGMEMBERS 3  
MAXDATAFILES 100  
MAXINSTANCES 8  
MAXLOGHISTORY 292  
LOGFILE  
GROUP 1 '/oradata/V11/redo01.log' SIZE 50M,  
GROUP 2 '/oradata/V11/redo02.log' SIZE 50M,  
GROUP 3 '/oradata/V11/redo03.log' SIZE 50M  
DATAFILE  
'/oradata/V11/system01.dbf',  
'/oradata/V11/sysaux01.dbf',  
'/oradata/V11/undotbs01.dbf' 
'/oradata/V11/user01.dbf'  
CHARACTER SET WE8MSWIN1252  
;

  

It is important to delete everything above the "CREATE CONTROLFILE" and 

everything after the CHARACTER SET. Ensure you leave the semi colon. ";". 

In the above example we are choosing the NORESETLOGS option and running the 

database in archivelog mode. 

After successfully saved the script you are now able to recreate the 

controlfile. When shutting down the database ensure that you shutdown with the 

immediate option.

    
    
SQL> shutdown immediate;

  

NOTE: Oracle recommeds that a full database is taken prior to a creation of the 

controlfile.
    

    
 SQL> startup nomount;
                          
 SQL>@control.sql

  

Once the controlfile is successfully created the database is automatically 

mounted. If you have opened the database with a resetlogs it is important to 

take a backup asap.

Creating a new controlfile from a database that is not able to mount.

Under the rare occasion that you do not have a controlfile to either:

1. Restore

2. or have a script from a "backup controlfile to trace script"

you must create a script from the beginning.

CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS ARCHIVELOG

Follow the format listing:

- Location of redo logs.

- Location of datafiles

- Specifying the characterset.

Once you have listed all files correctly you are ready to recreate your controlfile

SQL> startup nomount; 



SQL>@control.sql