How to recreate a controlfile in Oracle Database

How to recreate a controlfile in Oracle Database

The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation .
Control File contains

-Database information (RESETLOGS SCN and their time stamp)
-Archive log history
-Tablespace and datafile records
(filenames, datafile checkpoints, read/write status, offline or not)
-Redo Logs (current online redo log)
-Database’s creation date
-database name
-current archive log mode
-Log records (sequence numbers, SCN range in each log)
-RMAN catalog
-Database block corruption information
-Database ID, which is unique to each DB

Recreating a Controlfile
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
(OR)
A easier way to locate the tracefile is given below:
SQL> alter database backup controlfile to trace;
Database altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/opt/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_17822.trc

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

Note:
Specify RESETLOGS if we want Oracle to ignore the contents of the files listed in the LOGFILE clause. The log files do not have to exist but each redo_log_file_spec in the LOGFILE clause must specify the SIZE parameter. Oracle will assign all online redo log file groups to thread 1 and will enable this thread for public use by any instance. We must then open the database using ALTER DATABASE RESETLOGS.

NORESETLOGS will use all files in the LOGFILE clause as they were when the database was last open. These files must exist and must be the current online redo log files rather than restored backups.Oracle will reassign the redo log file groups to re-enabled threads as previously assigned.