3 - 4 minutes readManaging Control Files

A control file is a small binary file that records the physical structure of the database with database name, Names and locations of associated datafiles, online redo log files, timestamp of the database creation, current log sequence number and Checkpoint information.

Note:

• Without the control file, the database cannot be mounted.

• You should create two or more copies of the control file during database creation.

Role of Control File:

When Database instance mount, Oracle recognized all listed file in Control file and open it. Oracle writes and maintains all listed control files during database operation.

Important:

• If you do not specify files for CONTROL_FILES before database creation, and you are not using the Oracle Managed Files feature, Oracle creates a control file in <DISK>:ORACLE_HOMEDTATBASE location and uses a default filename. The default name is operating system specific.

• Every Oracle database should have at least two control files, each stored on a different disk. If a control file is damaged due to a disk failure, the associated instance must be shut down.

• Oracle writes to all filenames listed for the initialization parameter CONTROL_FILES in the database’s initialization parameter file.

• The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle database server during database operation.

• If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

How to Create Control file at the time od database creation:

The initial control files of an Oracle database are created when you issue the CREATE DATABASE statement. The names of the control files are specified by the CONTROL_FILES parameter in the initialization parameter file used during database creation.

How to Create Additional Copies, Renaming, and Relocating Control Files

Step:1 Shut down the database.

Step:2 Copy an existing control file to a different location, using operating system commands.

Step:3 Edit the CONTROL_FILES parameter in the database’s initialization parameter file to add the new control file’s name, or to change the existing control filename.

Step:4 Restart the database.

When you Create New Control Files?

• All control files for the database have been permanently damaged and you do not have a control 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’s name and the following parameters: MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

Steps for Creating New Control Files

Step:1 Make a list of all datafiles and online redo log files of the database.

SELECT MEMBER FROM V$LOGFILE;

SELECT NAME FROM V$DATAFILE;

SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘CONTROL_FILES’;

Step:2 Shut down the database.

Step:3 Back up all datafiles and online redo log files of the database.

Step:4 Start up a new instance, but do not mount or open the database:

STARTUP NOMOUNT

Step:5 Create a new control file for the database using the CREATE CONTROLFILE statement.

Example:

CREATE CONTROLFILE REUSE DATABASE "<DB_NAME" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 ‘<DISK>:DirectoryREDO01.LOG’ SIZE 5024K,

GROUP 2 ‘<DISK>:DirectoryREDO02.LOG’ SIZE 5024K,

GROUP 3 ‘<DISK>:DirectoryREDO03.LOG’ SIZE 5024K

# STANDBY LOGFILE

DATAFILE

‘<DISK>:DirectorySYSTEM.DBF’,

‘<DISK>:DirectoryUNDOTBS.DBF’

CHARACTER SET WE8MSWIN1252

;

Step:6 Open the database using one of the following methods:

• If you specify NORESETLOGS when creation the control file, use following commands: ALTER DATABASE OPEN;

• If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

ALTER DATABASE OPEN RESETLOGS;

TIPS:

When creating a new control file, select the RESETLOGS option if you have lost any online redo log groups in addition to control files. In this case, you will need to recover from the loss of the redo logs . You must also specify the RESETLOGS option if you have renamed the database. Otherwise, select the NORESETLOGS option.

Backing Up Control Files

Method 1:

Back up the control file to a binary file (duplicate of existing control file) using the following statement:

ALTER DATABASE BACKUP CONTROLFILE TO ‘<DISK>:Directorycontrol.bkp’;

Method 2:

Produce SQL statements that can later be used to re-create your control file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

How to retrieve information related to Control File:

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