Create Control file Manually. When and How ?

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. So We can Manually create a new control file for a database using the CREATE CONTROLFILE statement. The following statement creates a new control file for the  database (a database that formerly used a different database name) .

When to Create New Control Files :

It is necessary for us to create new control files in the following situations:

1.) All control files for the database have been permanently damaged and we do not have a control file backup.

2.) We want to change the database name. For example, we would change a database name if it conflicted with another database name in a distributed environment.

3.) The compatibility level is set to a value that is earlier than 10g, and we must make a change to an area of database configuration that relates to any of the following parameters from the CREATE DATABASE or CREATE CONTROLFILE commands: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES. If compatibility is 10g or later, we do not have to create new control files when we make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.

For example, assume that when we created the database or recreated the control files, we set MAXLOGFILES to 3. Suppose that now we want to add a fourth redo log file group to the database with the ALTER DATABASE command. If compatibility is set to 10g or later, we can do so and the controlfiles automatically expand to accommodate the new logfile information. However, with compatibility set earlier than 10g, our ALTER DATABASE command would generate an error, and we would have to first create new control files .

Command to Create Controlfile Manually 

C:\>sqlplus sys/ramtech@noida as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Apr 18 17:31:50 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> STARTUP NOMOUNT

SQL> CREATE CONTROLFILE REUSE DATABASE  "NOIDA"

NORESETLOGS archivelog

MAXLOGFILES 5 

MAXLOGMEMBERS 3 

MAXDATAFILES 10 

MAXINSTANCES 1 

MAXLOGHISTORY 113

LOGFILE 

GROUP 1 'D:\oracle\oradata\noida\REDO01.LOG' SIZE 50M,

GROUP 2 'D:\oracle\oradata\noida\REDO02.LOG' SIZE 50M,

GROUP 3 'D:\oracle\oradata\noida\REDO03.LOG' SIZE 50M

DATAFILE 

'D:\oracle\oradata\noida\SYSTEM01.DBF' , 

'D:\oracle\oradata\noida\USERS01.DBF' , 

'D:\oracle\oradata\noida\EXAMPLE01.DBF' , 

'D:\oracle\oradata\noida\SYSAUX01.DBF' ,

'D:\oracle\oradata\noida\TRANS.DBF' ,

'D:\oracle\oradata\noida\UNDOTBS01.DBF' ;

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.

Enjoy       J J J