Recreate Control Files; How? and When?

Recreate Control Files; How? and When?

When?

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

If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated
When you want to rename your database at that time also u need to recreate control files

How?
You need a create controlfile script for recreating control files.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 – Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.0.1.1.1- Production

#this is for creating Control File Creation script
SQL> alter database backup controlfile to trace;
Database altered.
SQL>

This will create a trace file in the udump directory.
Im my case it was

C:\oracle\test\udump\ORA03620.TRC
Edit the file to point the path of the datafiles and redologfiles.

CREATE CONTROLFILE REUSE DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1696
LOGFILE
GROUP 1 ‘C:\ORACLE\TEST\REDO\LOG1.LOG’ SIZE 100M,
GROUP 2 ‘C:\ORACLE\TEST\REDO\LOG2.LOG’ SIZE 100M
DATAFILE
‘C:\ORACLE\TEST\DATA\DATA.DBF’,
‘C:\ORACLE\TEST\TS_TEST1.DBF’,
‘C:\ORACLE\TEST\UNDO.DBF’,
‘C:\ORACLE\TEST\DATA\USER_1.DBF’,
‘C:\ORACLE\TEST\USER_2.DBF’,
‘C:\ORACLE\TEST\USER_UNDO.DBF’
‘C:\ORACLE\TEST\USER_TEMP.TMP’
CHARACTER SET WE8MSWIN1252
;

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP_TEST ADD TEMPFILE ‘C:\ORACLE\TEST\TMP_TEST.DBF’ REUSE;
# End of tempfile additions.
#

ALTER DATABASE OPEN RESETLOGS;

and rename it to control_trace.sql
Then startup the database in nomount mode and run the control_trace.sql file as sys as sysdba user
SQL*Plus: Release 9.0.1.1.1 – Production on Tue Dec 30 10:45:47 2008

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 80812648 bytes
Fixed Size 453224 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> @ C:\oracle\test\udump\ control_trace.sql

Control file created.

Database altered.

Check the status from v$instance
Check the name from v$controlfile