Home / 12c / Oracle Database 12c – How to Add / Multiplex Control Files
7cf484ceeae6bc7fc2161eddc603cd15.jpeg

Oracle Database 12c – 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
2 SET CONTROL_FILES=’/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’
3 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

Check Also

a2b042fe070145288fbcd8df3f1198ed.png

Oracle Database 12c Release 1 (12.1) Installation on Oracle Linux 7 (OL7.2)

Table of Contents ▼▲ Note from Oracle Database 12c R1 Installation Guide: Starting with Oracle …

Oracle 12c: Create a Container Database (CDB) manually with CREATE DATABASE script

Note: Oracle strongly recommends using the Database Configuration Assistant (DBCA) instead of the CREATE DATABASE …

Leave a Reply