Showing posts with label Multiplexing of control file. Show all posts
Showing posts with label Multiplexing of control file. Show all posts

Friday, September 4, 2020

Control File, Multiplexing of Control File

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 :

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

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

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

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 !!