Maintaining the Control File

Maintaining the Control File

Control File

The control file is a small binary file necessary for the database to start and operate successfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use. A control file is updated continuously by the Oracle server during database use, so it must be available for writing whenever the database is open. The information in the control file 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 accessible, 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. At least one control file is required, but control files can be multiplexed up to eight times.

Control File Contents

• Database name and identifier

• Time stamp of database creation

• Tablespace names

• Names and locations of data files and redo log files

• Current redo log file sequence number

• Checkpoint information

• Begin and end of undo segments

• Redo log archive information

• Backup information

Multiplexing

To safeguard against a single point of failure of the control file, it is strongly recommended that the control file be multiplexed, storing each copy on a different physical disk. If a control file is lost, a copy of the control file can be used to restart the instance without database recovery. Control files can be multiplexed up to eight times. The Oracle server creates and maintains all files listed in this parameter when the instance is started.

               Here we can Multiplex controlfile in two ways, if our database using spfile then we can multiplex controlfile using spfile, if our database using pfile then we can multiplex control file using pfile.

Multiplexing the controlfile using spfile

-->Database is in up (Database open and running)
-->If our database consists of 1 control file in the location

Control_file = ‘/Oracle/product/10.2.0/oradata/TEST/ctrl1.ctl’ then we are going to multiplex another control file in another location like this show below.
-->Sql> alter system set control_files = ‘/Oracle/product/10.2.0/oradata/TEST/ctrl1.ctl’,’/u01/TEST/ctrl2.ctl’ SCOPE=SPFILE;
-->Sql>shutdown
-->By issuing alter system set command we can modify that spfile dynamically, because spfile is a binary file we cannot modify that file manually, so we have to modify that spfile dynamically. We just modify that file but physically ctrl2.ctl doesn’t exist at that location /u01/TEST. We have to copy the old controlfile to that location.
-->$ cp /Oracle/product/10.2.0/oradata/TEST/ctrl1.ctl /u01/TEST/ctrl2.ctl
-->After copying the old control file to new  control file then we have to startup the database
-->Sql>startup

è Then automatically new controlfile also updated by oracle server.

è Like that we can multiplex up to eight control files

Multiplexing the controlfile using pfile(init.ora)

-->If our database using pfile then we can easily edit that pfile. To edit that pfile first we have to shutdown the database.
-->Sql>shutdown
-->Edit the pfile

$ vi initTEST.ora

In that parameter file

Control_files = ‘/Oracle/product/10.2.0/oradata/TEST/ctrl1.ctl’,’/u01/TEST/ctrl2.ctl’

Save and exit (:wq)
-->We just modify the parameter, but physically new control file doesn’t exist so we have to copy the old control file to new control file in another new location
-->$ cp /Oracle/ product/10.2.0/oradata/TEST/ctrl1.ctl /u01/TEST/ctrl2.ctl
-->After copying the control file we have to run the database or startup the database then new controlfile is updated by oracle server automatically
-->Sql>startup

How to create controlfile

we can create controlfile using the command

sql>alter database backup controlfile to trace;

-->After giving this statement oracle will write the CREATE CONTROLFILE statement in a trace file. The trace file will be randomly named something like ORA23212.TRC and it is created in USER_DUMP_DEST directory.

-->Go to the USER_DUMP_DEST directory and open the latest trace file in text editor. This file will contain the CREATE CONTROLFILE statement. It will have two sets of statement one with RESETLOGS and another without RESETLOGS. Since we are changing the name of the Database we have to use RESETLOGS option of CREATE CONTROLFILE statement. Now copy and paste the statement in a file. Let it be c.sql

Obtaining Control File Information

To obtain the location and names of the control files, use the dynamic performance view V$CONTROLFILE.

Sql>SELECT name FROM V$CONTROLFILE;

NAME

------------------------------------

/u01/home/db03/ORADATA/u01/ctrl01.ctl

/u01/home/db03/ORADATA/u01/ctrl01.ctl

2 rows selected.

The V$PARAMETER view can also be used.

Sql>SELECT name, value from V$PARAMETER

   WHERE name = 'control_files';

NAME Value

------------- -------------------------------------

control_files /u01/home/db03/ORADATA/u01/ctrl01.ctl

sql>show parameters control_files;

NAME TYPE VALUE

------------ ------- ------------------------------

control_files string /u01/home/db03/ORADATA/u01/ctrl01.ctl

To obtain information about the different sections of the control files, query the

V$CONTROLFILE_RECORD_SECTION dynamic performance view.

Sql>SELECT type, record_size, records_total, records_used

   FROM v$controlfile_record_section

   WHERE type=’DATAFILE’;

TYPE RECORD_SIZ RECORDS_TO RECORDS_US

------- ---------- -------- ------------------

DATAFILE 180 30 4

1 row selected.