Hey Folks,
We shall deep dive to know more about the Control file and its function and Why the control file is a very important component and what is the use of it. Here we shall know about the Control File Multiplexing method and steps.
Control
File :
Control file Characteristics is as below :
- A small binary
file
- Defines the current state of the physical database
- Maintains the integrity of the database
- Required :
- At MOUNT state during database startup
- To operate the Database
- Linked to a
single database
- Loss May require
recovery
- Sized initially
by CREATE DATABASE
The control file is a
small binary file, it is 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 whether the database is in a valid state to use.
A control file is updated continuously by 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 file are lost, the database must be recovered before it can be opened.
Control File Contents :
A control file
contains the Fallowing entries :
- Database name
and identifier
- Time stamp of
the Database 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 End of
UNDO segments
- Redo log Archive
information
- Backup Information
The information in
the Control file includes the following OR when the control file entries are
updated:
- The database name is taken from either the name specified by the initialization parameter DB_NAME or the name used in the CREATE DATABASE statement.
- A database identifier is recorded when the database is created.
- Timestamp of database creation is also recorded at the time of database creation.
- Name and location of Associated data files and online redo log files are updated when a data file or online redo log file is added to, renamed in, or dropped from the database.
- Tablespace information is updated as tablespaces are added or dropped.
- Online redo log file history is recorded during log switches.
- Location and status of archive logs are recorded when archiving occurs.
- Location and status for backups are recorded by the Recovery Manager (RMAN) utility.
- Current log sequence number is recorded when log switches occur.
- Checkpoint Information is recorded as checkpoints are made.
The Control file consists of two types of sections :
- Reusable
- Not reusable
Reusable sections
store RMAN information, Such as backup data file names and backup online redo
log file names. They are used in a circular manner and can be reused by only
RMAN.
Multiplexing the Control File:
To
Safeguard against a single point failure of the control file, It is strongly
recommended that the control file be Multiplexed, Storing each copy in a
different physical disks. If a control file is lost, a multiplexed copy of the control file can be used to restart the instance without database recovery.
Control
files can be multiplexed up to Eight times by :
- Creating multiple control files
when the database is created by including the control file names and full
path in the initialization Parameter file :
CONTROL_FILES = $HOME/ORADATA/u01/ctrl01.ctl,
$HOME/ORADATA/u02/ctrl02.ctl
- Adding a control file after the database is created.
Backing
Up the Control File :
Because
the control file records the physical structure of the database, you should
immediately make a backup of your control file after making changes to the physical structure of the Database.
Multiplexing
the Control File When Using SPFILE :
For
Multiplexing the control file using SPFILE, Below are the steps have to follow
:
- Alter the SPFILE :
Alter the SPFILE using the ALTER SYSTEM SET command, alter the SPFILE to include a list of all control files to be used: Main Control file and multiplexed copies. Below is the Command:
SQL> ALTER SYSTEM SET control files =
'$HOME/ORADATA/u01/ctrl01.ctl','$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;
2. Shut Down the Database :
Shutdown the database in order to create the additional control files on the operating system.
SQL> shutdown immediate
3. Create Additional control files :
Create additional control files using the operating system copy command, Create the additional control files as required and verify that the files have been created in the appropriate directories.
cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u02/ctrl02.ctl
4. Start The Database:
When the database is started the SPFILE will be read and the Oracle Server will maintain all the control files listed in the CONTROL_FILES parameter.
SQL> startup
Multiplexing
the Control File When Using PFILE:
- Shut Down the Database:
Shut Down the Database in order to create additional control files on the operating system.
SQL> shutdown immediate
2. Create Additional control files:
Create additional control file using the operating system copy command, Create the additional control files as required and verify that the files have been created in the appropriate directories.
cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u02/ctrl02.ctl
3. Add control file names to PFILE :
Alter the PFILE to Add names of all the control files.
CONTROL_FILES = $HOME/ORADATA/u01/ctrl01.ctl,
$HOME/ORADATA/u02/ctrl02.ctl
4. Start the database :
When the database is started the PFILE will be read and the Oracle server will maintain all the control files listed in the CONTROL_FILES parameter.
SQL> startup
Managing Control File with OMF :
- OMF (Oracle Managed File ) is created if the CONTROL_FILES parameter is not specified.
- Locations are defined by DB_CREATE_ONLINE_LOG_DEST_n.
- Names are uniquely generated and displayed in the alertSID.log.
If using an init.ora file, the CONTROL_FILES parameter must be set to the OMF generated Names, Which can be found by selecting from V$CONTROLFILE or from the alertSID.log. If an SPFILE is used, the CONTROL_FILES parameter is automatically set and saved when the database is created.
The locations of the control files are determined by the DB_CREATE_ONLINE_LOG_DEST_n Parameter. If this parameter is not set the control files location will be defined by the DB_CREATE_FILE_DEST Parameter. If neither of these parameter sets, then the control file will not be of OMF. If the control files are not of OMF then the CONTROL_FILES parameter must be set in the initialization parameter file or An error will be received.
The control names are uniquely generated (ora_cmr7t30p.ctl) and displayed in the alertSID.log when the files are created
Obtaining Control File Information :
Information About Control File Status and locations can be retrieved by querying the following views.
- V$CONTROLFILE: Lists the name and status of all the 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 the Name, Status, and Location of the control files.
See below
a few Examples to Obtaining Control file Information :
- To Obtain the location and names of the control files, Query the V$CONTROLFILE view,
SQL> SELECT NAME FROM V$CONTROLFILE;
NAME
----------------------------------------
/u02/oraprod/proddata/cntrl01.dbf
/u02/oraprod/proddata/cntrl02.dbf
/u02/oraprod/proddata/cntrl03.dbf
- You can also
query the V$PARAMETER view.
SQL> SELECT name, value from V$PARAMETER where name =
'control_files';
NAME VALUE
--------------- --------------------------------------
control_files /u02/oraprod/proddata/cntrl01.dbf,
/u02/oraprod/proddata/cntrl02.dbf,
/u02/oraprod/proddata/cntrl03.dbf
- To Obtain
Information about the different sections of the control files, query the
V$CONTROLFILE_RECORD_SECTION view.
SQL> SELECT type, record_size, records_total, records_used FROM v$controlfile_record_section WHERE TYPE='DATAFILE';
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED
----------------- ----------- ------------- ------------
DATAFILE 520 512 62
The RCORDS_TOTAL column specifies the number of records allocated for a special section.
- To SHOW
PARAMETER command can also be used to find the location of the control
files.
SQL> SHOW PARAMETER control_file;
NAME TYPE VALUE
-------------------------- ----------- ------------------------
control_file_record_keep_time integer 7
control_files string /u02/oraprod/proddata/cntrl01.dbf,
/u02/oraprod/proddata/cntrl02.dbf, /u02/oraprod/proddata/cntrl03.dbf
Information In Several dynamic performance views is obtained from the control files. See below List is for examples :
- V$BACKUP
- V$DATAFILE
- V$TEMPFILE
- V$TABLESPACE
- V$ARCHIVE
- V$LOG
- V$LOGFILE
- V$LOGHIST
- V$ARCHIVED_LOG
- V$DATABASE
Thank you !!
