Replicating the Oracle Controlfile

For safety it is good to replicate the controlfile for an Oracle DB. Our muoncal instance (after reinstalling in May 2009) only had one controlfile:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATAFLASH/muoncal/controlfile/current.256.686019077

To fix this we can use RMAN to create a new copy as follows:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2085320 bytes
Variable Size             838864440 bytes
Database Buffers         1291845632 bytes
Redo Buffers               14688256 bytes
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
muoncal on umors.grid.umich.edu: rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Tue May 5 16:09:09 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN> connect target

connected to target database: muoncal (not mounted)
using target database control file instead of recovery catalog

RMAN> restore controlfile to '+DATAFLASH' from '+DATAFLASH/muoncal/controlfile/current.256.686019077';

Starting restore at 05-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 05-MAY-09

RMAN> quit


Recovery Manager complete.
muoncal on umors.grid.umich.edu: asmcmd
asmcmd: command disallowed by current instance type
muoncal on umors.grid.umich.edu: export ORACLE_SID=+ASM
+ASM on umors.grid.umich.edu: asmcmd
ASMCMD> find -t CONTROLFILE +DATAFLASH *
+DATAFLASH/MUONCAL/CONTROLFILE/current.256.686019077
+DATAFLASH/MUONCAL/CONTROLFILE/current.303.686074195

Now we need to update the init file with the additional control file:

*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=5
*.control_files='+DATAFLASH/muoncal/controlfile/current.256.686019077','+DATAFL\
ASH/MUONCAL/CONTROLFILE/current.303.686074195'
*.core_dump_dest='/opt/app/oracle/admin/muoncal/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATAFLASH'
*.db_domain='grid.umich.edu'
*.db_file_multiblock_read_count=16
*.db_flashback_retention_target=4320
*.db_name='muoncal'
*.db_recovery_file_dest='/opt/app/oracle/flash_rec_area'
*.db_recovery_file_dest_size=128849018880
*.dispatchers='(PROTOCOL=TCP) (SERVICE=muoncalXDB)'
*.fast_start_mttr_target=30
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=+DATAFLASH/muoncal/ OPTIONAL REOPEN=300'
*.log_archive_dest_2=''
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
--u-:---F1  initmuoncal.ora      (Fundamental)--L14--25%------------------------
+ASM on umors.grid.umich.edu:
+ASM on umors.grid.umich.edu: export  ORACLE_SID=muoncal
muoncal on umors.grid.umich.edu: sqlplus sys/xxxxxxx as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 5 16:12:50 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2085320 bytes
Variable Size             838864440 bytes
Database Buffers         1291845632 bytes
Redo Buffers               14688256 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATAFLASH/muoncal/controlfile/current.256.686019077
+DATAFLASH/muoncal/controlfile/current.303.686074195

Done! -- ShawnMcKee - 05 May 2009