- 21 Nov, 2021
- 0 Comments
- 2 Mins Read
ORACLE DATABASE 19C – HOW TO ADD / MULTIPLEX CONTROL FILES
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