ControlFile

·A small binary file

·Defines current state of physical database

·Maintains integrity of database

·Required:

  -At MOUNT state during database start up

  -To operate the database

·Linked to asingle database

·Loss may require recovery

·Sized initially by CREATE DATABASE

The Size of Control File

The main determinants of the size of a control file are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCE parameters in the CREATE DATABASE statement that created the associated database. Increasing the values of these parameters increases the size of acontrol file of the associated database.

Control File Contents

A control file contains the following entries:

·Database nameand identifier

·Time stamp ofdatabase 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 endof undo segments

·Redo log archive information

·Backup information

The control file is a fairly small file(it can grow up to 64MB or so in extreme cases) that contains a directory of the other files Oracle needs. The parameter file tells the instance where control files are, and the control files tell the instance where the database and online redo log files are.

The control files also tell Oracle other things, such as information about checkpoints that have taken place, the name of the database(which should match the DB_NAME parameter), the timestamp of the database as it was created, an archive redo log history(this can make a control file large in some cases), RMAN information, and so on.

Control files should be multiplexed either by hardware(RAID) or by Oracle when RAID or mirroring is not available. More than one copy of them should exist, and they should be stored on separate disk, to avoid losing them in the event you have a disk failure. It is not fatal to lose your control files—it just makes recovery that much harder.

Control files are something a developer will probably never have to actually deal with. To a DBA they are an important part of the database, but to a software developer they are not extremely relevant.

Multiplexing the Control File

Every Oracle Database should have at least two 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 be havior 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 disks.

SHOW CONTROL FILES:

$cd $ORACLE_HOME/dbs

$ strings spfileSID.ora | grep control_files

SQL> show parameter control_files;

SQL> select name from v$controlfile;

When using Spfile

1.Alter the SPFILE:

SQL> alter system set control_files=

     '/u01/app/oradata/example/control01.ctl',

     '/u01/app/oradata/example/control02.ctl',

     '/u01/app/oradata/example/control03.ctl' scope=spfile;

2.Shut down the database:

SQL> shutdown immediate;

3.Create additionalcontrol files:

$ cd /u01/app/oradata/example/

$ cp control01.ctlcontrol02.ctl

$ cp cintrol01.ctl control03.ctl

4.Start the database:

SQL> startup;

Get Control File Information

Information about control file status and locations can retrieved by querying the following views.

·v$controlfile: Lists the name and status of all control files associated with the instance.

  ·v$parameter: Lists status and location of all parameters.

·v$controlfile_record_section: Provides information about the control file record sections.

·show parameter control_files: Lists thename, status, and location of the control files.