5
Managing Control Files

This chapter explains how to create and maintain the control files for your database, and includes the following topics:

• Guidelines for Control Files

• Creating Control Files

• Troubleshooting After Creating Control Files

• Dropping Control Files

Guidelines for Control Files

This section describes guidelines you can use to manage the control files for a database, and includes the following topics:

• Name Control Files

• Multiplex Control Files on Different Disks

• Place Control Files Appropriately

• Manage the Size of Control Files

Name Control Files

Assign control file names via the CONTROL_FILES initialization parameter in the database's parameter file. CONTROL_FILES indicates one or more names of control files separated by commas. The instance startup procedure recognizes and opens all the listed files. The instance maintains all listed control files during database operation.

During database operation, Oracle writes to all necessary files listed for the CONTROL_FILES parameter.

Multiplex Control Files on Different Disks

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 an intact copy of the control file and the instance can be restarted; no media recovery is required.

Behavior of Multiplexed Control Files

The following list describes the behavior of multiplexed control files:

• Two or more filenames are listed for the initialization parameter CONTROL_FILES in the database's parameter file.

• The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle Server during database operation.

• If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

The only disadvantage of having multiple control files is that all operations that update the control files (such as adding a datafile or checkpointing the database) can take slightly longer. However, this difference is usually insignificant (especially for operating systems that can perform multiple, concurrent writes) and does not justify using only a single control file.

Place Control Files Appropriately

Each copy of a control file should be stored on a different disk drive. Furthermore, a control file copy should be stored 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.

Manage the Size of Control Files

The main determinants of a control file's size are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and 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.

See Also: The maximum control file size is operating system specific. See your operating system-specific Oracle documentation for more information.

Creating Control Files

Every Oracle database has a control file. A control files records the physical structure of the database and contains:

• the database name

• names and locations of associated databases and online redo log files

• the timestamp of the database creation

• the current log sequence number

• checkpoint information

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 must be created during database creation. On some operating systems, Oracle creates 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 ways to create control files, and includes the following topics:

• Creating Initial Control Files

• Creating Additional Control File Copies, and Renaming and Relocating Control Files

• New Control Files

• Creating New Control Files

Creating Initial Control Files

You create the initial control files of an Oracle database by specifying one or more control filenames in the CONTROL_FILES parameter in the parameter file used during database creation. The filenames specified in CONTROL_FILES should be fully specified. Filename specification is operating system-specific.

If files with the specified names currently exist at the time of database creation, you must specify the CONTROLFILE REUSE parameter in the CREATE DATABASE statement, or else an error occurs. Also, if the size of the old control file differs from that of the new one, you cannot use the REUSE option. 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 MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES affect control file size.

If you do not specify files for CONTROL_FILES before database creation, Oracle uses a default filename. The default name is also operating system-specific.

You can subsequently change the value of the CONTROL_FILES parameter to add more control files or to change the names or locations of existing control files.

See Also: For more information about specifying control files, see your operating system-specific Oracle documentation.

Creating Additional Control File Copies, and Renaming and Relocating Control Files

You add a new control file by copying an existing 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.

To Multiplex or Move Additional Copies of the Current Control Files

1. Shut down the database.

2. Copy an existing control file to a different location, using operating system commands.

3. Edit the CONTROL_FILES parameter in the database's parameter file to add the new control file's name, or to change the existing control filename.

4. Restart the database.

New Control Files

You can create a new control file for a database using the CREATE CONTROLFILE statement. This is recommended in the following situations:

• All control files for the database have been permanently damaged and you do not have a control file backup.

• You want to change one of the permanent database settings originally specified in the CREATE DATABASE statement, including the database's name, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

  For example, you might need to change a database's name if it conflicts with another database's name in a distributed environment. As another example, you might need to change one of the previously mentioned parameters if the original setting is too low.

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 ('logfile1A', 'logfile1B') SIZE 50K,
    GROUP 2 ('logfile2A', 'logfile2B') SIZE 50K
   NORESETLOGS
   DATAFILE 'datafile1' SIZE 3M, 'datafile2' SIZE 5M
 MAXLOGFILES 50
 MAXLOGMEMBERS 3
 MAXDATAFILES 200
 MAXINSTANCES 6
 ARCHIVELOG;

See Also: For more information about the CREATE CONTROLFILE statement, see the Oracle8i SQL Reference.

Creating New Control Files

This section provides step-by-step instructions for creating new control files.

To Create New Control Files

1. Make a list of all datafiles and online redo log files of the database.

   If you followed the recommendations for database backups, you should already have a list of datafiles and online redo log files that reflect the current structure of the database.

   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.

2. Shut down the database.

   If the database is open, shut down the database with normal priority, if possible. Use the IMMEDIATE or ABORT options only as a last resort.

3. Back up all datafiles and online redo log files of the database.

4. Start up an new instance, but do not mount or open the database.

5. Create a new control file for the database using the CREATE CONTROLFILE statement.

   When creating the new control file, select the RESETLOGS option if you have lost any online redo log groups in addition to the 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 NORESETLOGS option.

6. Store a backup of the new control file on an offline storage device.

7. Edit the parameter files of the database.

   Edit the parameter files of the database to indicate all of the control files created in Step 5 and Step 6 (not including the backup control file) in the CONTROL_FILES parameter.

8. Recover the database if necessary.

   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.

9. Open the database.

   Open the database using one of the following methods:

• If you did not perform recovery, open the database normally.

• If you performed complete, closed database recovery in Step 8, start up the database.

• If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

The database is now open and available for use.

See Also: See the Oracle8i Backup and Recovery Guide for more information about:

• listing database files

• backing up all datafiles and online redo log files of the database

• recovering online or archived redo log files

• closed database recovery

Troubleshooting After Creating Control Files

After issuing the CREATE CONTROLFILE statement, you may encounter some common errors. This section describes the most common control file usage errors, and includes the following topics:

• Checking for Missing or Extra Files

• Handling Errors During CREATE CONTROLFILE

Checking for Missing or Extra Files

After creating a new control file and using it to open the database, check the ALERT log 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 MISSINGnnnn (where nnnn is the file number in decimal). MISSINGnnnn is flagged in the control file as being offline and requiring media recovery.

In the following two cases only, the actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn to point to it.

Case 1: The new control file was created using the CREATE CONTROLFILE statement with the NORESETLOGS option, thus allowing the database to be opened without using the RESETLOGS option. This would be possible only if all online redo logs are available.

Case 2: It was necessary to use the RESETLOGS option of the CREATE CONTROLFILE statement, thus forcing the database to be opened using the RESETLOGS option, but the actual datafile corresponding to MISSINGnnnn was read-only or offline normal.

If, on the other hand, it was necessary to open the database using the RESETLOGS option, and MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible (since the datafile requires media recovery that is precluded by the results of RESETLOGS). In this case, the tablespace containing the datafile must be dropped.

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 file to let you know what it found.

Handling Errors During CREATE CONTROLFILE

If Oracle sends you an error (usually error ORA-01173, ORA-01176, ORA-01177, ORA-01215, or 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.

Dropping Control Files

You can drop control files from the database. For example, you might want to do so if the location of a control file is inappropriate. Remember that the database must have at least two control files at all times.

1. Shut down the database.

2. Edit the CONTROL_FILES parameter in the database's parameter file to delete the old control file's name.

3. Restart the database.

   WARNING: This operation does not physically delete the unwanted control file from the disk. Use operating system commands to delete the unnecessary file after you have dropped the control file from the database.

---