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.
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 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.
Code:
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
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:
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