When and How? Create Control file Manually. ?

When and How? Create Control file Manually. ?

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.

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  "INDIA"

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

;

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.

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.

Note:

Note: 

o Beginning in Oracle8, when you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. Later, if you add a file whose number exceeds MAXDATAFILES but is less than or equal to the value specified by the DB_FILES initialization parameter, the control file automatically expands to allow the datafile portion to accommodate more files.  You will not need to recreate the controlfile in this case.

o In Oracle 10.2 all sections of the control file are now automatically extended when they run out of space. This means that there is no longer a requirement to re-create the control file when changes in the configuration parameters are needed. These include the MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES parameters. This feature eliminates database downtime because changes to these parameters are now automatically made.
For more information, please refer to the document below:
10gR2 New Feature: Eliminate Control File Re-Creation (Doc ID 329981.1) 

================================================================================

Opening A Copied Database With Incorrect Redo Log Group Locations In Controlfile (Doc ID 112001.1)

Problem Description
-------------------

You have copied a database from a hot backup.  You have placed your
datafiles in a different directory path. You recreated your controlfile
and did not list the new location of the redo log groups. You attempt
to open the database with "Recover database using backup controlfile
until cancel";  cancel; "alter database open resetlogs" and get
ora-344 and ora-7352.
 
Solution Description
--------------------
 
1. Go to startup mount

2. Drop all logfile group members except the active one.

   Alter database drop logfile group #;

3. Recreate the logfile groups with correct location.

   alter database add logfile ('/disk3/log3a.rdo','/disk4/log3b.rdo') size 1M;

4. startup mount;

5. alter databse backup controlfile to trace;

6. recreate the  controlfile and correct the location of the newly created
   logfile groups.

7. run the new controlfile.sql file

8. recover database using backup controlfile until cancel;

9. cancel;

10. alter database open resetlogs;

=============================================================================