Wednesday, September 15, 2010

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

When we created spfile in ASM instance and found ORA-29786.
SQL> create spfile='+DATA' from memory;
create spfile='+DATA' from pfile
*
ERROR at line 1:
ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]
lsts[0]]
If we check oracle support 976075.1:
11gR2, ASM instance is a resoure in CRS repository also in single instance installations. Hence, it must be registered to OCR before.
then, check and solve:
$ crsctl stat res | grep NAME\= |grep asm
I had no asm in my OCR.
$ srvctl add asm -l LISTENER -p '+DATA' -d +DATA
PRCR-1001 : Resource ora.LISTENER.lsnr does not exist
$ srvctl add listener -l LISTENER -s -o /u01/app/oracle/product/11.2.0/grid_1
$ srvctl add asm -l LISTENER -p '+DATA' -d +DATA
$ crsctl stat res | grep NAME\= |grep asm
NAME=ora.asm
then try to create spfile again:
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

SQL> create spfile='+DATA' from memory;
File created.

SQL> shutdown
this case, I used "srvctl" to help (start)
$ srvctl start asm
check spfile in ASM instance.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/asm/asmparameterfile/registry.253.729779795
So, ORA-29786 error, we can solve to register ASM in OCR.

3 comments:

Anonymous said...

Thanks for this posting! It was exactly what I needed

Anonymous said...

Thank you! Your topic solved my problem :)

DBA University said...

Thank you for the solution.