Create Control file manually - Oracle

When to create control files ?

=>  Create control files in situations when :

1. You have lost all your control files.
2. When you want to rename your database name (db_name);

Note :although we can use utility called DBNEWID to change the DBNAME. DBNEWID can be used to change :

-- Only the DBID of a database
-- Only the DBNAME of a database
-- Both the DBNAME and DBID of a database

 How to create control files.

You need a create controlfile script for recreating control files.
Code:
SQL*Plus: Release 11.2.0.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:
 Enterprise Edition Release 11.2.0.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options


SQL> alter database backup controlfile to trace;

Database altered.

SQL>

This will create a trace file in the udump directory.

In my case it was

E:\oracle\admin\ORCL\udump\orcl_ora_20327.trc

Edit the file to point the path of the datafiles and redologfiles.

Code:
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 14
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 100M,
  GROUP 2 'C:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 100M,
  GROUP 3 'C:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 100M
DATAFILE
  'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
  'C:\ORACLE\ORADATA\ORCL\INDX01.DBF',
  'C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',
  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
CHARACTER SET WE8MSWIN1252
;

ALTER DATABASE OPEN RESETLOGS;

and rename it to control_file.sql

Then startup the database in nomount mode and run the control_file.sql file as sys as sysdba user

Code:
SQL*Plus: Release 11.2.0.0 - Production

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> @C:\oracle\ORCL\udump\control_file.sql

Control file created.


Database altered.

SQL> select status from v$instance;

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

SQL> select name from v$controlfile;

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


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

Code:
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 14
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 100M,
  GROUP 2 'C:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 100M,
  GROUP 3 'C:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 100M
DATAFILE
  'C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  'C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF',
  'C:\ORACLE\ORADATA\ORCL\INDX01.DBF',
  'C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF',
  'C:\ORACLE\ORADATA\ORCL\USERS01.DBF',
CHARACTER SET WE8MSWIN1252
;

ALTER DATABASE OPEN RESETLOGS;