Sunday 4 December 2022

How to Multiplex Control Files in Oracle Database

 How to Multiplex Control Files in Oracle Database?


After this article, you will have the understanding of,
  • How to add one or more control files in oracle database? 
  • Why do we require control file multiplexing? 
  • Why is it important to have multiple copies of control files in your oracle base?

Control files serve a very important role and are very crucial for database working because of the information stored inside them. You can't take the risk of losing your control file especially when the database is running. When database is properly shutdown, even if you lose your control file, you can recreate it without much trouble. But the general idea is to secure the control file but taking regular backups and also multiplexing the same. By multiplexing, we simply mean to keep multiple copies of the same file in different locations.


Importance of Control File Multiplexing in Oracle Database

The information inside a control file is so critical that if you lose a control file in the running database, database would immediately crash and stop working. This file is not only required at the time of DB startup actions but is continuously updated along with the changes inside a running database.

Hence it is important to make provisions to have less impact or to secure at least 1 copy of the control file which can help in such scenarios.


How to Multiplex a Control File?

Multiplexing a Control File in Oracle database is quite simple and mandatory. Follow the below mentioned steps if you want to perform the same:

By default, there are 2 control files created when you create a fresh database. You can confirm that by running the below command,

SQL> show parameter control_file


NAME      TYPE VALUE

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

control_file_record_keep_time      integer 7

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

ontrol01.ctl, /u01/app/oracle/

oradata/CDB1/control02.ctl

Create a pfile where you can add the new controlfiles,

SQL> create pfile from spfile;

File created.

Now shutdown your database,

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

Create copies of exiting control file and place them in your desired locations,

[oracle@racnode1 CDB1]$ cp control02.ctl /u01/app/oracle/fra/control03.ctl

[oracle@racnode1 CDB1]$ cp control02.ctl /u01/app/oracle/oradata/control04.ctl

Edit your pfile and add the new locations of control files,

[oracle@racnode1 dbs]$ vi initcdb1.ora


*.control_files='/u01/app/oracle/oradata/CDB1/control01.ctl','/u01/app/oracle/oradata/CDB1/control02.ctl','/u01/app/oracle/fra/control03.ctl','/u01/app/oracle/oradata/control04.ctl'

Login to Database as SYS and create SPFILE again from PFILE first and then start the database.

[oracle@racnode1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 4 17:08:28 2022

Version 19.3.0.0.0

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

Connected to an idle instance.


SQL> create spfile from pfile;

File created.


SQL> startup

ORACLE instance started.


Total System Global Area 1577055360 bytes

Fixed Size     9135232 bytes

Variable Size   385875968 bytes

Database Buffers 1174405120 bytes

Redo Buffers     7639040 bytes

Database mounted.

Database opened.

SQL> 

SQL> show parameter control_file


NAME      TYPE VALUE

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

control_file_record_keep_time      integer 7

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

ontrol01.ctl, /u01/app/oracle/

oradata/CDB1/control02.ctl, /u

01/app/oracle/oradata/fra/con

trol03.ctl, /u01/app/oracle/or

adata/control04.ctl

Now you can see the database is configured with 4 control files so the multiplexing is successful.


I hope this helps !!

No comments:

Post a Comment