Chapter 4: MAINTAINING THE CONTROL FILE

Chapter 4: MAINTAINING THE CONTROL FILE

CHAPTER # 4

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 weather 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 control files are lost, the database must be recovered before it can be opened.
Size initially defined by    CREATE DATABASE

CONTROL FILE CONTENTS:
A control file contains the following entries:
1. Database name and identifier
2. Timestamp of database creation.
3. Tablespace name  TB information is updated as TB are added or dropped.
4. Names & Location of data files and online redo log files  updated when data file or online redo log file is added, renamed, or drop from the database.
5. Current online redo log file sequence number  sequence number is recorded when log switch occur.
6. Checkpoint information  check point information are recorded as check point are made.
7. Begin and undo segments 
8. Redo log archive information  Location and status of archive logs are recorded when archived occurs. 
9. Backup information    Location and status of backup are recorded by recovery manager (RMAN) utility.

CONTROL FILE SECTIONS:
Control file consist of two types of sections.
1- Reusable
2- Not reusable

REUSABLE SECTION :
Reusable section store RMAN information such as
Backup data files names
Backup online redo log file names
They are use in circular manner and can be reuse only by RMAN.

NOT REUSABLE SECTION:
Belong to physical structure
Having Location of Data files
             Location of Control files.

MULTIPLEXING THE CONTROL FILE:

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 of different physical disk. If a control file is lost, a multiplexed copy of control file can be used, to start the instance without database recovery.

MULTIPLEXING BY SPFILE:

PRACTICAL #

STEP # 1:
First see the destination and how many control file already have using
SQL> select * from v$controlfile;

STEP # 2:
Alter the spfile by using
SQL>   ALTER SYSTEM SET CONTROL_FILES =
             ‘D:\ORACLE\ORADATA\ORCL\CONTROL05.CTL’ ,
             ‘D:\ORACLE\ORADATA\ORCL\CONTROL06.CTL’  scope=spfile

STEP # 4:
Shutdown the database
SQL >     SHUTDOWN IMMEDIATE;

STEP #5:
Copy control file to another location and rename the file as you give step 3 using operation system and paste original location.

STEP # 6:
Start the database using
SQL >  STARTUP;

MULTIPLEXING BY PFILE:
Use the following steps to multiplex control file when using pfile

PRACTICAL #

STEP # 1:
Shutdown the database
SQL > SHUTDOWN;

STEP # 2:
Goto  location :  D:\oracle\admin\orcl\pfile\init.ora
Add control file in parameter file

STEP # 3:
Copy control file to another location and rename the file as you give step 2 using operation system and paste original location.

STEP # 4:
Start the database using pfile
SQL > STARTUP PFILE = ‘D:\ORACLE\ADMIN\ORCL\PFILE\init.ora’;


MANAGING CONTROL FILES WITH OMF

Control file are created OMF automatically during the database creation time if the control_files parameter is not specified in the initialization parameter file.

Location are defined by   DB_CREATE_ONLINE_LOG_DEST_n
If this parameter in not set than
Location will be DB_CREATE_FILE_DEST parameter
If both of the parameter is not set
Than control_files are not created by OMF.

Names are uniquely generated and displayed in the alertSID.log.

PRACTICAL #

OBTAINING CONTROL FILE INFORMATION

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

 SQL > SELECT * FROM V$CONTROLFILE;

 SQL > SHOW PARAMETER CONTROL_FILES;

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