In Oracle databases, it is recommended to multiplex you controlfile to safeguard against different failures like corruption, accidentally removing control file and so on.
In this article I will demonstrate how to multiplex/duplicating a controlfile into Automatic Storage Management (ASM).
Current Setup
Exadata 8-node RAC using ASM.
Current controlfile is stored in ASM.
Database is using SPFILE.
There are diffferent ASM Disk Groups available such as DATA, RECO, DBFS_DG, ACFS_DG.
dm01db01-orcldb1 {/home/oracle}:srvctl status database -d orcldb
Instance orcldb1 is running on node dm01db01
Instance orcldb2 is running on node dm01db02
Instance orcldb3 is running on node dm01db04
Instance orcldb4 is running on node dm01db05
Instance orcldb5 is running on node dm01db07
Instance orcldb6 is running on node dm01db06
Instance orcldb7 is running on node dm01db03
Instance orcldb8 is running on node dm01db08
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATA/ORCLDB/PARAMETERFILE/spfile.431.939367673
SQL> select name from v$controlfile;
NAME
——————————————————————————–
+DATA/ORCLDB/CONTROLFILE/current.384.939367517
dm01db01-+ASM1 {/home/oracle}:asmcmd lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL Y 512 512 4096 4194304 10092544 424 315392 -157484 1 N ACFS_DG/
MOUNTED NORMAL Y 512 512 4096 4194304 7208960 532 225280 -112374 1 N DATA/
MOUNTED HIGH N 512 512 4096 4194304 12390400 12012736 450560 3854058 0 N RECO/
MOUNTED NORMAL N 512 512 4096 4194304 2106432 2104640 30528 1037056 0 Y DBFS_DG/
Steps to multiplex controlfile in ASM When Database is using SPFILE
- Update the control_files to include the location for second control file. The second controlfile is going to be created on different diskgroup RECO.
SQL> alter system set control_files=’+DATA/ORCLDB/CONTROLFILE/current.384.939367517′,‘+RECO’ scope=spfile sid=’*’;
System altered.
- Stop and start the instance on node 1 in NOMOUNT state.
dm01db01-orcldb1 {/home/oracle}:srvctl status database -d orcldb
Instance orcldb1 is running on node dm01db01
Instance orcldb2 is running on node dm01db02
Instance orcldb3 is running on node dm01db04
Instance orcldb4 is running on node dm01db05
Instance orcldb5 is running on node dm01db07
Instance orcldb6 is running on node dm01db06
Instance orcldb7 is running on node dm01db03
Instance orcldb8 is running on node dm01db08
dm01db01-orcldb1 {/home/oracle}:srvctl stop database -d orcldb
dm01db01-orcldb1 {/home/oracle}:srvctl status database -d orcldb
Instance orcldb1 is not running on node dm01db01
Instance orcldb2 is not running on node dm01db02
Instance orcldb3 is not running on node dm01db04
Instance orcldb4 is not running on node dm01db05
Instance orcldb5 is not running on node dm01db07
Instance orcldb6 is not running on node dm01db06
Instance orcldb7 is not running on node dm01db03
Instance orcldb8 is not running on node dm01db08
dm01db01-orcldb1 {/home/oracle}:srvctl start instance -d orcldb -i orcldb1 -o nomount
SQL> set lines 200
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU
————— —————- —————————————————————- —————– ——— ———— — ———- ——- ————— ———- —
DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO CON_ID INSTANCE_MO EDITION FAMILY DATABASE_TYPE
—————– —————— ——— — ———- ———– ——- ——————————————————————————– —————
1 orcldb1 dm01db01 12.2.0.1.0 09-MAY-17 STARTED YES 0 STOPPED ALLOWED NO
ACTIVE UNKNOWN NORMAL NO 0 REGULAR EE RAC
- Connect to RMAN and duplicate the controlfile
dm01db01-orcldb1 {/home/oracle}:rman target /
Recovery Manager: Release 12.2.0.1.0 – Production on Tue May 9 05:07:45 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLDB (not mounted)
RMAN> restore controlfile from ‘+DATA/ORCLDB/CONTROLFILE/current.384.939367517’;
Starting restore at 09-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=372 instance=orcldb1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/ORCLDB/CONTROLFILE/current.384.939367517
output file name=+RECO/ORCLDB/CONTROLFILE/current.1003.943506471
Finished restore at 09-MAY-17
RMAN> exit
Recovery Manager complete.
- update the control_file parameter with the full path and name.
SQL> alter system set control_files=’+DATA/ORCLDB/CONTROLFILE/current.384.939367517′,’+RECO/ORCLDB/CONTROLFILE/current.1003.943506471′ scope=spfile sid=’*’;
System altered.
- Shutdown and start database
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
dm01db01-orcldb1 {/home/oracle}:srvctl start database -d orcldb
dm01db01-orcldb1 {/home/oracle}:srvctl status database -d orcldb
Instance orcldb1 is running on node dm01db01
Instance orcldb2 is running on node dm01db02
Instance orcldb3 is running on node dm01db04
Instance orcldb4 is running on node dm01db05
Instance orcldb5 is running on node dm01db07
Instance orcldb6 is running on node dm01db06
Instance orcldb7 is running on node dm01db03
Instance orcldb8 is running on node dm01db08
- verify that both controlfiles are in ASM now.
SQL> select name from v$controlfile;
NAME
——————————————————————————–
+DATA/ORCLDB/CONTROLFILE/current.384.939367517
+RECO/ORCLDB/CONTROLFILE/current.1003.943506471
SQL> show parameter control_files
NAME TYPE VALUE
———————————— ———– ——————————
control_files string +DATA1/ORCLDB/CONTROLFILE/current.384.939367517, +RECO/ORCLDB/CONTROLFILE/current.1003.943506471
Conclusion
In this article we have learned how to duplicate a control file in ASM. Multiplexing control file is recommended to safeguard against controlfil failures.