從ORA-27300,ORA-27301到ORA-00064,ora-27300ora-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