Uncategorized

Multiplex Oracle Database Controlfile in ASM

Introduction:
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.