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

Here we will discuss ‘what is control file in oracle’ and ‘how to multiplex control files on separate disk’.

Table of Contents

What is control file in oracle.

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 control file:

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

How to multiplex control files?

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;