Mir Sayeed Hassan – Oracle Blog

Managing Multiplexing of Control Files in Oracle database 11gR2

As per the Oracle DB, every db has its own controlfile; the control file is a binary file that records the physical structure of the db

 The control file contains:

• The database name
• Name & location of datafiles & redo log files
• Current log seq no
• Checkpoint info
• Timestamp of DB Creation

Oracle recommended to have the multiplex of control file & at least it should have 2 controlfile in 2 different disks, i.e: 1 controlfile in one disk location as ‘/u01’ and another controlfile in ‘/u02’, because if one controlfile gets corrupt in one disk then you can copy the available from another disk in such scenario you don’t have to recover the controlfile.

Create the multiplex of controlfile while creation of the database or later, below example are creating the multiplex of controlfile after the creation of the database.

Procedure to perform the multiplex of controlfile:

sys@TESTDB> select INSTANCE_NAME,VERSION from v$instance;

INSTANCE_NAME    VERSION
---------------- -----------
testdb           11.2.0.4.0

 Verify the controlfile exist in database:

sys@TESTDB> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------
control_files                        string      /u01/app/oracle/oradata/prim/control01.ctl

Note:

As per the above query, there is only 1 controlfile existing in it & it’s not a best practice, At least it should have 2 controlfile in 2 different locations.

 Shutdown the database:

sys@TESTDB>shu immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

Now Copy the control file from existing location to new location using operating system command as shown below:

[oracle@ogg-test1 prim]$ pwd
/u01/app/oracle/oradata/prim

[oracle@ogg-test1 prim]$ ll control01.ctl
-rw-r-----. 1 oracle oracle 24002560 Feb 12 12:11 control01.ctl

[oracle@ogg-test1 prim]$ cp control01.ctl /u01/app/oracle/fast_recovery_area/prim/

[oracle@ogg-test1 prim]$ ll control01.ctl
-rw-r-----. 1 oracle oracle 24002560 Feb 12 12:11 control01.ctl

Rename the controlfile

[oracle@ogg-test1 prim]$ mv control01.ctl control02.ctl
[oracle@ogg-test1 prim]$ ll control02.ctl
-rw-r-----. 1 oracle oracle 24002560 Feb 12 12:11 control02.ctl

Now open the parameter file (inittestdb.ora) and specify the new location as shown below:

[oracle@ogg-test1 prim]$ cd  $ORACLE_HOME/dbs

[oracle@ogg-test1 dbs]$ vi inittestdb.ora
*.control_files='/u01/app/oracle/oradata/prim/control01.ctl'

Change to

*.control_files=’/u01/app/oracle/oradata/prim/control01.ctl’,’/u01/app/oracle/fast_recovery_area/prim/control02.ctl’

 Now startup the database:

sys@TESTDB> startup

ORACLE instance started.
Total System Global Area 2455228416 bytes
Fixed Size                  2255712 bytes
Variable Size             771753120 bytes
Database Buffers         1660944384 bytes
Redo Buffers               20275200 bytes
Database mounted.
Database opened.

Verify – 2 Controlfile as present now as per the Oracle recommended

sys@TESTD> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------------------------
control_files                        string      /u01/app/oracle/oradata/prim/control01.ctl, /u01/app/oracle/fast_recovery_area/prim/control02.ctl

Therefore Oracle will start updating both the control files & if 1 control file is lost you can copy  it from another location.

======Hence tested & verified in our test env========