Multiplexing Control Files

This document will detail multiplexing of control files. Having multiple control files reduces the risk of control file loss due to corruption or accidental removal. In the case of corruption or loss recovery is no more difficult than copying the control file from another location.

The first step in multiplexing the control files is to see where the exiting control files are located.

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/app/oracle/oradata/orcl/c
                                                 tl/control01.ctl, /u01/app/ora
                                                 cle/flash_recovery_area/orcl/c
                                                 ontrol02.ctl
SQL>

We want to verify were the current control files are because we want to ensure that we do no put the multiplexed control files on the same location/disk. This way we ensure that we have a copy of the control in the case of disk loss.

We can see that currently there are two control files. In order to add a control we need to update the CONTROL_FILES parameter with the new location using the ALTER SYSTEM SET CONTROL_FILES command.

SQL> alter system 
  2  set control_files='/u02/app/oracle/oradata/orcl/ctl/control01.ctl', 
  3                '/u01/app/oracle/flash_recovery_area/orcl/control02.ctl',
  4                '/u03/app/oracle/oradata/orcl/ctl/control03.ctl'
  5  scope=spfile;
  
System altered.

SQL>

In the command above I added the location /u03/app/oracle/oradata/orcl/ctl/. Also note that the scope was set to SPFILE and not BOTH or MEMORY this is to prevent the database from attempting to look for the new file at this time.

Next we shutdown the database and copy one of the existing control files to the new location.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
[oracle@ora1 ~]$ cp /u02/app/oracle/oradata/orcl/ctl/control01.ctl /u03/app/oracle/oradata/orcl/ctl/control03.ctl
[oracle@ora1 ~]$ ls -alh /u03/app/oracle/oradata/orcl/ctl/
total 9.4M
drwxr-xr-x 2 oracle oinstall 4.0K Apr 26 09:07 .
drwxr-xr-x 5 oracle oinstall 4.0K Apr 26 08:21 ..
-rw-r----- 1 oracle oinstall 9.3M Apr 26 09:07 control03.ctl
[oracle@ora1 ~]$ 

Now all that is left is to bring the database back up.

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/app/oracle/oradata/orcl/c
                                                 tl/control01.ctl, /u01/app/ora
                                                 cle/flash_recovery_area/orcl/c
                                                 ontrol02.ctl, /u03/app/oracle/
                                                 oradata/orcl/ctl/control03.ctl
SQL>

Multiplexing the control files is a simple process that provides great benefits in the case of control file corruption and loss.