How to Add Control File when Using ASM

Background & Overview

This document provides a step by step guide to add extra control files to a database whose storage is on ASM. In this case the database has one control file on diskgroup +DATA01 and the procedure explains how to add an additional control file located on diskgroup +FRA01

This has not been tested on databases prior to version 11g

Step-By-Step Guide

1. Take a backup of your spfile

  • sqlplus '/ as sysdba'
  • SQL> create pfile='/tmp/spfileBACKUP.ora' from spfile;

2. Update the control_file parameter in the spfile.

  • sqlplus '/ as sysdba'
  • SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA01/MYDB/controlfile/current.200.899418013

  • SQL> alter system set control_files='+DATA01/MYDB/controlfile/current.200.899418013','+FRA01' scope=spfile sid='*';

3. Restart the database in nomount mode

  • sqlplus '/ as sysdba'
  • SQL> shutdown immediate;
  • SQL> startup nomount;

4. Create the new control file using RMAN.

  • rman
  • RMAN> connect target /
  • RMAN> restore controlfile from '+DATA1/MYDB/controlfile/current.200.899418013';

Starting restore at 09-NOV-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=111 instance=MYDB devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA1/MYDB/controlfile/current.200.899418013
output filename=+FRA01/MYDB/controlfile/current.200.800418013
Finished restore at 09-NOV-12

5. Update the spfile with the new control file name

  • sqlplus '/ as sysdba'
  • SQL> alter system set control_files='+DATA01/MYDB/controlfile/current.200.899418013','+FRA01/MYDB/controlfile/current.200.800418013' scope=spfile sid='*';

6. Restart the database and check the new control file

  • sqlplus '/ as sysdba'
  • SQL> shutdown immediate;
  • SQL> startup;
  • SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA01/MYDB/controlfile/current.200.899418013
+FRA01/MYDB/controlfile/current.200.800418013

7. Remove the backup spfile

  • rm /tmp/spfileBACKUP.ora
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License