- 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
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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.ctlSQL> show parameter control_filesNAME 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
1 2 | 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
1 2 3 4 | SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down. |
5. Copy one of the existing control files to the new location
1 | [oracle@ol7]$ cp /u02/oradata/testdb01/control03.ctl /u03/oradata/testdb01/control04.ctl |
6. Start your Database
1 2 3 4 5 6 7 8 9 | SQL> startupORACLE instance started.Total System Global Area 1610612736 bytesFixed Size 2924928 bytesVariable Size 520097408 bytesDatabase Buffers 1073741824 bytesRedo Buffers 13848576 bytesDatabase mounted.Database opened. |
7. Check your new list of Control Files
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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.ctlSQL> show parameter control_filesNAME 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