How to multiplex Oracle control file?

It is highly recommended to run your Oracle database with multiple control files(called control file multiplexing) in order to reduce the risk of losing control file due to corruption, accidental removal or any possible worst.

Ensure all existing control file locations:

SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/RTS/control01.ctl

Update oracle database parameter CONTROL_FILES with new location and name of control file:

alter system set control_files=
'/u01/app/oracle/oradata/RTS/control01.ctl',
'/u01/app/oracle/oradata/RTS/control02.ctl'
scope=spfile;

Note: I am demonstrating above example in test environment, that’s why the path of both control files are same, it is highly recommended to multiplex control files in a different location as well as different disks.

Shutdown database:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host

Copy existing control file(i.e. control01.ctl) to new location:

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@PR]$ ll /u01/app/oracle/oradata/RTS/control02.ctl
-rw-r-----. 1 oracle oinstall 9748480 Jun 24 17:28 /u01/app/oracle/oradata/RTS/control02.ctl

Startup database:

SQL> startup
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.
Database opened.
SQL> show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/RTS/co
                                                 ntrol01.ctl, /u01/app/oracle/o
                                                 radata/RTS/control02.ctl

Cheers!
Stay Tune. 🙂

Leave a Reply

%d bloggers like this: