Multiplexing Control Files in Oracle

Multiplexing Control Files in Oracle

by

Multiplexing Control Files in Oracle: In this article we will discuss about Multiplexing Control Files in Oracle.

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.

These steps apply in Oracle 12c :

STEP-1 :

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

SQL>
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCL/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 fast_recovery_area/ORCL/contro
                                                 l02.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.

STEP-2 :

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>
SQL> alter system set control_files='/u01/app/oracle/oradata/ORCL/control01.ctl', '/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl', '/home/oracle/control03.ctl' scope=spfile;

System altered.

In the command above I added the location /home/oracle/. 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.

STEP-3 :

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

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@orakldba ~]$
[oracle@orakldba ~]$
[oracle@orakldba ~]$ cp /u01/app/oracle/oradata/ORCL/control01.ctl /home/oracle/control03.ctl
[oracle@orakldba ~]$
[oracle@orakldba ~]$ ls -ltr /home/oracle/control03.ctl
-rw-r----- 1 oracle oinstall 10600448 Aug 18 02:23 /home/oracle/control03.ctl
[oracle@orakldba ~]$

STEP-4 :

Startup Your Database :

SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  8622776 bytes
Variable Size             448793928 bytes
Database Buffers           75497472 bytes
Redo Buffers                3956736 bytes
Database mounted.
Database opened.

STEP- 5 :

Check your new list of Control Files :

SQL>
SQL>
SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCL/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 fast_recovery_area/ORCL/contro
                                                 l02.ctl, /home/oracle/control0
                                                 3.ctl

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

ADDITIONAL NOTES :

  • 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.
  • Make sure that the new disk / location for your new control file exists.
  • Make sure that Oracle user has permission to create the control file on the new location.

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.

Leave a Comment