Control files
Every Oracle instance must have one or more control files. The control file is a binary file that is critical to Oracle, but is not directly readable by a user, nor is it editable by a text editor. The control file can be thought of as a software “bootstrap” file; it contains information that Oracle requires to start. Information stored in the control file includes:
Names and locations of data files
Names and locations of redo log files
Information on the status of archived log files
The current redo log sequence number
Redo log information required for recovery
Backup history (Oracle8 only)
Timestamp information on the instance creation and startup/shutdown
Essential parameters specified at database creation (e.g., MAXDATAFILES)
The information stored in the control file is so critical that if the control file is lost or damaged, the only options available for recovery are either to create a new control file (assuming that the DBA has access to all pertinent information required) or to rebuild the database and restore from a backup. Because of the critical nature of the control file, Oracle allows the DBA to maintain multiple mirrored control files, as specified by the CONTROL_FILES parameter in the INIT.ORA file. For example, the following line from INIT.ORA specifies two control files:
CONTROL_FILES = (/disk00/oracle/control01.ctl,/disk02/oracle/control02.ctl)
Tip
We strongly recommend that you maintain multiple mirrored control files on separate disks in case disk failure occurs, and, where possible, on different disk controllers in case controller failure occurs. Three or more mirrored control files are not unusual at well-administered Oracle installations.
Since the control file is not human readable, and is used only by Oracle itself, we recommend that, for operating systems with file protection, the control file be made readable only by the Oracle owner. In Unix, control files should be owned by Oracle, be assigned to group DBA, and have a protection of 600, which gives read/write access to the owner, but no access to the group or world.
Although the control file is in a binary format and is readable only by Oracle, a method is provided to create a script containing SQL statements that can be used to recreate a control file. This text version may be edited and used to create a new control file with modified values.
To create a text version of the control file, the DBA may use this command:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;
After execution of this command, a trace file will be created in a directory specified by the INIT.ORA parameter BACKGROUND_DUMP_DEST; by default, this will usually be $ORACLE_HOME/rdbms/log. You must go to the directory containing trace files and look for a file with an extension of .trc and a date/time stamp at the time you executed the ALTER DATABASE command. This trace file will be similar to the one shown in the following sample, which was created with Oracle Version 7.3.4 on an HP platform, and will contain the SQL statements required to create a new control file and restart the database.
Dump file /disk00/oracle/product/7.3.4/rdbms/log/DW1/ora_13607.trc
Oracle7 Server Release 7.3.4.2.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.2.0 - Production
ORACLE_HOME = /disk00/oracle/product/7.3.4
System name: HP-UX
Node name: datasrv2
Release: B.10.20
Version: E
Machine: 9000/800
Instance name: DW1
Redo thread mounted by this instance: 1
Oracle process number: 24
UNIX process pid: 13607, image: oracleDW1
*** SESSION ID:(33.132) 1998.09.20.20.10.16.174
*** 1998.09.20.20.10.16.173
# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current versions of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DW1" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES 2
MAXLOGHISTORY 200
LOGFILE
GROUP 1 '/disk07/oracle/oradata/DW1/log01.log' SIZE 2M,
GROUP 2 '/disk10/oracle/oradata/DW1/log02.log' SIZE 2M,
GROUP 3 '/disk07/oracle/oradata/DW1/log03.log' SIZE 2M,
GROUP 4 '/disk10/oracle/oradata/DW1/log03.log' SIZE 2M
DATAFILE
'/disk00/oracle/oradata/DW1/system01.dbf',
'/disk05/oracle/oradata/DW1/temp01',
'/disk00/oracle/oradata/DW1/tools01.dbf',
'/disk04/oracle/oradata/DW1/ldata01.dbf',
'/disk08/oracle/oradata/DW1/ldata02.dbf',
'/disk07/oracle/oradata/DW1/user01.dbf',
'/disk09/oracle/oradata/DW1/rbs01.dbf',
'/disk14/oracle/oradata/DW1/data01.dbf',
'/disk15/oracle/oradata/DW1/data02.dbf',
'/disk02/oracle/oradata/DW1/index01.dbf'
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;This SQL text file may then be edited (carefully!) by the DBA. You might change the value of one of the configuration parameters (MAXDATAFILES, for example), or perhaps change the name or location of a LOGFILE. To replace a control file, perform the following steps:
Use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command to create a control file trace (.trc) file.
Locate the .trc file in the BACKGROUND_DUMP_DEST directory, and rename it to something meaningful.
Perform a NORMAL or IMMEDIATE shutdown of the database. It is best if all tablespaces are online at the time of the shutdown; otherwise, recovery will be required after the control file is recreated.
Edit the .trc file created in step 1. Be sure to remove the documentation lines at the top of the file.
Using Server Manager or SQL*DBA, CONNECT AS INTERNAL.
Execute the edited file, which will recreate the control file and start the database.
Since this is a critical operation and an error may result in a database that cannot be opened, we highly recommend that you back up the database prior to creating a new control file.