Oracle DBA Tutorial04 - Oracle Control File Management

A control file is a small binary file which store information on database physical layout. A control file includes ->
The database name

Names and locations of associated datafiles and redo log files
The timestamp of the database creation
The current log sequence number
Checkpoint information

The control file must be available for writing by the Oracle Database server whenever the database is open. 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 is created during database creation.

Multiplexing 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.

To add a multiplexed copy of the current control file or to rename a control file.
1. Shut down the database.
2. Copy an existing control file to a new location, using operating system commands.
3. 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.
4. Restart the database.

How to create a new control file?

When to Create New Control Files
It is necessary for you to create new control files 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 the database name.

You can create a new control file for a database using the CREATE CONTROLFILE statement. The following statement creates a new control file for the prod database

LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
GROUP 2 ('/u01/oracle/prod/redo02_01.log',
GROUP 3 ('/u01/oracle/prod/redo03_01.log',
DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
'/u01/oracle/prod/rbs01.dbs' SIZE 5M,
'/u01/oracle/prod/users01.dbs' SIZE 5M,
'/u01/oracle/prod/temp01.dbs' SIZE 5M

The CREATE CONTROLFILE statement can potentially damage specified datafiles and redo log files. Omitting a filename can cause loss of the data in that file, or loss of access to the entire database.

Backing Up Control Files

There are two options for backing up

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:

Recovering from Permanent Media Failure Using a Control File Copy
This procedure assumes that one of the control files specified in the CONTROL_FILES parameter is inaccessible due to a permanent media failure and that you have a multiplexed copy of the control file.

1. 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

2. Edit the CONTROL_FILES parameter in the initialization parameter file to replace
the bad location with the new location:
CONTROL_FILES = (/u01/oracle/prod/control01.ctl,

3. Start SQL*Plus and open the database:

Control Files Data Dictionary Views
The following views display information about control files:

V$DATABASE = Displays database information from the control file
V$CONTROLFILE = Lists the names of control files
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

No comments :