How to Recreate a Controlfile [ID 735106.1]

How to Recreate a Controlfile [ID 735106.1]

In this Document
Goal
Solution
References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.2.0.2 - Release: 9.0.1 to 11.2
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 the following 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

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 
;

ALTER TABLESPACE TEMP_TEST ADD TEMPFILE '˜/oradata/V11/temp01.dbf'™ reuse;

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 saving 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;

   

 SQL> startup nomount;                                  

 SQL>@control.sql

Note: After recreating the controlfile ensure you add the existing TEMP files:

Example:

alter tablespace temp_ts add tempfile '˜/oradata/V11/temp01.dbf'™ reuse;

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