Multiplex control files on ASM

The process of creating multiple copies of control files on oracle database is popularly called as control file multiplexing. The multiplexing controlfiles used to prevent loss of control files. If a control file is lost and the database instance crashes, it becomes impossible to bring up the database to a working state.  DBA’s need to protect control files from any type of loss, and to help with that, Oracle recommends multiplexing control files and locating them at different hard drive partitions.

What is a control file ?

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

•         The database name

•         Names and locations of associated datafiles and redo log files

•         The timestamp of the database creation

•         The current log sequence number

•         Checkpoint information

Advantages of multiplexing controlfile:

By storing multiple control files for a single database on different disks, you safeguard against a single point of failure. If a single disk containing a control files crashes, then the instance fails when Oracle attempts to access the damaged control file.

Note the following characteristics of multiplexed control files:

  • At least two filenames are listed for the initialization parameter CONTROL_FILES in the database’s parameter file.
  • The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle database server during database operation.
  • If any of the control files becomes unavailable during database operation, then the instance becomes inoperable and aborts. Copy a good control file to the bad control file’s location or, if media failure makes the disk inaccessible, copy a good control file to a new location and edit the initialization parameter file.

Procedure to multiplex your controlfile on ASM:

Connect to the database from any one of the RAC nodes and check the current control file status. Current control file is in the FILESYS disk group and I want to add another control file to the FILESYSFRA diskgroup for multiplexing/mirroring.

SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 21 21:51:54 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options


SQL> select name from v$controlfile;

NAME

———————————————————–

+FILESYS/prod/controlfile/current.482.736011333

Add the FILESYSFRA  disk group to the controlfile parameter by issuing the following command:


SQL> alter system set control_files='+FILESYS/prod/controlfile/current.482.736011333', '+FILESYSFRA' scope=spfile;

System altered.


SQL> commit;

Commit complete.

Shutdown the RAC database and start the database in nomount mode:

/home/oracle: srvctl stop database -d prod

/home/oracle: srvctl start database -d prod -o nomount

Connect to the DB and view the modified control_file parameter:


SQL> show parameter control_files

NAME                                 TYPE             VALUE

———————————————————————————————————————

control_files                        string           +FILESYS/prod/controlfile/current.482.736011333, +PRFRA

Connect to RMAN and issue the restore command to create a copy of the control file from the FRADG disk group. (The bold one is the new control file.)

RMAN> restore controlfile from ‘+FILESYS/prod/controlfile/current.482.736011333′;

Starting restore at 27-07-2013 11:27:30using target database control file instead of recovery catalogallocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 instance=PROD1 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+FILESYS/prod/controlfile/current.482.736011333

output file name=+FILESYSFRA/prod/controlfile/current.256.821878053

Finished restore at 27-07-2013 11:27:34

Connect to the database and issue alter command with the exact path of the file that was created by the restore of RMAN.


SQL> alter system set control_files='+FILESYS/prod/controlfile/current.482.736011333','+FILESYSFRA/prod/controlfile/current.256.821878053' scope=spfile;

System altered.

Now, shutdown the database and start up. Use the below commands:

/home/oracle: srvctl stop database -d prod

/home/oracle: srvctl start database -d prod

Check by connecting to the RAC database, you will see the two copies of control files in two different disk groups, FILESYS and FILESYSFRA.


SQL> show parameter control_files;

NAME                                 TYPE        VALUE

———————————————————————————————————–

control_files                        string      +FILESYS/prod/controlfile/current.482.736011333,

+PRFRA/prod/controlfile/current.256.821878053


SQL> select name from v$controlfile;

NAME

————————————————————–

+PRDATA/prod/controlfile/current.482.736011333

+PRFRA/prod/controlfile/current.256.821878053

 

Author: Swathi