Oracle Fundamental I - Chapter 6: Maintaining the Control Files

In this chapter, we are going to talk about how to manage the control files.
The control fiel is a small binary file necessary for the database to sgtart and operate successfully. Each control file is associated only one Oracle database. Before a database is opened, the control file is read to determine whether the database is in a valid state to use.
A control file is udpated continuously by the oracle server during database use, so it must be available for writing whenever the databasei s open. The information in the control fiel can be modified only by the Oracle server; no database administrator or end user can edit the control file.
If for some reason the control file is not acessible, the database does not function properly. If all copies of a database's control files are lost, the database must be recovered before it can be opened.

Control File contents:

• Database name and identifier
• Time stamp of database creation
• Tablespace names
• Names and locations of data files and online redo log files
• Current online redo log file sequence number
• Checkpoint information
• Begin and end of undo segments
• Redo log archive information
• Backup Information

Multiplexing the Control File
To safeguard against a single pint of failure of the control file, it is strongly reommended that the control file be multiplexed, storing eah copy on a different physical disk.
Control files can be multiplexed up to eight times by:

• Creating multiple control files when the database is created by including the control file names and full path in the initialization parameter file:
• CONTROL_FILES=$HOME/ORADATA/data01/ctr101.ctl,$HOME/ORADATA/data01/ctr102.ctl
• Addming a control file after the database is created

In Oracle Fundamental II Backup and Recovery, we will talk more detail about how to backup the control file.

You follow different steps to multiplexing control files in Oracle database using SPFILE and PFILE.
Using SPFILE,
1. Alter the SPFILE:

SQL> ALTER SYSTEM SET control_files =           ‘$HOME/ORADATA/data01/ctr101.ctl’,           ‘$HOME/ORADATA/data02/ctr102.ctl’ SCOPE=SPFILE;

2. Shutdown the database:

SQL> shutdown immediate;

3. Create Additional control files in OS level by using the OS supported copy command:

$ cp $HOME/ORADATA/data01/ctr101.ctl $HOME/ORADATA/data01/ctr102.ctl

4. Start the database:

SQL> startup

Using PFILE,
1. Shutdown the database:

SQL> shutdown immediate;

2. Create Additional control files in OS level by using the OS supported copy command:

$ cp $HOME/ORADATA/data01/ctr101.ctl $HOME/ORADATA/data01/ctr102.ctl

3. Add control file names to the PFILE:

CONTROL_FILES = $HOME/ORADATA/data01/ctr101.ctl,$HOME/ORADATA/data01/ctr102.ctl)

4. Start the database:

SQL> startup

Note: In Oracle Recover Manager, it provides a feature called replicate controlfile, which can accomplish the same task of multiplexing the control files. We will discuss this feature in Fundamental II.

To obtaining the control file information, there are a couple dynamic views that you can query. Few dynamic views below are example:

• V$CONTROLFILE: lists the name and status of all control files associated with the instance.
• V$PARAMETER: lists status and location of all parameters, in which you find the control file parameter
• V$CONTROLFILE_RECORD_SECTION: Provides inforamtion about the control file record sections
• Show PARAMETER CONTROL_FILES: lists the name, status, and location of control files.