Friday, January 25, 2019

How to do "Multiplexing of Control Files" through pfile (parameter file) in Oracle 11g

Step 1) First step is to check how many control files are in IN USE.

Example: -
SQL> select name from v$controlfile;


Note: - As from the above output we can see 2 control files are in IN USE. Now I will add one more control file in /u01/app/oracle/oradata/prod location.

Step 2) Second step is to edit pfile which is under ORACLE_HOME/dbs location.

Example: -

Note: - I have added third control file in pfile.

Step 3) Now we need to copy the content through 'cp' command to make the control files in sync.

Example: -
[oracle@prod dbs]$ cp /u01/app/oracle/oradata/prod/control01.ctl /u01/app/oracle/oradata/prod/control03.ctl
[oracle@prod dbs]$ cp /u01/app/oracle/fast_recovery_area/prod/control02.ctl /u01/app/oracle/oradata/prod/control03.ctl

Step 4) Now shut down the database through shut immediate command then start it again.

Example: -
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size     2257880 bytes
Variable Size   545262632 bytes
Database Buffers   289406976 bytes
Redo Buffers     2355200 bytes
Database mounted.
Database opened.

Note: - In case you will get an error during startup, then you need to shut down the database and repeat the copy step again to make control files in sync.

Step 5) Now check the control file status from below command.

Example: -
SQL> select name from v$controlfile;


Note: - Now we can see from the above output that our 3rd Control file has been added successfully.