what is control file in oracle and how to multiplex controlfiles on separate disk

Here we will discuss 'what is control file' and 'how to multiplex control files on separate disk'.

Control file is a binary file which stores Database name, associated data files, redo files, DB creation time and current log sequence number. Without control file database cannot be started and can hamper data recovery.

The control file is used to keep track of the database's status and physical structure. Oracle strongly recommended that database has minimum two control file and they are stored in separate disk. We can create control file up to a maximum of 8.

Show Control File:-

SQL> show parameter control_files;

SQL> SELECT name FROM v$controlfile;

Check Content of control file.

SQL>SELECT * FROM v$controlfile_record_section;

Backup existing  control file in UDUMP;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Make a binary copy of the control file:

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

Generate an SQL file to create the control file:

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

Multiplex control file.

Make sure that the new control file resides on a separate physical disk.

The purpose of multiple control files is to protect the database in case of a disk failure.

SQL>ALTER SYSTEM SET CONTROL_FILES=

’/u01/app/oracle/oradata/db01/control01.ctl’,

'/u02/app/oracle/fast_recovery_area/db01/control02.ctl’,

‘/u03/oradata/db01/control03.ctl’,

‘/u04/oradata/db01/control04.ctl’

SCOPE=spfile;

SQL> SELECT name FROM v$controlfile;

We will discuss on next topic ‘How to recreate control file‘ .