ORACLE DATABASE 19C – HOW TO ADD / MULTIPLEX CONTROL FILES

ANKUSH THAVALI
21 Nov, 2021
0 Comments
2 Mins Read

ORACLE DATABASE 19C – HOW TO ADD / MULTIPLEX CONTROL FILES

PURPOSE:

– protect your Database in case of a disk failure
– reduce the risk of control file loss due to corruption or accidental removal
– as a DBA, it’s your responsibility to multiplex and backup control files to protect your organization in case of possible data loss due to media failure or control file corruption

ENVIRONMENT:

– Single instance database (we will cover ASM and RAC environment on future post)
– Database Name: testdb01
– Operating System: Oracle Linux 7.2

STEPS:

1. Connect to your Database

2. Make a list of all existing Control Files

SQL> SELECT name FROM v$controlfile;
NAME
———————————————————
/u01/app/oracle/oradata/testdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/testdb01/control02.ctl
/u02/oradata/testdb01/control03.ctl

SQL> show parameter control_files
NAME TYPE VALUE
————– ——- ———————————————–
control_files string /u01/app/oracle/oradata/testdb01/control01.ctl,
/u01/app/oracle/fast_recovery_area/testdb01/control02.ctl,
/u02/oradata/testdb01/control03.ctl

3. Update the CONTROL_FILES parameter

– add the new location and control file name using ALTER SYSTEM SET CONTROL_FILES command
– set the SCOPE to SPFILE and not BOTH or MEMORY to prevent the database from attempting to look for the new file at this time
– you may want to create a pfile from spfile before doing this step so you have a backup of your initialization parameters

SQL> ALTER SYSTEM SET CONTROL_FILES='/data/app/oracle/oradata/PRIM/control01.ctl','/data/app/oracle/oradata/PRIM/control02.ctl','/data/app/oracle/oradata/PRIM/control03.ctl' SCOPE=spfile;
System altered.

4. Shutdown your Database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

5. Copy one of the existing control files to the new location

[oracle@ol7]$ cp /u02/oradata/testdb01/control03.ctl /u03/oradata/testdb01/control04.ctl

6. Start your Database

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 520097408 bytes
Database Buffers 1073741824 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.

7. Check your new list of Control Files

SQL> SELECT name FROM v$controlfile;
NAME
———————————————————
/u01/app/oracle/oradata/testdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/testdb01/control02.ctl
/u02/oradata/testdb01/control03.ctl
/u03/oradata/testdb01/control04.ctl

SQL> show parameter control_files
NAME TYPE VALUE
————– ——- ———————————————–
control_files string /u01/app/oracle/oradata/testdb01/control01.ctl,
/u01/app/oracle/fast_recovery_area/testdb01/control02.ctl,
/u02/oradata/testdb01/control03.ctl,
/u03/oradata/testdb01/control04.ctl

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

ORACLE DATABASE 19C – HOW TO ADD / MULTIPLEX CONTROL FILES