How and when to create a control file.....?

Q. When to create control files ?A. Create controlfiles when1. You have lost all your control files.2. When you want to rename a database name (db_name)Q. How to create control files.

You need a create controlfile script for recreating control files.SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:04:00 2003Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.Enter user-name: sys as sysdbaEnter password:Connected to:Oracle9i Enterprise Edition Release 9.2.0.2.1 - ProductionWith the Partitioning, Oracle Label Security,

OLAP and Oracle Data Mining optionsJServer Release 9.2.0.2.0 - Production

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

E:\oracle\admin\ORCL\udump\orcl_ora_2052.trc
Edit the file to point the path of the datafiles and redologfiles.
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 14
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILEGROUP 1
'E:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 100M,GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 100M,GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 100MDATAFILE'
E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF','
E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF','
E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF','
E:\ORACLE\ORADATA\ORCL\INDX01.DBF','
E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS01.DBF','
E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF','
E:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF','
E:\ORACLE\ORADATA\ORCL\DRSYS01.DBF','
E:\ORACLE\ORADATA\ORCL\ODM01.DBF','
E:\ORACLE\ORADATA\ORCL\XDB01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS02.DBF','
E:\ORACLE\ORADATA\ORCL\USERS03.DBF','
E:\ORACLE\ORADATA\ORCL\USERS04.DBF'
CHARACTER SET WE8MSWIN1252;
ALTER DATABASE OPEN RESETLOGS;
and rename it to ctl.sql
Then startup the database in nomount mode and run the ctl.sql
file as sys as sysdba user
SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:25:14 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdbaEnter password:Connected to an idle instance.
SQL> startup nomountORACLE 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> @E:\oracle\admin\ORCL\udump\ctl.sql

Control file created.Database altered.

SQL> select status from v$instance;

STATUS-------------OPENS

QL> select name from v$controlfile;
NAME-----
E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
E:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
E:\ORACLE\ORADATA\ORCL\CONTROL03.CTLTo

rename the database change reuse to set in the create control file script as shown below
CREATE

CONTROLFILE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 14
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILEGROUP 1 '
E:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 100M,GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 100M,GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 100MDATAFILE'
E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF','
E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF','
E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF','
E:\ORACLE\ORADATA\ORCL\INDX01.DBF','
E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS01.DBF','
E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF','
E:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF','
E:\ORACLE\ORADATA\ORCL\DRSYS01.DBF','
E:\ORACLE\ORADATA\ORCL\ODM01.DBF','
E:\ORACLE\ORADATA\ORCL\XDB01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS02.DBF','
E:\ORACLE\ORADATA\ORCL\USERS03.DBF','

E:\ORACLE\ORADATA\ORCL\USERS04.DBF'CHARACTER SET WE8MSWIN1252;

ALTER DATABASE OPEN RESETLOGS;