Replacing spfile in ASM

In a database that is residing in a file system it is easy to replace the spfile with

create spfile from pfile

when the database is started with a pfile. But note the following when replacing a spfile that resides in ASM.

When the DB is created the spfile inside ASM is as below. spfile.260.714060565 is the exact spfile and spfilerac11g.ora is alias refering it

ASMCMD> ls -l
Type  Redund  Striped  Time   Sys  Name
                               Y    ARCHIVELOG/
                               Y    CONTROLFILE/
                               Y    DATAFILE/
                               Y    ONLINELOG/
                               Y    PARAMETERFILE/
                               Y    TEMPFILE/
                               N    spfilerac11g.ora => +DATA/RAC11G/PARAMETERFILE/spfile.260.714060565 

Now create a pfile and start the DB using it

SQL> startup nomount pfile=pfile.ora
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2162280 bytes
Variable Size             385876376 bytes
Database Buffers          230686720 bytes
Redo Buffers                7602176 bytes

Create the spfile with the usual command try to start the DB

SQL> create spfile='+DATA' from pfile;

File created.

SQL> startup force;

The startup command will not return. Looking inside the ASM you will see the following

ASMCMD> ls -l
Type  Redund  Striped  Time   Sys  Name
                               Y    ARCHIVELOG/
                               Y    CONTROLFILE/
                               Y    DATAFILE/
                               Y    ONLINELOG/
                               Y    PARAMETERFILE/
                               Y    TEMPFILE/
                               N    spfilerac11g.ora => +DATA/RAC11G/PARAMETERFILE/spfile.260.714060565
ASMCMD> ls -l parameterfile
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   APR 28 23:00:00  Y    spfile.260.714060565
PARAMETERFILE  UNPROT  COARSE   APR 29 00:00:00  Y    spfile.393.717552031 

The alias is still refering the old spfile. Rectifiy this as follows

ASMCMD> rmalias spfilerac11g.ora
ASMCMD> mkalias +DATA/RAC11G/PARAMETERFILE/spfile.393.717552031 spfilerac11g.ora
ASMCMD> ls -l
Type  Redund  Striped  Time   Sys  Name
                               Y    ARCHIVELOG/
                               Y    CONTROLFILE/
                               Y    DATAFILE/
                               Y    ONLINELOG/
                               Y    PARAMETERFILE/
                               Y    TEMPFILE/
                               N    spfilerac11g.ora => +DATA/RAC11G/PARAMETERFILE/spfile.393.717552031

Try starting the DB and it would still hang. Problem lies how the spfile was created. When creating the spfile specify the full path of the creating pfile

create spfile='+DATA' from pfile='/home/oracle/asanga/pfile.ora';

Replace alias and start the DB

ASMCMD> ls parameterfile
spfile.260.714060565
spfile.393.717552031
spfile.394.717553085
ASMCMD> rmalias spfilerac11g.ora
ASMCMD> mkalias +DATA/RAC11G/PARAMETERFILE/spfile.394.717553085 spfilerac11g.ora
ASMCMD> ls -l
Type  Redund  Striped  Time   Sys  Name
                               Y    ARCHIVELOG/
                               Y    CONTROLFILE/
                               Y    DATAFILE/
                               Y    ONLINELOG/
                               Y    PARAMETERFILE/
                               Y    TEMPFILE/
                               N    spfilerac11g.ora => +DATA/RAC11G/PARAMETERFILE/spfile.394.717553085

SQL> startup force;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2162280 bytes
Variable Size             385876376 bytes
Database Buffers          230686720 bytes
Redo Buffers                7602176 bytes
Database mounted.
Database opened. 

Script to get full name of the resources from crs_stat

More on metalink note 259301.1

#!/bin/sh
#
# Sample 10g CRS resource status query script
#
# Description:
# - Returns formatted version of crs_stat -t, in tabular
# format, with the complete rsc names and filtering keywords
# - The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# - $ORA_CRS_HOME should be set in your environment

RSC_KEY=$1
QSTAT=-u
AWK=/bin/awk # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$CRS_HOME/bin/crs_stat $QSTAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

For 11.2 replace $CRS_HOME/bin/crs_stat QSTAT with $CRS_HOME/bin/crsctl stat res
Script For Listing 11gR2 Cluster Resource Status in CRS_STAT Output Format

Block device permissions on 11g Clusterware

According to 11gR1 clusterware installation guide, the way to preserve the permissions of block devices used for OCR and Vote disk is to add file to /etc/udev/permissions.d directory

But this does not work for RHEL 5 as there's no permission.d folder. On RHEL5 have to create a file in /etc/udev/rules.d/99-raw.rules or /etc/udev/rules.d/99-sdb.rules (if the block device used for ocr and vote disk is a partion of sdb) with the following content

KERNEL=="raw[1-2]*", GROUP="oinstall", MODE="640"
KERNEL=="raw[3-5]*", OWNER="oracle", GROUP="oinstall", MODE="660"

Or

#OCR
KERNEL=="sdb[1]*", GROUP="oinstall", MODE="640"
#VOTE and ASM spfile
KERNEL=="sdb[2-3]*", OWNER="oracle", GROUP="oinstall", MODE="660"
#ASM
KERNEL=="sdb[5-6]*", OWNER="oracle", GROUP="dba", MODE="660"

Read more from here

Useful metalink note
How to Setup UDEV Rules for RAC OCR & Voting devices on SLES10, RHEL5, OEL5 [ID 414897.1]
Configuring raw devices (singlepath) for Oracle Clusterware 10g Release 2 (10.2.0) on RHEL5/OEL5 [ID 465001.1]
Configuring raw devices (multipath) for Oracle Clusterware 10g Release 2 (10.2.0) on RHEL5/OEL5 [ID 564580.1]