|
-
How to and when to recreate control files.
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
Last edited by adewri; 02-28-2003 at 08:25 AM.
Amar
"There is a difference between knowing the path and walking the path."
 
-
Good stuff Amar!
Perhaps you should point out that "alter database backup controlfile to trace;" needs to be done BEFORE you loose the control files!
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
-
Dear ,
if you have lost all control files then do the following.
1. create new database using dbca on the same database server version.
2. create control file script as above.
3. Move new data files to some other place and copy old data files of your database on the new server.
4. Modify control file script so that it points to copied data files (Add all data files path).
5. now create new control file with the modified control file script.
6. Recover your database.(see database recovery process).
-
Hi, your post is something new to me and helpful. Anyway, upon a thorough search about the topic , I found out that the first control files of an oracle database are created when you issue the create database statement. On the other side, we are able to manually create a new control file for a database using the create control file statement. This is the reason why control file is recreated. I just hope, I make sense with this
-
What about the db references in the spfile when you want to rename the db? When running the create controlfile command I got this error:
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01504: database name 'FOO1' does not match parameter db_name 'foo'
I created pfile from spfile, changed all db references there, and converted back to spfile.
Then it worked!
Somehow offtopic, but also edit name in /etc/oratab to reflect name change for dbca.
-
i'm using Adewri's code as an example. Instead of the word reuse, you need to use the word "SET".
Keep in mind that the Oracle database has changed significantly in the last 14 years. The original
post was written for 7.3.4, 8.0 or 8i. Few people used RMAN back when this post was written.
Now, you can restore the control file and use the nid program to change the database name.
Therefore you don't specifically need to recreate the control files. Is there a specific problem
that you are trying to solve?
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;
-
I'm learning about the CloneDB feature using copy on write with Direct NFS introduced in Oracle 11. In a first step you need to create a db clone and creating a cold backup plus creating controlfile is one method often described. I know it might not be the latest preferred method, but I'm still learning to understand what's going on under the hood.
-
 Originally Posted by 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.
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
All current copies of the control file have been lost or are corrupted.
You are restoring a backup in which the control file is corrupted or missing.
You need to change a hard limit database parameter in the controlfile.
-
Please note - this thread started 02-28-2003, 08:19 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
