oracle 无法分配32字节,ORA-04031:无法分配 32 字节的共享内存

案例 ORA-04031:无法分配 32 字节的共享内存

今天發現數據庫異常宕機情況

出現錯誤ORA-04031: 无法分配 ORA-04031: 无法分配 32 字节的共享内存

數據庫版本 目前版本10.2.0.4 但是compatible還是10.2.0.3.0

按道理講此bug，在10.2.0.4已解決

select * from (select name

,value

,decode(isdefault, 'TRUE','Y','N') as "Default"

,decode(ISEM,'TRUE','Y','N') as SesMod

,decode(ISYM,'IMMEDIATE', 'I',

'DEFERRED', 'D',

'FALSE', 'N') as SysMod

,decode(IMOD,'MODIFIED','U',

'SYS_MODIFIED','S','N') as Modified

,decode(IADJ,'TRUE','Y','N') as Adjusted

,description

from ( --GV$SYSTEM_PARAMETER

select x.inst_id as instance

,x.indx+1

,ksppinm as name

,ksppity

,ksppstvl as value

,ksppstdf as isdefault

,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM

,decode(bitand(ksppiflg/65536,3),

1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM

,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD

,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ

,ksppdesc as description

from x$ksppi x

,x$ksppsv y

where x.indx = y.indx

and substr(ksppinm,1,1) = '_'

and x.inst_id = USERENV('Instance')

)

order by name

)

以上sql，能解釋隱含參數

分析 1.可能sharepool 真的不夠

先查詢下pool

select pool,sum(bytes/1024/1204),sum(bytes)/(select sum(bytes) from v$sgastat)*100 as "Percent"

from v$sgastat  where pool is not null  group by pool;

查詢buffer大小

select name,sum(bytes/1024/1204),sum(bytes)/(select sum(bytes) from v$sgastat)*100 as "Percent"

from v$sgastat where pool is  null group by name;

10G SGA自動管理信息

select component,CURRENT_SIZE/1024/1204,user_specified_size,granule_size from v$sga_dynamic_components;

11G MEMORY自動管理信息

SELECT * FROM   V$MEMORY_DYNAMIC_COMPONENTS

SELECT * FROM V$MEMORY_RESIZE_OPS

SELECT * FROM V$MEMORY_TARGET_ADVICE

結論：sharepool沒有不夠的問題！

分析 2.compatible參數還是10.2.0.3.0，導致BUG

解決辦法

2.1 create pfile='/u01/pfile.ora' from spfile;

并將compatible 修改為‘10.2.0.4.0’

2.2 shutdown 資料庫

2.3 startup nomount pfile='/u01/pfile.ora'

2.4 create spfile from pfile='/u01/pfile.ora'

2.5 shutdown immediate;

2.6 startup;

修改完成，待觀察。