How to and when to recreate control files.

[adewri]

Q. When to create control files ?
A. Create controlfiles when
1. 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 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer 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
LOGFILE
  GROUP 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 100M
DATAFILE
  '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 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

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> @E:\oracle\admin\ORCL\udump\ctl.sql

Control file created.


Database altered.

SQL> select status from v$instance;

STATUS
------------------------------------
OPEN

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------
E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
E:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
E:\ORACLE\ORADATA\ORCL\CONTROL03.CTL

To 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
LOGFILE
  GROUP 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 100M
DATAFILE
  '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;

HTH

Regards