Thursday, September 3, 2009

How to Multiplex Control File using ASM and RAC

Shutdown all other instances

Follow these steps using one instance


$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 2 17:50:15 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, Real Application Clusters, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------------
+FRA/ctrk027/controlfile/current.258.695839303

SQL> alter system set control_files = ‘+FRA/ctrk027/controlfile/current.258.695839303’,’+TRACKING_DATA’,’+TRACKING_REDOCTL1’ scope=spfile sid=’*’;

System altered.

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

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size 1995856 bytes
Variable Size 536873904 bytes
Database Buffers 1996488704 bytes
Redo Buffers 14778368 bytes

SQL> exit

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

$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 2 17:48:42 2009

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

connected to target database: ctrk027q (not mounted)

RMAN> restore controlfile from '+FRA/ctrk027/controlfile/current.258.695839303';

Starting restore at 02-SEP-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 instance=ctrk027q2 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+FRA/ctrk027/controlfile/current.258.695839303
output filename=+TRACKING_DATA/ctrk027q/controlfile/current.370.696534557
output filename=+TRACKING_REDOCTL1/ctrk027q/controlfile/current.300.696534559
Finished restore at 02-SEP-09

RMAN> exit


Recovery Manager complete.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 13 15:29:24 2008

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


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

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system set control_files='+FRA/ctrk027/controlfile/current.258.695839303’,’ +TRACKING_DATA/ctrk027q/controlfile/current.370.696534557’,’ +TRACKING_REDOCTL1/ctrk027q/controlfile/current.300.696534559’ scope=spfile sid=’*’;

System altered.

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

Total System Global Area 2550136832 bytes
Fixed Size 1995856 bytes
Variable Size 536873904 bytes
Database Buffers 1996488704 bytes
Redo Buffers 14778368 bytes
Database mounted.
Database opened.
SQL>
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------
+FRA/ctrk027/controlfile/current.258.695839303
+TRACKING_DATA/ctrk027q/controlfile/current.370.696534557
+TRACKING_REDOCTL1/ctrk027q/controlfile/current.300.696534559
SQL>