How to Fix a Broken ASM SPFILE, held within ASM

My server rebooted itself and when it came back up, none of the databases or ASM had restarted. Everything is 11.2.0.3 or 11.2.0.1 with ASM being 11.2.0.3 – so Oracle Restart should have kicked in.

srvctl config asm -a | grep -i spfile

[oracle@rac2 trace]$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Sat Sep 15 23:11:45 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> 

/u01/app/oracle/diag/asm/+asm/+ASM2/trace

$srvctl start asm
PRCR-1079 : Failed to start resource ora.asm
CRS-5017: The resource action "ora.asm start" encountered the following error:
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'myserver.mydomain.net:1899'

We have a problem in the spfile that needs to be fixed. Where is it located so that it can be converted to a pfile and corrected? The usual place to check is $ORACLE_HOME/dbs.

$cd $ORACLE_HOME/dbs
$ls spfile*
spfile* not found
$srvctl config asm -a | grep -i spfile
Spfile: +DATA/asm/asmparameterfile/registry.123.123456789

The spfile name may also be listed in the alert.log as part of a startup. It is for me in this case:


$grep "^Using.*spfile" alert_+ASM.log | tail -1
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.123.123456789



There are numerous blog postings on the internet that explain how to start ASM, or extract the spfile, when the spfile it needs to start is in ASM, but due to a missing $GRID_HOME/gpnp/myserver/profiles/peer/profile.xml file, those were not an option here. (I think the problem is that the profile.xml is used by RAC only.)

$sqlplus / as sysasm
Connected to an idle instance.

SQL> create pfile='/home/oracle/pfile.ora' from spfile='+DATA/asm/asmparameterfile/registry.123.123456789';
create pfile='/home/oracle/pfile.ora' from spfile='+DATA/asm/asmparameterfile/registry.123.123456789'
*
ERROR at line 1:
ORA-01565: error in identifying file '+DATA/asm/asmparameterfile/registry.123.123456789'
ORA-17503: ksfdopn:2 Failed to open file +DATA/asm/asmparameterfile/registry.123.123456789
ORA-01034: ORACLE not available
$cd /app/oracle/diag/asm/+asm/+ASM/trace
$view alert_+ASM.log
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.123.123456789
System parameters with non-default values:
  large_pool_size          = 12M
  instance_type            = "asm"
  remote_login_passwordfile= "EXCLUSIVE"
  local_listener           = "myserver.mydomain.com:1899"
  asm_diskstring           = "/dev/oracleasm/disks/disk*"
  asm_diskgroups           = "FRA"
  asm_power_limit          = 1
  diagnostic_dest          = "/app/oracle"
USER (ospid: 9251): terminating the instance due to error 119
Instance terminated by USER, pid = 9251

$vi /home/oracle/initASMtemp.ora

*.spfile="+DATA/asm/asmparameterfile/registry.123.123456789"
*.LOCAL_LISTENER='myserver.mydomain.com:1899'

$sqlplus / as sysasm
Connected to an idle instance.

SQL> startup pfile='/home/oracle/initASMtemp.ora';
ASM instance started

Total System Global Area  283930624 bytes
Fixed Size                  2181896 bytes
Variable Size             256582904 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
SQL> alter system set local_listener='myserver.mydomain.com:1899' scope=spfile;
System altered.

SQL> show parameter local

NAME             TYPE        VALUE
---------------- ----------- -------------------------------
local_listener   string      myserver.mydomain.com:189