首页 > 代码库 > 从ORA-27300，ORA-27301到ORA-00064
从ORA-27300，ORA-27301到ORA-00064
最近由于session数量增加，需要调整session，也就是要调整process参数。看是比较简单的一个问题，却遭遇了ORA-27300，ORA-27301。因为这个涉及到了有关内核参数kernel.sem的修改。下面是其具体描述。
1、故障现象
OS版本：SUSE Linux Enterprise Server 10 SP3 (x86_64) - Kernel \r (\l)
DB版本: SQL*Plus: Release 10.2.0.3.0 - Production
需求：Processes参数由1000修改到2000
SQL> startup pfile=/u02/database/MRDB/initMRDB.ora;
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
2、故障的分析与解决
#起初咋一看还以为空间不够呢，如下，显然不是空间的问题，其次有个很重要的表示"semget"
SQL> ho df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 1.8T 826G 885G 49% /
udev 32G 116K 32G 1% /dev
/dev/sda1 99M 9.5M 85M 11% /boot
#后台日志如下
Tue Aug 5 18:07:22 2014
Starting ORACLE instance (normal)
Tue Aug 5 18:07:22 2014
Errors in file /u02/database/MRDB/udump/mrdb_ora_30366.trc:
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
#Google了一些文章，描述的是需要调整内核参数kernel.sem，也就是信号量的问题
v2012db02u:~ # grep kernel.sem /etc/sysctl.conf
kernel.sem = 1250 32000 100 256 #这个是当前的值
信号量设置示例
SEMMSL应该设置为服务器中实例中具有最大的PROCESSES参数+10，例如，当最大的PROCESSES参数为5000时，SEMMSL应设置为5010。
SEMMNS参数应设置为SEMMSL*SEMMNI，接上例SEMMSL为5010，SEMMNI的值一般为128，则SEMMNS参数应为（5010*128）=641280。
SEMOPM参数应设置与SEMMSL参数相同，接上例此处应设置为5010
因此对于信号量建议做如下设置
sysctl -w kernel.sem="5010 641280 5010 128"
#关于这个参数的具体描述及设置可以参考：Linux 内核参数优化(for oracle)
#由于当前服务器存在N个实例，因此给了一个比较大的值，生产环境应慎重修改，如下
v2012db02u:~ # vi /etc/sysctl.conf
v2012db02u:~ # sysctl -p
...........
kernel.sem = 7000 1792000 7000 256
...........
#再次启动，狂汗....
SQL> startup pfile=/u02/database/MR/initMR.ora;
ORA-00064: object is too large to allocate on this O/S (1,5150880)
v2012db02u:~ > oerr ora 00064
00064, 00000, "object is too large to allocate on this O/S (%s,%s)"
// *Cause: An initialization parameter was set to a value that required
// allocating more contiguous space than can be allocated on this
// operating system.
// *Action: Reduce the value of the initialization parameter.
#从上面的描述来看，不能从os分配连续的空间。
#关于这个问题Metalink(Doc ID 283980.1)上提供了描述信息，需要增大内存分配粒度到16M。
#以下是当前的内存粒度为4M。
SQL> SQL> SELECT x.ksppinm name, y.ksppstvl VALUE, x.ksppdesc descbtion
2 FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = USERENV (‘Instance‘)
3 4 AND y.inst_id = USERENV (‘Instance‘)
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE ‘%_ksmg_granule%‘;
NAME VALUE DESCBTION
----------------------------------- ------------------------------ --------------------------------------------------
_ksmg_granule_size 4194304 granule size in bytes
_ksmg_granule_locking_status 1 granule locking status
SQL> show parameter sga #当前sga大小为1G
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1G
sga_target big integer 1G
#Author : Leshami
#Blog : http://blog.csdn.net/leshami
#直接调整为2G
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 2G
sga_target big integer 2G
#下面是修改过后内存的粒度。
SQL> set linesize 145
col name format a35
SQL> SQL> col value format a30
col descbtion format a50 wrap
SELECT x.ksppinm name, y.ksppstvl VALUE, x.ksppdesc descbtion
SQL> SQL> 2 FROM x$ksppi x, x$ksppcv y
3 WHERE x.inst_id = USERENV (‘Instance‘)
4 AND y.inst_id = USERENV (‘Instance‘)
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE ‘%_ksmg_granule%‘;
NAME VALUE DESCBTION
----------------------------------- ------------------------------ --------------------------------------------------
_ksmg_granule_size 16777216 granule size in bytes
_ksmg_granule_locking_status 1 granule locking status
#再次重启DB OK。
3、Doc ID 283980.1
Oracle Server - Enterprise Edition - Version: 9.0.1.0 and later [Release: 9.0.1 and later ]
Information in this document applies to any platform.
OPEN_LINKS_PER_INSTNACE is set to over 400.
(subarea) size. The granule size is calculated based on the SGA size.
SOLUTION
Test case to reproduce the error
================================
a) add OPEN_LINKS_PER_INSTANCE = 1000 in the initora
b) use sqlplus to try starting up the database
This is NOT a BUG.
To fix the issue , increase the size of the SGA to over 128Mb by increasing DB_CACHE_SIZE,
SHARED_POOL_SIZE,LARGE_POOL_SIZE, JAVA_POOL_SIZE, etc.This will cause the granule size to
be increased to 16Mb.
Workaround
==========
Set the following hidden parameter
_ksmg_granule_size = 16777216
Note: Please set granule size more than or equal to size showing in the error message.
1、故障现象
OS版本：SUSE Linux Enterprise Server 10 SP3 (x86_64) - Kernel \r (\l)
DB版本: SQL*Plus: Release 10.2.0.3.0 - Production
需求：Processes参数由1000修改到2000
SQL> startup pfile=/u02/database/MRDB/initMRDB.ora;
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
2、故障的分析与解决
#起初咋一看还以为空间不够呢，如下，显然不是空间的问题，其次有个很重要的表示"semget"
SQL> ho df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 1.8T 826G 885G 49% /
udev 32G 116K 32G 1% /dev
/dev/sda1 99M 9.5M 85M 11% /boot
#后台日志如下
Tue Aug 5 18:07:22 2014
Starting ORACLE instance (normal)
Tue Aug 5 18:07:22 2014
Errors in file /u02/database/MRDB/udump/mrdb_ora_30366.trc:
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
#Google了一些文章，描述的是需要调整内核参数kernel.sem，也就是信号量的问题
v2012db02u:~ # grep kernel.sem /etc/sysctl.conf
kernel.sem = 1250 32000 100 256 #这个是当前的值
信号量设置示例
SEMMSL应该设置为服务器中实例中具有最大的PROCESSES参数+10，例如，当最大的PROCESSES参数为5000时，SEMMSL应设置为5010。
SEMMNS参数应设置为SEMMSL*SEMMNI，接上例SEMMSL为5010，SEMMNI的值一般为128，则SEMMNS参数应为（5010*128）=641280。
SEMOPM参数应设置与SEMMSL参数相同，接上例此处应设置为5010
因此对于信号量建议做如下设置
sysctl -w kernel.sem="5010 641280 5010 128"
#关于这个参数的具体描述及设置可以参考：Linux 内核参数优化(for oracle)
#由于当前服务器存在N个实例，因此给了一个比较大的值，生产环境应慎重修改，如下
v2012db02u:~ # vi /etc/sysctl.conf
v2012db02u:~ # sysctl -p
...........
kernel.sem = 7000 1792000 7000 256
...........
#再次启动，狂汗....
SQL> startup pfile=/u02/database/MR/initMR.ora;
ORA-00064: object is too large to allocate on this O/S (1,5150880)
v2012db02u:~ > oerr ora 00064
00064, 00000, "object is too large to allocate on this O/S (%s,%s)"
// *Cause: An initialization parameter was set to a value that required
// allocating more contiguous space than can be allocated on this
// operating system.
// *Action: Reduce the value of the initialization parameter.
#从上面的描述来看，不能从os分配连续的空间。
#关于这个问题Metalink(Doc ID 283980.1)上提供了描述信息，需要增大内存分配粒度到16M。
#以下是当前的内存粒度为4M。
SQL> SQL> SELECT x.ksppinm name, y.ksppstvl VALUE, x.ksppdesc descbtion
2 FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = USERENV (‘Instance‘)
3 4 AND y.inst_id = USERENV (‘Instance‘)
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE ‘%_ksmg_granule%‘;
NAME VALUE DESCBTION
----------------------------------- ------------------------------ --------------------------------------------------
_ksmg_granule_size 4194304 granule size in bytes
_ksmg_granule_locking_status 1 granule locking status
SQL> show parameter sga #当前sga大小为1G
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1G
sga_target big integer 1G
#Author : Leshami
#Blog : http://blog.csdn.net/leshami
#直接调整为2G
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 2G
sga_target big integer 2G
#下面是修改过后内存的粒度。
SQL> set linesize 145
col name format a35
SQL> SQL> col value format a30
col descbtion format a50 wrap
SELECT x.ksppinm name, y.ksppstvl VALUE, x.ksppdesc descbtion
SQL> SQL> 2 FROM x$ksppi x, x$ksppcv y
3 WHERE x.inst_id = USERENV (‘Instance‘)
4 AND y.inst_id = USERENV (‘Instance‘)
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE ‘%_ksmg_granule%‘;
NAME VALUE DESCBTION
----------------------------------- ------------------------------ --------------------------------------------------
_ksmg_granule_size 16777216 granule size in bytes
_ksmg_granule_locking_status 1 granule locking status
#再次重启DB OK。
3、Doc ID 283980.1
Oracle Server - Enterprise Edition - Version: 9.0.1.0 and later [Release: 9.0.1 and later ]
Information in this document applies to any platform.
SYMPTOMS
ORA-00064 error is possibly encountered on database startup when the parameterOPEN_LINKS_PER_INSTNACE is set to over 400.
CAUSE
The contiguous area of storage to be allocated , exceeds the SGA granule(subarea) size. The granule size is calculated based on the SGA size.
SOLUTION
Test case to reproduce the error
================================
a) add OPEN_LINKS_PER_INSTANCE = 1000 in the initora
b) use sqlplus to try starting up the database
This is NOT a BUG.
To fix the issue , increase the size of the SGA to over 128Mb by increasing DB_CACHE_SIZE,
SHARED_POOL_SIZE,LARGE_POOL_SIZE, JAVA_POOL_SIZE, etc.This will cause the granule size to
be increased to 16Mb.
Workaround
==========
Set the following hidden parameter
_ksmg_granule_size = 16777216
Note: Please set granule size more than or equal to size showing in the error message.
另外一篇与该错误有关Bug 5959853 - ORA-64 DURING DATABASE STARTUP WITH PROCESSES>6000. DocID:1457812.1
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。