Multiplexing in control_file is one of the best practices of oracle database setup. It means keeping control files in different mount point or disk groups , so that in case one mount points is inaccessible, control file can be accessed from the available mount point.
NOTE – > This activity needs downtime.
FOR MULTIPLEXING IN RAC – > STEPS FOR MULTIPLEXING IN ORACLE RAC
In this below DEMO, we will explain the steps for multiplexing control files.
DEMO:
1. Check current control files:
SQL> select name from gv$controlfile
2 ;
NAME
--------------------------------------------------------------------------------
/dmdata02/oradata/BSDMSIT2/control01.ctl
/dmdata02/oradata/BSDMSIT2/control02.ctl
/dmdata07/oradata/control03.ctl
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /dmdata02/oradata/BSDMSIT2/con
trol01.ctl, /dmdata02/oradata/
BSDMSIT2/control02.ctl
Create a directory in the new mount point, where we will keep the new control file.
mkdir -p /dmdata07/oradata
2. update the control_files parameter by adding the new path also
SQL> alter system set control_files='/dmdata02/oradata/BSDMSIT2/control01.ctl','/dmdata02/oradata/BSDMSIT2/control02.ctl','/dmdata07/oradata/control03.ctl' scope=spfile; System altered.
3. Shutdown the database and start with nomount:
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 8388608000 bytes Fixed Size 5303664 bytes Variable Size 3623879312 bytes Database Buffers 4747952128 bytes Redo Buffers 11472896 bytes SQL> exit
4. restore the controlfile from existing control file .
$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 24 08:50:19 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: BSDMSIT2 (not mounted) RMAN> restore controlfile from '/dmdata02/oradata/BSDMSIT2/control01.ctl'; Starting restore at 24-FEB-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1058 device type=DISK channel ORA_DISK_1: copied control file copy output file name=/dmdata02/oradata/BSDMSIT2/control01.ctl output file name=/dmdata02/oradata/BSDMSIT2/control02.ctl output file name=/dmdata07/oradata/control03.ctl Finished restore at 24-FEB-20 RMAN> exit
5. Restart the database:
SQL> shutdown immediate; startup ORA-01507: database not mounted ORACLE instance shut down. SQL> ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 8388608000 bytes Fixed Size 5303664 bytes Variable Size 3623879312 bytes Database Buffers 4747952128 bytes Redo Buffers 11472896 bytes Database mounted. Database opened.
6. Check whether new control file is available or not :
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /dmdata02/oradata/BSDMSIT2/con
trol01.ctl, /dmdata02/oradata/
BSDMSIT2/control02.ctl, /dmdat
a07/oradata/control03.ctl
NUMBER
SQL> select name from gv$controlfile;
NAME
--------------------------------------------------------------------------------
/dmdata02/oradata/BSDMSIT2/control01.ctl
/dmdata02/oradata/BSDMSIT2/control02.ctl
/dmdata07/oradata/control03.ctl
We can observe controlfile is available now on all 3 locations.
SELECT inst_id,DECODE(request,0,’Holder: ‘,’—> Waiter: ‘)||sid session_ID,
id1, id2, lmode, request, type,ctime/60 mts
FROM gV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request
/