Control File in DBMS

The Control File of the database is a binary file that contains database information such as the database name, data about the database log files and data files, timestamp of database creation, tablespace name, checkpoint information and backup information. Database can't open without a Control file.
Getting information of current control file in use:


SQL>SHOW PARAMETER control_files;
SQL>SELECT * FROM v$controlfile;
SQL>SELECT * FROM v$controlfile_record_section;

MULTIPLEXING THE CONTROL FILE USING SPFILE

Let we had two control file control01 and control02 in our database. We are going to multiplex control03 in our database.


SQL>ALTER SYSTEM SET CONTROL_FILES='$HOME/ORADATA/U01/control01.ctl'
'$HOME/ORADATA/U01/control02.ctl','$HOME/ORADATA/U01/control03.ctl' SCOPE=spfile; 
SQL>shutdown immediate;

Copy the existing control file with new name control03.ctl on defined path of spfile parameter so that it is available on the os level.

$cp $HOME/ORADATA/U01/control01.ctl $HOME/ORADATA/U01/control03.ctl

Control files can be multiplexed up to eight times. The Oracle server maintains all files listed in spfile parameter when instance is started.

SQL>startup

MULTIPLEXING THE CONTROL FILE USING PFILE

SQL>create pfile from spfile;
SQL>shutdown immediate;

Copy the existing control file with new name control03.ctl

$cp $HOME/ORADATA/U01/control01.ctl $HOME/ORADATA/U01/control03.ctl

Open the pfile and add a new control file control03.ctl and its path in CONTROL_FILES parameter.

SQL>startup;

CONTROL FILE BACKUP

Because the control file records the physical structure of the database so it's an important part of oracle database. It's important to make a backup of control file.

SQL>ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/control_file.bkp';

We can also make backup of control file to a trace file. This trace can contains a script to recreate a control file. Trace file created in UDUMP directory.


SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/create_control_file.sql'

INFO-TECH