How to multiplex Control Files in Oracle

Control File is a physical component of every Oracle Database, it is a small binary file, which records database structure. Control File includes information like database name, name and location of data files and redo log files, database creation timestamp, current log sequence number, checkpoint information. Additionally if we use RMAN for backup and we do use RMAN catalog, information about RMAN backups is stored in control file. Oracle Database requires at least one Control File to operate. Control Files must be accessible all the time the database is open.
Control File is crucial physical component of every Oracle database. Multiplexing Control Files to several different file systems decreases the probability of losing Control Files. The procedure used to multiplex Control Files can also be used to move or rename Control Files. It is worth to remember that each control file is exact copy of primary Control File. The locations and names of control files is stored in instance parameter named CONTROL_FILES.
The first step in this procedure is to identify the current CONTROL_FILES.

SQL> column is_recovery_dest_file format a25

SQL> column name format a60

SQL> set linesize 160

SQL> 

SQL> select status, name, is_recovery_dest_file

  2  from v$controlfile

  3  /



STATUS  NAME                                             IS_RECOVERY_DEST_FILE

------- ------------------------------------------------------------ -------------------------

        /u01/app/oracle/oradata/orcl/control01.ctl                   NO

        /u01/app/oracle/oradata/orcl/control02.ctl                   NO

        /u01/app/oracle/oradata/orcl/control03.ctl                   NO



SQL> show parameter control_files



NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      /u01/app/oracle/oradata/orcl/c

                                                 ontrol01.ctl, /u01/app/oracle/

                                                 oradata/orcl/control02.ctl, /u

                                                 01/app/oracle/oradata/orcl/con

                                                 trol03.ctl

As we can see I have three Control Files however each residing on one single disk, which means that in case of hardware failure I will lose all my Control Files. To prevent this I will multiplex into two more locations, which should be placed on separate hard drives. For presentation purposes I will simulate hard drives my creating directories in /. The suggested location for storing additional CONTROL_FILES is /u[n]/app/oracle/oradata/instance_name/ , where is two digit string like 01 or 02.

[root@localhost ~]# mkdir -p /u02/app/oracle/oradata/orcl

[root@localhost ~]# mkdir -p /u03/app/oracle/oradata/orcl

[root@localhost ~]# chown -R oracle:oinstall /u02/app/

[root@localhost ~]# chown -R oracle:oinstall /u03/app

[root@localhost ~]# chmod -R 775 /u02/app

[root@localhost ~]# chmod -R 775 /u03/app

[root@localhost ~]# ls -l /u02/app/

total 4

drwxrwxr-x 3 oracle oinstall 4096 Jul 17 12:45 oracle

[root@localhost ~]# ls -l /u03/app/

total 4

drwxrwxr-x 3 oracle oinstall 4096 Jul 17 12:45 oracle

After the locations for multiplexed control files were created we need to modify CONTROL_FILES instance parameter, by using ALTER SYSTEM command. We use scope=spfile to indicate that the change will be applied after database restart.

SQL> alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl',

  2  '/u02/app/oracle/oradata/orcl/control02.ctl', '/u03/app/oracle/oradata/orcl/control03.ctl'

  3  scope=spfile

  4  /



System altered.
Then we shut down the database using shutdown immediate and move Control Files to new locations.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.



[oracle@localhost ~]$ mv /u01/app/oracle/oradata/orcl/control02.ctl /u02/app/oracle/oradata/orcl/

[oracle@localhost ~]$ mv /u01/app/oracle/oradata/orcl/control03.ctl /u03/app/oracle/oradata/orcl/

[oracle@localhost ~]$ ls -l /u02/app/oracle/oradata/orcl/

total 9536

-rw-r----- 1 oracle oinstall 9748480 Jul 17 13:02 control02.ctl

[oracle@localhost ~]$ ls -l /u03/app/oracle/oradata/orcl/

total 9536

-rw-r----- 1 oracle oinstall 9748480 Jul 17 13:02 control03.ctl

The last thing to do is to start the database and check the location of Control Files.

[oracle@localhost ~]$ sqlplus / as sysdba



SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jul 17 13:10:29 2009



Copyright (c) 1982, 2007, Oracle.  All rights reserved.



Connected to an idle instance.



SQL> startup

ORACLE instance started.



Total System Global Area  477073408 bytes

Fixed Size                  1300716 bytes

Variable Size             201328404 bytes

Database Buffers          268435456 bytes

Redo Buffers                6008832 bytes

Database mounted.

Database opened.



SQL> set linesize 160

SQL> column name format a60

SQL> column is_recovery_dest_file format a25

SQL> select status, name, is_recovery_dest_file     

  2  from v$controlfile

  3  /



STATUS  NAME                                                         IS_RECOVERY_DEST_FILE

------- ------------------------------------------------------------ -------------------------

        /u01/app/oracle/oradata/orcl/control01.ctl                   NO

        /u02/app/oracle/oradata/orcl/control02.ctl                   NO

        /u03/app/oracle/oradata/orcl/control03.ctl                   NO

HAPPY LEARNING :)