RECREATING 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.

Code:

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 11.2.0.2.0 - Production

SQL> alter database backup controlfile to trace;

Database altered.

SQL>

This will create a trace file in the /u01/app/oracle/diag/rdbms/db11g/DB11G/trace

/u01/app/oracle/diag/rdbms/db11g/DB11G/trace/orcl_ora_2052.trc

Code:

CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5 
    MAXLOGMEMBERS 5 
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/redo01.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/redo02.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/redo03.log'  SIZE 100M BLOCKSIZE 512 
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/11.2.0/oradata/system01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/sysaux01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/undotbs01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/users01.dbf'
CHARACTER SET AL32UTF8
;   

and rename it to ctl.sql


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

Code:

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

Code:Code:

CREATE CONTROLFILE SET DATABASE "DB11G" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 5 
    MAXLOGMEMBERS 5 
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/redo01.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/redo02.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/redo03.log'  SIZE 100M BLOCKSIZE 512 
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/11.2.0/oradata/system01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/sysaux01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/undotbs01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/users01.dbf'
CHARACTER SET AL32UTF8
;