5 Managing Control Files

This chapter explains how to create and maintain the control file s for your database and contains the following topics:

What Is a Control File?
Guidelines for Control Files
Cre ating Control Files
Troubleshooting After Creating Control Files

Backing Up Control Files

Recovering a Cont rol File Using a Current Copy

Dropping Control Files

Displaying Control File Information

See Also:

Part III, " Automated File and Storage Management " for information about creating control files that are both created and managed by the Oracle Database server

What Is a Control File?

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes :

The database name
Names and locations of associated datafiles and redo lo g files
The timestamp of the database creation
The current log sequence num ber
Checkpoint information

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 contro l 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 can also create control files later, if you lose control files or w ant to change particular settings in the control files.

Guidelines for C ontrol Files

This section describes guid elines you can use to manage the control files for a database, and contains the following topics:

Provide Filenames for the Control Files
Multiplex Control Files on Different Disks
Back Up Control Files
Manage the Size of Control Files

Provide Filenames for the Control Files

You specify control file names using the CONTROL_FILES initialization parameter in the database initialization parameter fi le (see "Creating Initial Control Files"). The instance recognizes and opens all the listed file during start up, and the instance writes to and maintains all listed control files during database operation.

If you do not specify files f or CONTROL_FILES before database creation:

If you are not using Oracle-managed files, then the database creates a control file and uses a default filename. The default name is operating system specific.
If you are using Oracle-managed files, then the initialization parameters you set to enable that feature determine the name a nd location of the control files, as described in Chapter 11, " Using Oracle-Managed Files".

Multiplex Control Files on Different Disks

Every Oracle Database should have at least tw o control files, each stored on a different physical 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 behavior of multiplexed control files is this:

The database writes to all filenames listed for the initialization parameter CONTROL_FILES in the database initialization parameter file.
The database reads only the first file listed in the CONTROL_FILES parameter during database operation.
If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

Note:
Oracle strongly recommends that your database has a minimum of two control files and that they are located on separate physical disk s.

One w ay to multiplex control files is to store a control file copy on every disk drive that stores members of redo log groups, if the redo log is multiplexed. By storing control files in these locations, you minimize the risk that all control files and all groups of the redo log will be lost in a single disk failure.

Back Up Control Files

It is very important that you back up your control files. This is true initially, and every time you change the physical structure of your database. Such structural changes include:

Adding, dropping, or renaming datafiles
Adding or dropping a tablespace, or altering the read/write state of the tablespace
Adding or dropping redo log files or groups

The methods for backing up control files are discussed in "Backing Up Control Files".

Manage the Size of Control Files

The main determinants of the size of a control file are the values set fo r the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTA NCES parameters in the CREATE DATABASE statement that created the associated database. Increasing the values of t hese parameters increases the size of a control file of the associated database.

See Also:

Your operating system specific Oracl e documentation contains more information about the maximum control file size.
Oracle Database SQL Reference for a description of t he CREATE DATABASE statement

< /div>

Creating Control Files

This sec tion describes ways to create control files, and contains the following topics:

Crea ting Initial Control Files
Creating Additional Copies, Renaming, and Relocating Control Files
Creating New Control Files

Creating Initial Control Files

The initial control files of an Oracle Database are created when you issue the CREATE DATA BASE 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 ar e 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 REUSE clause.

The s ize of the control file changes between some releases of Oracle Database, as well as when the number of files specified in the contro l file changes. Configuration parameters such as MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and 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.

See Also:

Y our operating system specific Oracle documentation contains more information about specifying control files.

Creating Additional Cop ies, Renaming, and Relocating Control Files

You can create an additional control file copy for multiplexing by copying an existing control file to a new location and adding the file name to the list of control files. Similarly, you rename an ex isting control file by copying the file to its new name or location, and changing the file name in the control file list. In both cas es, to guarantee that control files do not change during the procedure, shut down the database before copying the control file.

< p>To add a multiplexed copy of the current control file or to rename a control file:

Shut down th e database.
Copy an existing control file to a new location, using operating system commands.
Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control file name, or to change the existing control filename.
Restart the database.

Creating New Control Files

This section discusses when and how to create new control files.

When to Create New Control Files

It is necessary for you to create new control files in the following s ituations:

All control files for the database have been permanently damaged and you do not have a contro l file backup.
You want to change one of the permanent database parameter settings originally specified in the CREATE DATABASE statement. These settings include the database name and th e following parameters: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

For example, you would change a database name if it conflicted with another database name in a distributed environment, or you would change the value of MAXLOGFILES if the original setting is too low.

Note:
You can change the database name and DBID (internal database identifier) using the DBNEWID utility. See Oracle Database Utilities for information about using this utility.

< /div>

The CREATE CONTROLFILE Sta tement

You can create a new contr ol file for a database using the CREATE CONTROLFILE statement. The following statement creates a new control file for th e prod database (a database that formerly used a different database name):

CREATE CONTROLF
ILE
   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') 
   RESETLOGS
   DATAFILE '/u01/oracle/prod/s
ystem01.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
   MAXINSTANCE
S 6
   ARCHIVELOG;

Cautions:

The CREATE CONTROLFILE statement can potentially damage specified datafiles and redo lo g files. Omitting a filename can cause loss of the data in that file, or loss of access to the entire database. Use caution when issu ing this statement and be sure to follow the instructions in "Steps for Creating New Control Files".
If the database had forced logging enabled before creating the new control file, and you want it to continue to be enabled, then you must specify the FORCE LOGGING clause in the CREATE CONTROLFILE statement. See "Specifying FORCE LOGGING Mode".

See Also:

Oracle Database SQL Reference describes the complete syntax of the CREATE CONTROLFILE statement

Steps for Creating New Control Files

Complete the following steps to create a new control file.

Make a list of all datafiles and re do log files of the database.

If you follow recommendations for control file backups as discussed in "Back ing Up Control Files" , you will already have a list of datafiles and redo log files that reflect the current structure of the da tabase. 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 h ave no such lists and your control file has been damaged so that the database cannot be opened, try to locate all of the datafiles an d redo log files that constitute the database. Any files not specified in step 5 are not recoverable once a n ew control file has been created. Moreover, if you omit any of the files that make up the SYSTEM tablespace, you might n ot be able to recover the database.
Shut down the database.

If the database is open, shut down the database n ormally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.
Back up all datafiles and redo log files of the database.
Start up a new instance, but do not mount or open the database:
```
STARTUP NOMOUNT
```

Create a new control file for the database using the CREATE CONTROLFILE state ment.

When creating a new control file, specify the RESETLOGS clause if yo u have lost any 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 specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.

Store a backup of the new control file on an offline storage device. See "Backing Up Control Files" for inst ructions for creating a backup.
Edit the CONTROL_FILES initialization parameter for the database to ind icate all of the control files now part of your database as created in step 5 (not including the backup contr ol file). If you are renaming the database, edit the DB_NAME parameter in your instance parameter file to specify the ne w name.
Recover the database if necessa ry. If you are not recovering the database, skip to step 9.

If you are creating the control file as pa rt of recovery, recover the database. If the new control file was created using the NORESETLOGS clause (step 5), you can recover the database with complete, closed database recovery.

If the new control file was created usi ng the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you have lost online or archived re do logs or datafiles, use the procedures for recovering those files.

See Also:
Orac le Database Backup and Recovery Basics and Oracle Datab ase Backup and Recovery Advanced User's Guide for information about recovering your database and methods of recovering a los t control file
Open the database using one of the following methods:
- If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.
```
ALTER DATABASE OPEN; 
```
- If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.
```
ALTER DATABASE OPEN R
ESETLOGS;
```

The database is now open and available for use.

Troubleshooting After Creating Control Files

After issuing the CREATE CONTROLFILE statement, you may encounter some error s. This section describes the most common control file errors:

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 file to see if the database has detected inconsistencies between the data dictionary and the control file, such as a datafi le in the data dictionary includes that the control file does not list.

If a datafile exists in the data dictionary but not in the new control file, the database creates a placehold er entry in the control file under the name MISSINGnnnn, where nnnn is the f ile number in decimal. MISSINGnnnn is flagged in the control file as being offline and requiring m edia recovery.

If the actual datafile corresponding to MISSINGnnnn is read-only or offline normal, then you can make the datafile accessible by renaming MISSINGnnnn to the name of the actua l datafile. If MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, the n you cannot use the rename operation 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.

Conversely, if a datafile listed in the control file is not present in the data dictionary, then the database removes references to it from the new control file. In both cases, the database includes an explanatory message in the alert file to let you know what was found.

Handling Errors During CREATE CONTROLFILE

< a id="sthref763" name="sthref763">If Oracle Databas e sends you an error (usually error ORA-01173, ORA-01176, ORA-01177, ORA-01215, o r 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.

Backing Up Control Files

Use the ALTER DATABASE BACKUP CONTROLFILE statement to back up your control file s. You have two options:

Back up the control file to a binary file (duplicate of existing control file) using the following statement:
```
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp'
;
```
Produce SQL statements that can later be used to re-create your control file:
```
A
LTER DATABASE BACKUP CONTROLFILE TO TRACE;
```
This command writes a SQL script to the database trace file where it can be ca ptured and edited to reproduce the control file.

See Also:
Oracle Database Backup a nd Recovery Basics for more information on backing up your control files

Recovering a Control File Using a Curren t Copy

This section presents ways that you can recover your control file from a current backup or from a multiplexed c opy.

Re covering from Control File Corruption Using a Control File Copy

This procedure assumes that one of the control files s pecified in the CONTROL_FILES parameter is corrupted, that the control file directory is still accessible, and that you have a multiplexed copy of the control file.

With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:
```
% cp /u03/oracle/prod/control03.ctl  /u02/
oracle/prod/control02.ctl
```
Start SQL*Plus and open the database:
```
SQL> STARTUP
<
/pre>
```

Recovering from Permanent Media Failure Using a Control File Copy

This procedure as sumes that one of the control files specified in the CONTROL_FILES parameter is inaccessible due to a permanent media fa ilure and that you have a multiplexed copy of the control file.

With the instance shut down, use an operating system command to copy the current copy of the control file to a new, accessible location:
```
% cp /u01/oracle/prod/control01.ctl  /u04/oracle/prod/control03.ctl
```

Edit the CONTROL_FILES parame ter in the initialization parameter file to replace the bad location with the new location:

CONTROL_FIL
ES = (/u01/oracle/prod/control01.ctl,
                 /u02/oracle/prod/control02.ctl, 
                 /u04/oracle/prod/control03.c
tl)

Start SQL*Plus and open the database:
```
SQL> STARTUP
```

I f you have multiplexed control files, you can get the database started up quickly by editing the CONTROL_FILES initializ ation parameter. Remove the bad control file from CONTROL_FILES setting and you can restart the database immediately. Th en 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.

Dropping Control Files

Yo u want to drop control files from the database, for example, if the location of a control file is no longer appropriate. Remember tha t the database should have at least two control files at all times.

Shut down the database.
Edit the CONTROL_FILES parameter in the database initialization parameter file to delete the old control fil e name.

Restart the database.

Note:

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

Displaying Control File Info rmation

The following views display information about control files:

View	Description
`V$DATABASE`	Displays database information from the control file
`V$CONTROLFILE`	Lists the names of control fi les
`V$CONTROLFILE_RECORD_SECTION`	Displays information about control file record sections
`V$PARAMETER`	Displays the names of control files as specified in the `CONTROL_FILES` initialization parameter

This example lists the names of the control files.

SQL> SELECT NAME FROM V$CONTROLFILE;

NAME
-------------------------------------
/u01/oracle/prod/control01.ctl
/u02/ora
cle/prod/control02.ctl 
/u03/oracle/prod/control03.ctl