Introduction
In Oracle database management, ensuring the integrity and availability of control files is crucial. Multiplexing of control files is a vital step in safeguarding against potential failures. This process involves creating multiple copies of control files and storing them in separate locations, reducing the risk of data loss due to a control file failure. In this blog post, we’ll guide you through the steps for multiplexing control files in your Oracle database.
Multiplexing of Control Files methods:
- Using SPFILE
- a) Add New control file location in Database
- b) Shutdown the Database
- c) Copy the control file to desired location
- d) Start the DB and validate new control file location
- Using PFILE
- a) Create Pfile from Spfile & Shutdown the Database
- b) Copy the control file to desired location
- c) Put new control file location in control_files=” parameter in Pfile
- d) Start the Database with Pfile
- e) Create Spfile from Pfile, bounce the Database & validate
1. Using SPFILE
a) Add New control file location in Database
##Check Current control file status and location
QL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/control01.ctl
/u01/app/oracle/fast_recovery_area/PROD/control02.ctl
##Add new control file location in DB i.e /u02/control_file/control03.ctl
SQL> alter system set control_files='/u01/app/oracle/oradata/PROD/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD/control02.ctl','/u02/control_file/control03.ctl' scope=spfile;
System altered.b) Shutdown the Database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.c) Copy the control file to desired location
##Copy the control file to different or same location with different name
[oracle@prod ~]$ cp /u01/app/oracle/oradata/PROD/control01.ctl /u02/control_file/control03.ctl
[oracle@prod ~]$ ls -ltr /u02/control_file/
total 10352
-rw-r-----. 1 oracle oinstall 10600448 Oct 11 13:00 control03.ctl
d) Start the Database and validate new control file location
## Start the Database
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
##Validate new control file Location
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/control01.ctl
/u01/app/oracle/fast_recovery_area/PROD/control02.ctl
/u02/control_file/control03.ctl2. Using PFILE
a) Create Pfile from Spfile & Shutdown the Database
##Create pfile from spfile to any location by default it is created in $ORACLE_HOME/dbs
SQL> create pfile from spfile;
File created.
##OR##
SQL> create pfile='/u02/initPROD.ora' from spfile;
File created.
##Validate File Created
[oracle@prod ~]$ ls -ltr /u02
total 4
drwxrwxr-x. 2 oracle oinstall 6 Oct 11 14:28 control_file
-rw-r--r--. 1 oracle oinstall 1148 Oct 11 14:42 initPROD.ora
##Shutdown the Database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
b) Copy the control file to desired location
##Copy the control file to different or same location with different name
[oracle@prod ~]$ cp /u01/app/oracle/oradata/PROD/control01.ctl /u02/control_file/control03.ctl
[oracle@prod ~]$ ls -ltr /u02/control_file/
total 10352
-rw-r-----. 1 oracle oinstall 10600448 Oct 11 14:48 control03.ctl
c) Put new control file location in control_files=” parameter in Pfile
##Add new control file location in control_files='' parameter and save it
*.audit_file_dest='/u01/app/oracle/admin/PROD/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/PROD/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD/control02.ctl','/u02/control_file/control03.ctl'
*.db_block_size=8192
*.db_name='PROD'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
d) Start the Database with Pfile
##Start the database with pfile
SQL> startup pfile='/u02/initPROD.ora';
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
##Confirm database started with pfile
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
e) Create Spfile from Pfile, bounce the Database & validate
#Create spfile from pfile so the changes reflects in spfile too
SQL> create spfile from pfile;
File created.
##Bounce the database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2415917880 bytes
Fixed Size 8899384 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
##Database by default started with spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19c/db
_1/dbs/spfilePROD.ora
#Validate new control file
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/control01.ctl
/u01/app/oracle/fast_recovery_area/PROD/control02.ctl
/u02/control_file/control03.ctlConclusion
By following these steps, you’ve successfully multiplexed your control files, enhancing the reliability and resilience of your Oracle database. Remember to regularly back up your control files and test your backup and recovery procedures to ensure a robust database management strategy.
