Creating a spfile from memory and starting the DB with the new spfile

Currently we had a situation where the database was showing started with spfile but the spfile physically was not there present at ASM location

i.e. show parameter pfile --- shows DB started with spfile but the same ASM file doesn't exist at its location.


SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/devnwwfw/spfiledevnwwfw.ora                                        



Current configuration of database

[oracle@subdb02 ~]$ srvctl config database -d DEVNWWFW
Database unique name: DEVNWWFW
Database name: DEVNWWFW
Oracle home: /oracle/app/database/11.2.0.3/dbhome_1
Oracle user: oracle
Spfile: +DATA/DEVNWWFW/spfileDEVNWWFW.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: KXD2BUPOOL
Database instances:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC

Database is policy managed

No SPFILE found

ASMCMD> cd +DATA/DEVNWWFW
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD> pwd
+DATA/DEVNWWFW
ASMCMD> exit



This was a part of 4 node cluster and when one node crashed and it denied to come up then this issue was noticed

Solution:

Step1(create a pfile from memory):

From a different node(instance) which is up please create a pfile;


SQL> create pfile='/tmp/DEVNWWFW.ora' from spfile='+DATA/devnwwfw/spfiledevnwwfw.ora';
create pfile='/tmp/DEVNWWFW.ora' from spfile='+DATA/devnwwfw/spfiledevnwwfw.ora'
*
ERROR at line 1:
ORA-01565: error in identifying file '+DATA/devnwwfw/spfiledevnwwfw.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/devnwwfw/spfiledevnwwfw.ora
ORA-15173: entry 'spfiledevnwwfw.ora' does not exist in directory 'devnwwfw'


SQL> create pfile='/tmp/DEVNWWFW.ora' from memory;

File created.

Step 2(create a new spfile with different name):

Create a new spfile with different name as it is not allowing us to create with same one as per the error above


[oracle@subdb02 ~]$ echo $ORACLE_SID
DEVNWWFW_2
[oracle@subdb02 ~]$ srvctl stop database -d DEVNWWFW -o immediate
[oracle@subdb02 ~]$ echo $ORACLE_SID
DEVNWWFW_2
[oracle@subdb02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 31 18:12:03 2012

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

Connected to an idle instance.

SQL> create spfile='+DATA/devnwwfw/spfiledevnwwfw1.ora' from pfile='/tmp/DEVNWWFW_new.ora';

File created.



Step3( using srvctl modify the database to start with new spfile):


[oracle@subdb02 ~]$ srvctl modify database -d DEVNWWFW -p +DATA/devnwwfw/spfiledevnwwfw1.ora
[oracle@subdb02 ~]$  srvctl config database -d DEVNWWFW
Database unique name: DEVNWWFW
Database name: DEVNWWFW
Oracle home: /oracle/app/database/11.2.0.3/dbhome_1
Oracle user: oracle
Spfile: +DATA/devnwwfw/spfiledevnwwfw1.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: KXD2BUPOOL
Database instances:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Database is policy managed

Step4(start the database with new spfile):


[oracle@subdb02 ~]$ srvctl start database -d DEVNWWFW
[oracle@subdb02 ~]$  srvctl status database -d DEVNWWFW
Instance DEVNWWFW_1 is running on node subdb01
Instance DEVNWWFW_2 is running on node subdb02
Instance DEVNWWFW_3 is running on node subdb03

step5(check if it started with new spfile and the ASM location also):


SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/devnwwfw/spfiledevnwwfw1.ora                                          


ASMCMD> cd +DATA/devnwwfw/
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
spfiledevnwwfw1.ora