ORA-29786: SIHA attribute GET failed with error [Attribute ‘SPFILE’ sts[200] lsts[0]]

I  had a situation where the ASM instance was started using the pfile. A better option for me was to start it with SPFILE rather than with PFILE. So, when I tried to create an SPFILE from the PFILE, Oracle threw an error “ORA-29786: SIHA attribute GET failed with error [Attribute ‘SPFILE’ sts[200] lsts[0]]“.

[oracle@ora1-1 ~]$ export ORACLE_SID=+ASM
[oracle@ora1-1 ~]$ export ORACLE_HOME=$ORA_CRS_HOME
[oracle@ora1-1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 15:08:12 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> select status,instance_name from v$instance;

STATUS   INSTANCE_NAME
-------- ---------------
STARTED  +ASM

SQL> select name,state from v$asm_diskgroup;

NAME   STATE
------ -----------
FRA    MOUNTED
DATA   MOUNTED

SQL> show parameter spfile

NAME    TYPE    VALUE
------  ------- -----------
spfile  string
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]lsts[0]]

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Automatic Storage Management option

The very next thing is to check if ASM (resource ora.asm) is registered with CRS.

[oracle@ora1-1 ~]$ srvctl config asm
PRCR-1001 : Resource ora.asm does not exist

Here it is !! The resource ora.asm does not exist and which is why Oracle is unaware of the ASM instance that was started through the PFILE and not allowing to perform any operations on it.

Now, lets add ASM through SRVCTL utility and check its configuration.

[oracle@ora1-1 ~]$ srvctl add asm
[oracle@ora1-1 ~]$ srvctl config asm
ASM home: /u01/app/oracle/product/11.2.0.3/grid
ASM listener was not found
PRCA-1032 : ASM listener LISTENER does not exist
Spfile:
ASM diskgroup discovery string: /dev/oracleasm/disks/

This newly registered ASM would not be running and will have to be started through SRVCTL

[oracle@ora1-1 ~]$ srvctl status asm
ASM is not running.
[oracle@ora1-1 ~]$ srvctl start asm
[oracle@ora1-1 ~]$ srvctl status asm

ASM is running on ora1-1

You can still see from the above configuration, that the newly added ASM is not using SPFILE. So, now lets connect to the ASM instance at SQL level, create an SPFILE and start the ASM instance with the newly created SPFILE.

[oracle@ora1-1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 15:10:35 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> show parameter spfile

NAME   TYPE     VALUE
------ -------- --------------------
spfile string
SQL>
SQL> create spfile from pfile;

File created.

SQL> alter diskgroup DATA dismount;

Diskgroup altered.

SQL> alter diskgroup FRA dismount;

Diskgroup altered.

SQL> shut immediate
ORA-15100: invalid or missing diskgroup name
ORA-15100: invalid or missing diskgroup name
ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 283930624 bytes
Fixed Size 2227664 bytes
Variable Size 256537136 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL>
SQL> show parameter spfile

NAME    TYPE    VALUE
------- ------- --------------------------------------------------------
spfile  string  /u01/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora
SQL>
SQL> select name,state from v$asm_diskgroup;

NAME  STATE
----- ---------
DATA  MOUNTED
FRA   MOUNTED

Finally, lets check the configuration of this ASM instance through SRVCTL

[oracle@ora1-1 ~]$ srvctl config asm
ASM home: /u01/app/oracle/product/11.2.0.3/grid
ASM listener was not found
PRCA-1032 : ASM listener LISTENER does not exist
Spfile: /u01/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora
ASM diskgroup discovery string: /dev/oracleasm/disks/

Here you go !!

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.