Multiplexing Controlfile on ASM

Many a times, we come across databases running on a single controlfile. Running a database with just one controlfile is risky. So, be safe, we will multiplex, ideally on different diskgroup.

Get the controlfile path. We need it later.

SQL> select status from v$instance;

SQL> select name from v$controlfile;

To multiplex, we first set the CONTROL_FILES parameter with the current controlfile and the diskgroup where the multiplexed controfile should reside :

SQL> alter system set control_files = '+DATA/orcl/controlfile/current.268.741615511','+FLASH' scope = spfile;

Setting SCOPE to SPFILE will not set the parameter CONTROL_FILES in currently running instance, but only when the instance is restarted. Also, we cannot name files in ASM as names are system generated. So, multiplexing is achieved by restoring controlfile with the help of RMAN from an existing controlfile to the specified disk group in ALTER SYSTEM. Here the specified disk group is +FLASH.

Shut down the database instance.

SQL> shutdown immediate

IMPORTANT : You should also shut down OEM 12c agent if it is running in your machine. Otherwise you could see alert.log pointing to trace files with memory dump. See below what I got.

Set the environment

$ . oraenv

Connect to RMAN and restore controlfile

$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from '+DATA/orcl/controlfile/current.268.741615511';

Starting restore at 13-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.268.741615511
output file name=+FLASH/orcl/controlfile/current.260.741615511
Finished restore at 13-JUN-14

RMAN> exit

We are done with restoring controfile

Connect to SQL*Plus to mount and open the database

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> alter database mount;
SQL> alter database open;
SQL> SELECT name FROM v$controlfile;