ORA-00849因参数设置不当造成ASM实例无法启动

概述

在搭建完成oracle11gr2的RAC后，查看相关数据库参数，发现ASM中memory内存设置过高，本想调整SGA/PGA，禁用AMM，神奇或者说是犯二之旅就来了。
首先查看了一下ASM实例相关值

SQL> show parameter mem

NAME TYPE VALUE

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

memory_max_target big integer 1076M

memory_target big integer 1076M

SQL> show parameter sga

NAME TYPE VALUE

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

lock_sga boolean FALSE

sga_max_size big integer 256M

sga_target big integer 256M
SQL> show parameter pga

NAME TYPE VALUE

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

pga_aggregate_target big integer 10M

曾扫过一眼，记得是将memory_target设置为0就是禁用AMM了，下面就开始犯二。

SQL> alter system set sga_max_size=256M scope=spfile;

System altered.

SQL> alter system set memory_max_target=0 scope=spfile;

System altered.

SQL> alter system set memory_target=0 scope=spfile;

System altered.

设置完成，重启集群，此时在刷体育新闻（没看alert日志），过了几分钟后登录，却发现

[grid@rac01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 13 21:45:59 2014

Connected to an idle instance.

SQL> show parameter mem

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

SQL> startup nomount

ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account

ORA-00849: SGA_TARGET 268435456 cannot be set to more than MEMORY_MAX_TARGET 0.

神马情况，此后得知，memory_max_target 要不设置比SGA还大的值要不就reset，0是不可取的。

虽然不太精通数据库，但心想，这也简单，修改一下初始化参数文件不就行了吗，二就要疯狂

SQL> create pfile='/tmp/asm.ora' from spfile;

create pfile='/tmp/asm.ora' from spfile

*

ERROR at line 1:

ORA-01565: error in identifying file '?/dbs/spfile@.ora'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

这又是怎么了，需要制定spfile吗，好吧

[grid@rac01 trace]$ asmcmd

Connected to an idle instance.

ASMCMD> ls

ASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM to run

还好，在之前查看spfile的具体位置

[grid@rac01 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 13 22:15:31 2014

Connected to an idle instance.

SQL> create pfile='/tmp/asm1.ora' from spfile='+CRS/rac-cluster/asmparameterfile/registry.253.839422741';

create pfile='/tmp/asm1.ora' from spfile='+CRS/rac-cluster/asmparameterfile/registry.253.839422741'

*

ERROR at line 1:

ORA-01565: error in identifying file

'+CRS/rac-cluster/asmparameterfile/registry.253.839422741'

ORA-17503: ksfdopn:2 Failed to open file

+CRS/rac-cluster/asmparameterfile/registry.253.839422741

ORA-01034: ORACLE not available

这次有点感觉了，神了，心想可能是ASM在启动过程中才能读取ASM中文件？或者grid用户权限小？？

先编辑一个pfile启动

[root@rac01 bin]# cat /tmp/asm.ora

spfile='+CRS/rac-cluster/asmparameterfile/registry.253.839422741'

memory_max_target=256M

memory_target=256M
SQL> startup nomount pfile='/tmp/asm.ora'

ORA-01565: error in identifying file '+CRS/rac-cluster/asmparameterfile/registry.253.839422741'

ORA-17503: ksfdopn:2 Failed to open file +CRS/rac-cluster/asmparameterfile/registry.253.839422741

这也不行，那就根据alert日志中，自己写一个pfile，启动

SQL> startup nomount pfile='/tmp/asm.ora'

ORA-01031: insufficient privileges

用root用户再试，还是报权限不足。此时你在想什么，还是已经看出门道来了，我当时在想，oracle不能这么弱智吧，如果在生产中遇到这个问题难道也没救了，重装？？？

既然不能重装，那就找吧，查，突然看到一个语句

sqlplus / as sysasm

我靠，太久没用，竟然忘了，11g的ASM还有个超级权限呢，sysasm 哎！！！
编辑pfile，再次通过pfile启动，终于见到阳光了

[grid@rac01 ~]$ vi /tmp/asm.ora

memory_max_target=1G

memory_target=1G

large_pool_size = 12M

instance_type = "asm"

remote_login_passwordfile= "EXCLUSIVE"

asm_diskstring = "/dev/asm*"

asm_diskgroups = "DATA"

asm_power_limit = 1

diagnostic_dest = "/grid/crs_base"
[grid@rac01 ~]$ sqlplus "/ as sysasm"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 13 23:35:10 2014

Connected to an idle instance.

SQL> startup pfile='/tmp/asm.ora';

ASM instance started

Total System Global Area 1135747072 bytes

Fixed Size 2260728 bytes

Variable Size 1108320520 bytes

ASM Cache 25165824 bytes

ASM diskgroups mounted

ASM diskgroups volume enabled

用原来spfile创建pfile，修改memory参数，并创建spfile

SQL> create pfile='/tmp/asm1.ora' from spfile='+CRS/rac-cluster/asmparameterfile/registry.253.839422741';

File created.

SQL> create spfile='+CRS/rac-cluster/asmparameterfile/registry.253.839422741' from pfile='/tmp/asm1.ora';

create spfile='+CRS/rac-cluster/asmparameterfile/registry.253.839422741' from pfile='/tmp/asm1.ora'

*

ERROR at line 1:

ORA-17502: ksfdcre:4 Failed to create file

+CRS/rac-cluster/asmparameterfile/registry.253.839422741

ORA-15177: cannot operate on system aliases

通过asmcmd命令进入，发现目录asmparameterfile不见了

ASMCMD> pwd

+crs/rac-cluster

ASMCMD> ls

OCRFILE/

ASMCMD>

那我们就自己创建一个spfile文件

SQL> create spfile='+CRS' from pfile='/tmp/asm.ora';

File created.

------查看alert日志发现输出一下内容
Sun Feb 16 00:07:12 2014

NOTE: updated gpnp profile ASM diskstring: /dev/asm*

NOTE: updated gpnp profile ASM SPFILE to +CRS/rac-cluster/asmparameterfile/registry.253.839635633

再次通过asmcmd命令查看

ASMCMD> pwd

+crs/rac-cluster

ASMCMD> ls

ASMPARAMETERFILE/

OCRFILE/

ASMCMD> cd ASMPARAMETERFILE

ASMCMD> pwd

+crs/rac-cluster/ASMPARAMETERFILE

ASMCMD> ls

REGISTRY.253.839635633

ASMCMD>

重启crs，至此，问题解决完了。

本是一个非常简单的问题，由于忽略了ASM中新权限，导致花费时间过长，没有很好的去理解和利用oracle特性，对于知识点掌握不够，也许更仔细一点会更好。

附件：关于ASM中SYSASM和SYSDBA简单说明，更详细可参考文档：
Oracle® Automatic Storage Management Administrator's Guide
11g Release 2 (11.2)

The SYSASM Privilege for Administering Oracle ASM

SYSASM is a system privilege that enables the separation of the SYSDBA database administration privilege from the Oracle ASM storage administration privilege. Access to the SYSASM privilege is granted by membership in an operating system group that is designated as the OSASM group. This is similar to SYSDBA and SYSOPER privileges, which are system privileges granted through membership in the groups designated as the OSDBA and OSOPER operating system groups. You can designate one group for all of these system privileges, or you can designate separate groups for each operating system privilege

The SYSDBA Privilege for Managing Oracle ASM Components

You can connect as SYSDBA to use SQL*Plus or ASMCMD commands to manage Oracle ASM components associated with the database. When running SQL or ASMCMD operations with the SYSDBA privilege, connect to the database instance rather than the Oracle ASM instance.

Connecting as SYSDBA to the database instance has a limited set of Oracle ASM privileges. For example, you cannot create a disk group when connected with the SYSDBA privilege.

欢迎同志们激烈交流，大家一起的力量是无穷的！

新浪微博：xysoul_云龙
博客地址：http://blog.sina.com.cn/longzhimeng99

ITPub博客

【ASM】ORA 00849 因参数设置不当造成ASM实例无法启动 xysoul

The SYSDBA Privilege for Managing Oracle ASM Components

最新文章