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已解決

參考文章
http://blog.csdn.net/perddy/article/details/4430823

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;
修改完成，待觀察。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25850100/viewspace-714825/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25850100/viewspace-714825/