Mir Sayeed Hassan – Oracle Blog

How to Multiplex Control file on RAC DB or ASM in Oracle 11gR2 (11.2.0.4.0)

In RAC Env, If you have newly configure & created the database as 2 node RAC by default there will be single controlfile will be created, But as per the Oracle recommend there should be atleast 2 controlfile to be created.

Please find the step by step procedure to add the multiplex controlfile into the New +ASM location.

Note:

My default location of the Controlfile was ORL, Now am going to multiplex the controlfile into another location as “ORADATA” & Taken the existing controlfile backup for safer side.

Backup of Current Controlfile by using ASM

Login to the ASM & Take a backup

[oracle@swdbn1 ~]$ . oraenv
ORACLE_SID = [swdb1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

ASMCMD> cd +ORL/swdb/CONTROLFILE

ASMCMD> ls
Current.256.1033332085

ASMCMD> pwd
+ORL/swdb/CONTROLFILE

ASMCMD> cp Current.256.1033332085 /tmp
copying +ORL/swdb/CONTROLFILE/Current.256.1033332085 > /tmp/Current.256.1033332085

OR

Backup of Current Controlfile by using RMAN

RMAN> backup current controlfile format '/tmp/current_controlfile250220.ctl';

Starting backup at 25-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 25-FEB-20
channel ORA_DISK_1: finished piece 1 at 25-FEB-20
piece handle=/tmp/current_controlfile250220.ctl tag=TAG20200225T214525 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-FEB-20
Starting Control File and SPFILE Autobackup at 25-FEB-20
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/c-507355125-20200225-01 comment=NONE
Finished Control File and SPFILE Autobackup at 25-FEB-20

Find the current controlfile location

[oracle@swdbn1 ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 25 21:46:09 2020
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter control_file

NAME                                                     TYPE                 VALUE
------------------------------------ ----------- ------------------------------------------------------------------------
control_file_record_keep_time                           integer                7
control_files                                           string      +ORL/swdb/controlfile/current.256.1033332085

Modify the new current controlfile parameter by adding “ORADATA” into it

SQL> alter system set control_files='+ORL/swdb/controlfile/current.256.1033332085','+ORADATA' scope=spfile;
System altered.

Check the status of DB

[oracle@swdbn1 ~]$ srvctl status database -d swdb
Instance swdb1 is running on node swdbn1
Instance swdb2 is running on node swdbn2

Stop the DB

[oracle@swdbn1 ~]$ srvctl stop database -d swdb

Start the DB in the nomount mode

[oracle@swdbn1 ~]$ srvctl start database -d swdb -o nomount

[oracle@swdbn1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Feb 25 21:55:28 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SWDB (not mounted)

RMAN> restore controlfile from '+ORL/swdb/controlfile/current.256.1033332085';

Starting restore at 25-FEB-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1569 instance=swdb1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+ORL/swdb/controlfile/current.256.1033332085
output file name=+ORADATA/swdb/controlfile/current.264.1033336613
Finished restore at 25-FEB-20

RMAN> exit
Recovery Manager complete.

Check the control_file parameter:

SQL> show parameter control_file
NAME                                              TYPE             VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time                    integer          7
control_files                               `    string           +ORL/swdb/controlfile/current.256.1033332085, +ORADATA/swdb/controlfile/current.264.1033336613

Stop & Start the DB

[oracle@swdbn1 ~]$ srvctl stop database -d swdb

[oracle@swdbn1 ~]$ srvctl start database -d swdb

Verify the DB & Modified Controlfile

[oracle@swdbn1 ~]$ srvctl status database -d swdb

Instance swdb1 is running on node swdbn1
Instance swdb2 is running on node swdbn2

Verify Modified Controlfile by Sqlplus

[oracle@swdbn1 ~]$ !sq
sqlplus / as sysdba

SQL> select name from V$controlfile;

NAME
-------------------------------------------------
+ORL/swdb/controlfile/current.256.1033332085
+ORADATA/swdb/controlfile/current.264.1033336613

SQL> show parameter control_file

NAME                                             TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time                   integer         7
control_files                                   string         +ORL/swdb/controlfile/current.256.1033332085, +ORADATA/swdb/controlfile/current.264.1033336613

Verify by ASM Location

[oracle@swdbn1 ~]$ . oraenv
ORACLE_SID = [swdb1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@swdbn1 ~]$ asmcmd
ASMCMD> cd +oradata/swdb/controlfile

ASMCMD> ls
current.264.1033336613

===== Hence tested & verified in our test env =====