从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.
SYMPTOMS
ORA-00064 error is possibly encountered on database startup when the parameter OPEN_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