从11gR2开始，使用图形化方式安装Grid Infrastructure和使用ASMCA工具创建ASM实例都强制使用磁盘组作为ASM实例参数文件的存储方式。如果想将ASM参数文件存放到本地磁盘文件系统中，只能手动创建ASM实例。手动创建ASM实例，在执行CRETAE SPFILE FROM PFILE将PFILE转换成SPFILE的时候收到如下报错：

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]]

下面是METALINK的解决方案：

ORA-29786: SIHA attribute GET failed with Error If 11gR2 ASM instance is created manually [ID 976075.1]
修改时间:2011-8-11

类型:PROBLEM

状态:PUBLISHED

优先级:3

In this Document
  Symptoms
  Changes
  Cause
  Solution

Symptoms

After creating an initial parameter file init+ASM.ora manually and starting an ASM instance, certain ASM commands fails with ORA-29786 in sqlplus:

sqlplus / as sysasm
SQL> create spfile='$ORACLE_HOME/spfile+ASM.ora' from pfile='$ORACLE_HOME/dbs/init+ASM.ora';
create spfile='$ORACLE_HOME/spfile+ASM.ora' from pfile='$ORACLE_HOME/dbs/init+ASM.ora'
*
ERROR at line 1:
ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]
lsts[0]]

SQL> create diskgroup dg1 normal redundancy disk '/opt/oracle/oradata/nobilldb/DG1_dev0' disk '/opt/oracle/oradata/nobilldb/DG1_dev1';

Diskgroup created.

SQL> drop diskgroup dg1;
drop diskgroup dg1
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]
lsts[0]]

Better option to create ASM instance is to run asmca in GUI/Silent mode.

Changes

Starting with 11gR2, ASM instance is a resoure in CRS repository also in single instance installations. Hence, it must be registered to OCR before doing certain operations like create/drop diskgroup, create pfile/spfile, etc.

Cause

ASM instance is created and started manually but it is not registered to cluster repository.

Solution

If ASM instance is created manually, add ASM instance to CRS repository with srvctl add asm:

srvctl add asm -l LISTENER -p /oracle/product/11.2.0/grid/dbs/init+ASM.ora -d '/dev/sdc*'

srvctl add asm -h -- will give the options

根据文章提示，执行以下命令：

# pwd

/u01/app/11.2.0/grid/bin

# ./srvctl add asm -h

Adds an ASM configuration to be managed by Oracle Restart.

Usage: srvctl add asm [-l ] [-p ] [-d ]

-l Listener name

-p Server parameter file path

-d ASM diskgroup discovery string

-h Print usage

# su - grid
$ ./srvctl add asm -l LISTENER -p /u01/app/11.2.0/grid/dbs/spfile+ASM.ora -d '/dev/rhdisk*'

注意：以上的srvctl add asm命令必须用grid用户执行，不能用root用户执行，详情参考文章《11gR2手动创建的ASM实例无法被Clusterware管理的问题的解决》http://space.itpub.net/?uid-23135684-action-viewspace-itemid-743090

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 10 11:12:53 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Automatic Storage Management option

SQL> create spfile from pfile;

create spfile from pfile

*

ERROR at line 1:

ORA-27038: created file already exists

Additional information: 1

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Automatic Storage Management option

$ cd $ORACLE_HOME/dbs

$ ls

ab_+ASM.dat hc_+ASM.dat init+ASM.ora init.ora spfile+ASM.ora

执行srvctl add asm命令会自动为ASM实例添加SPFILE参数文件。

$ strings spfile*

/M?

*.asm_power_limit=1

*.diagnostic_dest='/u01/app/grid'

*.instance_type='asm'

*.large_pool_size=12M

*.remote_login_passwordfile='EXCLUSIVE'

$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 10 11:13:21 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Automatic Storage Management option

SQL> shutdown immediate

ORA-15100: invalid or missing diskgroup name

ASM instance shutdown

SQL>

SQL> startup nomount

ASM instance started

Total System Global Area 283930624 bytes

Fixed Size 2220800 bytes

Variable Size 256544000 bytes

ASM Cache 25165824 bytes

SQL> show parameter spfile

NAME TYPE

------------------------------------ ----------------------

VALUE

------------------------------

spfile string

/u01/app/11.2.0/grid/dbs/spfil

e+ASM.ora

--end--

ITPub博客

11gR2手动创建ASM实例ORA-29786错误解决方法

Symptoms

Changes

Cause

Solution

最新文章