|Oracle9i Database Administrator's Guide
Release 2 (9.2)
Part Number A96521-01
This chapter explains how to create and maintain the control files for your database and contains the following topics:
Chapter 3, "Using Oracle-Managed Files" for information about creating control files that are both created and managed by the Oracle database server
Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:
The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.
The control file of an Oracle database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You might also need to create control files later, if you lose control files or want to change particular settings in the control files.
This section describes guidelines you can use to manage the control files for a database, and contains the following topics:
You specify control file names using the
CONTROL_FILES initialization parameter in the database's initialization parameter file (see "Creating Initial Control Files"). The instance startup procedure recognizes and opens all the listed files. The instance writes to and maintains all listed control files during database operation.
If you do not specify files for
CONTROL_FILES before database creation, and you are not using the Oracle Managed Files feature, Oracle creates a control file and uses a default filename. The default name is operating system specific.
Every Oracle database should have at least two control files, each stored on a different disk. If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.
The following describes the behavior of multiplexed control files:
CONTROL_FILESin the database's initialization parameter file.
CONTROL_FILESparameter is the only file read by the Oracle database server during database operation.
As already suggested, each copy of a control file should be stored on a different disk drive. One practice is to store a control file copy on every disk drive that stores members of online redo log groups, if the online redo log is multiplexed. By storing control files in these locations, you minimize the risk that all control files and all groups of the online redo log will be lost in a single disk failure.
It is very important that you back up your control files. This is true initially, and at any time after you change the physical structure of your database. Such structural changes include:
The methods for backing up control files are discussed in "Backing Up Control Files".
The main determinants of a control file's size are the values set for the
MAXINSTANCES parameters in the
CREATE DATABASE statement that created the associated database. Increasing the values of these parameters increases the size of a control file of the associated database.
This section describes ways to create control files, and contains the following topics:
The initial control files of an Oracle database are created when you issue the
CREATE DATABASE statement. The names of the control files are specified by the
CONTROL_FILES parameter in the initialization parameter file used during database creation. The filenames specified in
CONTROL_FILES should be fully specified and are operating system specific. The following is an example of a
CONTROL_FILES initialization parameter:
CONTROL_FILES = (/u01/oracle/prod/control01.ctl, /u02/oracle/prod/control02.ctl, /u03/oracle/prod/control03.ctl)
If files with the specified names currently exist at the time of database creation, you must specify the
CONTROLFILE REUSE clause in the
CREATE DATABASE statement, or else an error occurs. Also, if the size of the old control file differs from the
SIZE parameter of the new one, you cannot use the
The size of the control file changes between some releases of Oracle, as well as when the number of files specified in the control file changes. Configuration parameters such as
MAXINSTANCES affect control file size.
You can subsequently change the value of the
CONTROL_FILES initialization parameter to add more control files or to change the names or locations of existing control files.
Your operating system specific Oracle documentation contains more information about specifying control files.
You can create an an additional control file copy by copying an existing control file to a new location and adding the file's name to the list of control files. Similarly, you rename an existing control file by copying the file to its new name or location, and changing the file's name in the control file list. In both cases, to guarantee that control files do not change during the procedure, shut down the instance before copying the control file.
CONTROL_FILESparameter in the database's initialization parameter file to add the new control file's name, or to change the existing control filename.
This section discusses when and how to create new control files.
It is necessary for you to create new control files in the following situations:
CREATE DATABASEstatement. These settings include the database's name and the following parameters:
For example, you would change a database's name if it conflicted with another database's name in a distributed environment. Or, as another example, you can change the value of
MAXLOGFILES if the original setting is too low.
You can create a new control file for a database using the
CREATE CONTROLFILE statement. The following statement creates a new control file for the
prod database (formerly a database that used a different database name):
CREATE CONTROLFILE SET DATABASE prod LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log', '/u01/oracle/prod/redo01_02.log'), GROUP 2 ('/u01/oracle/prod/redo02_01.log', '/u01/oracle/prod/redo02_02.log'), GROUP 3 ('/u01/oracle/prod/redo03_01.log', '/u01/oracle/prod/redo03_02.log') NORESETLOGS DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M, '/u01/oracle/prod/rbs01.dbs' SIZE 5M, '/u01/oracle/prod/users01.dbs' SIZE 5M, '/u01/oracle/prod/temp01.dbs' SIZE 5M MAXLOGFILES 50 MAXLOGMEMBERS 3 MAXLOGHISTORY 400 MAXDATAFILES 200 MAXINSTANCES 6 ARCHIVELOG;
Oracle9i SQL Reference describes the complete syntax of the
Complete the following steps to create a new control file.
If you follow recommendations for control file backups as discussed in "Backing Up Control Files" , you will already have a list of datafiles and online redo log files that reflect the current structure of the database. However, if you have no such list, executing the following statements will produce one.
SELECT MEMBER FROM V$LOGFILE; SELECT NAME FROM V$DATAFILE; SELECT VALUE FROM V$PARAMETER WHERE NAME = 'CONTROL_FILES';
If you have no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles and online redo log files that constitute the database. Any files not specified in Step 5 are not recoverable once a new control file has been created. Moreover, if you omit any of the files that make up the
SYSTEM tablespace, you might not be able to recover the database.
If the database is open, shut down the database normally if possible. Use the
ABORT options only as a last resort.
When creating a new control file, select the
RESETLOGS option if you have lost any online redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs (Step 8). You must also specify the
RESETLOGS option if you have renamed the database. Otherwise, select the
CONTROL_FILESinitialization parameter for the database to indicate all of the control files now part of your database as created in Step 5 (not including the backup control file). If you are renaming the database, edit the
DB_NAMEparameter to specify the new name.
If you are creating the control file as part of recovery, recover the database. If the new control file was created using the
NORESETLOGS option (Step 5), you can recover the database with complete, closed database recovery.
If the new control file was created using the
RESETLOGS option, you must specify
USING BACKUP CONTROL FILE. If you have lost online or archived redo logs or datafiles, use the procedures for recovering those files.
RESETLOGSwhen creating the control file, use the
ALTER DATABASEstatement, indicating
The database is now open and available for use.
Oracle9i User-Managed Backup and Recovery Guide contains additional information about:
After issuing the
CREATE CONTROLFILE statement, you may encounter some common errors. This section describes the most common control file usage errors, and contains the following topics:
After creating a new control file and using it to open the database, check the alert file to see if Oracle has detected inconsistencies between the data dictionary and the control file, such as a datafile that the data dictionary includes but the control file does not list.
If a datafile exists in the data dictionary but not in the new control file, Oracle creates a placeholder entry in the control file under the name
nnnn is the file number in decimal).
nnnn is flagged in the control file as being offline and requiring media recovery.
The actual datafile corresponding to
nnnn can be made accessible by renaming
nnnn so that it points to the datafile only if the datafile was read-only or offline normal. If, on the other hand,
nnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of
RESETLOGS. In this case, you must drop the tablespace containing the datafile.
In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle removes references to it from the new control file. In both cases, Oracle includes an explanatory message in the alert.log file to let you know what was found.
If Oracle sends you an error (usually error
ORA-01216) when you attempt to mount and open the database after creating a new control file, the most likely cause is that you omitted a file from the
CREATE CONTROLFILE statement or included one that should not have been listed. In this case, you should restore the files you backed up in Step 3 and repeat the procedure from Step 4, using the correct filenames.
ALTER DATABASE BACKUP CONTROLFILE statement to back up your control files. You have two options:
This command writes a SQL script to the database's trace file where it can be captured and edited to reproduce the control file.
This section presents ways that you can recover your control file from a current backup or from a multiplexed copy.
This procedure assumes that one of the control files specified in the
CONTROL_FILES parameter is corrupted, the control file directory is still accessible, and you have a multiplexed copy of the control file.
This procedure assumes that one of the control files specified in the
CONTROL_FILES parameter is inaccessible due to a permanent media failure, and you have a multiplexed copy of the control file.
CONTROL_FILESparameter in the initialization parameter file to replace the bad location with the new location:
In any case where you have multiplexed control files, and you must get the database up in minimum time, you can do so by editing the
CONTROL_FILES initialization parameter to remove the bad control file and restarting the database immediately. Then you can perform the reconstruction of the bad control file and at some later time shut down and restart the database after editing the
CONTROL_FILES initialization parameter to include the recovered control file.
You can drop control files from the database. For example, you might want to do so if the location of a control file is no longer appropriate. Remember that the database must have at least two control files at all times.
CONTROL_FILESparameter in the database's initialization parameter file to delete the old control file's name.
The following views display information about control files:
Displays database information from the control file
Lists the names of control files
Displays information about control file record sections
Can be used to display the names of control files as specified in the
This example lists the names of the control files.