一问题现象及日志

DBA接到客户请求系统已经不能使用了，这是一个超市的一套系统，oracle 版本使用的是11.2.0.3.0

alert日志出现ora-04031报错，先使用alter system flush shared_pool 后，应用正常使用了20分钟后就宕机了

宕机前alert日志（ 2024.10.15）

alert日志在10.15日系统重启前报错

ORA-04031: ???? 2928 ??????? ("shared pool","unknown object","sga heap(1,1)","KGLHD")

ERROR at line 1:

ORA-04031: ???? 32 ??????? ("shared pool","unknown

object","KGLH0^556e728f","kglHeapInitialize:temp")

ERROR:

ORA-00604: ?? SQL ?? 1 ????

ORA-04031: ???? 32 ??????? ("shared pool","BEGIN

DBMS_OUTPUT.ENABLE(NUL...","KGLH0^e3a2d601","kglHeapInitialize:temp")

其中还报有ora-600错误

以上关键信息KGLHD，是什么有点纳闷，后面详细分析

重启数据库后，修改内存手工管理模式，过滤几天再次宕机，2024.10.20再次宕机前日志

Errors in file /u01/app/oracle/diag/rdbms/upop/upop/trace/upop_smon_3007.trc:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select f.file#, f.block#, f....","SQLA","tmp")

Mon Oct 20 08:23:26 2014

Errors in file /u01/app/oracle/diag/rdbms/upop/upop/trace/upop_smon_3007.trc:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")

Mon Oct 20 08:23:38 2014

Errors in file /u01/app/oracle/diag/rdbms/upop/upop/trace/upop_j001_7116.trc:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","update sys.job$ set this_dat...","KGLH0^7a42409d","kglHeapInitialize:temp")

Mon Oct 20 08:23:38 2014

Errors in file /u01/app/oracle/diag/rdbms/upop/upop/trace/upop_j000_7114.trc:

ORA-12012: 自动执行作业 14 出错

ORA-04031: 无法分配 3480 字节的共享内存 ("shared pool","DXS","PLMCD^97454214","BAMIMA: Bam Buffer")

ORA-06508: PL/SQL: 无法找到正在调用 : "DBUSRBAS.DXS" 的程序单元

ORA-06512: 在 "DBUSRBAS.TASK", line 685

二诊断思路

2.1 查看系统使用的是内存自动管理，在oracle 10g，11g 内存自动管理方式可能会导致ora-04031错误可以使用内存手工管理模式规避该问题，

2.2 ora-04031可能与shared pool中的绑定变量使用有关

2.3 oracle bug 问题也会导致ora-04031

三解决过程

3.1

由于已经宕机，先重启数据库，设置内存手工管理

机器内存是32G，还部署有应用，给数据库分配内存是10G

如下，修改内存手工管理

规划sga总大小8G，sga各个组件固定大小（其中db_chache_size 4G，shared_pool_size 3G）；pga 2G

(1) 取消memory_target和memory_max_target参数设置

create pfile='/tmp/puoppfile.ora' from spfile;

alter system set memory_max_target=0 scope=spfile;

alter system set memory_target=0 scope=spfile;

(2)SGA各个组件大小设置（总大小8G)

set line 120

select COMPONENT,current_size/1024/1024 as MB from v$sga_dynamic_components;

alter system set sga_target=0 scope=spfile;

alter system set sga_max_size=8G scope=spfile;

alter system set JAVA_POOL_SIZE=256M scope=spfile;

alter system set large_POOL_SIZE=256M scope=spfile;

alter system set db_cache_size=4G scope=spfile;

alter system set shared_pool_size=3G scope=spfile;

（3）pga设置2G

alter system set pga_aggregate_target=2G scope=spfile;

设置完成，重启数据库

shutdown immediate;

startup;

(4) 检查结果

show parameter sga

show parameter pga

show parameter shared

show parameter memory

set line 120

select COMPONENT,current_size/1024/1024 as MB from v$sga_dynamic_components;

select ROUND(value/1024/1024,2) from v$pgastat where name='total PGA allocated';

附：

修改内存组件大小后，startup 启动报错

提示memory_max_target小于sga_max_size;

解决：在数据库没有启动情况下，create pfile='/tmp/upopfile.ora' from spfile

vi /tmp/upopfile.ora

将memory_max_target修改为10737418240=10*1024*1024*1024

startup pfile='/tmp/upopfile.ora'

create spfile from pfile='/tmp/upopfile.ora';

操作结果

QL> show parameter sga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 8G

sga_target big integer 0

SQL> show parameter pga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target big integer 2G

SQL> show parameter shared

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address integer 0

max_shared_servers integer

shared_memory_address integer 0

shared_pool_reserved_size big integer 161061273

shared_pool_size big integer 3G

shared_server_sessions integer

shared_servers integer 0

SQL> show parameter memory

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address integer 0

memory_max_target big integer 10G

memory_target big integer 0

shared_memory_address integer 0

SQL> set line 120

SQL> select COMPONENT,current_size/1024/1024 as MB from v$sga_dynamic_components;

COMPONENT MB

---------------------------------------------------------------- ----------

shared pool 3072

large pool 256

java pool 256

streams pool 0

DEFAULT buffer cache 4096

KEEP buffer cache 0

RECYCLE buffer cache 0

DEFAULT 2K buffer cache 0

DEFAULT 4K buffer cache 0

DEFAULT 8K buffer cache 0

DEFAULT 16K buffer cache 0

COMPONENT MB

---------------------------------------------------------------- ----------

DEFAULT 32K buffer cache 0

Shared IO Pool 0

ASM Buffer Cache 0

SQL> select ROUND(value/1024/1024,2) from v$pgastat where name='total PGA allocated';

ROUND(VALUE/1024/1024,2)

------------------------

69.35

3.2 检查是否与shared pool中绑定变量或执行计划有关，或者sql有关

检查执行计划发现问题不大

select sql_id,child_number from v$sql where sql_id ='906p86k62kzkh';

select * from table(dbms_xplan.display_cursor('906p86k62kzkh',&child_number));

select HASH_VALUE,sql_text,executions from v$sqlarea where HASH_VALUE=':&hash';

1773737552

select * from table(dbms_xplan.display_awr('1773737552'));

以上检查排除是应用问题导致。

3.3 部署脚本监控shared pool各个内存组件使用情况

检查Shared Pool Usage使用情况，根据部署脚本，发现以下规律

SELECT SUM(bytes)/1024/1024 as "Total Shared Pool Usage" FROM v$sgastat WHERE pool = 'shared pool' AND name != 'free memory';

Total Shared Pool Usage 逐渐增长：手工管理设置为3G，war报告建议7G（SGA总大小才8G）。

10.15重启之后

"Total Shared Pool Usage"大小

4138.7879 (10.18)

5081.09141 (10.19)

5876.86457 （10.20)

导出宕机前的AWR报告，查看报告最后面内容

2014.10.15 13:00---15:00，可以发现shared_pool的KGLHD达到5.2G，buffer_cache 仅有352MB

查看系统重启后SGA各个组件内存使用情况（即手工管理固定内存大小后，系统重启时间2014.10.15 16:52），

2014.10.15 16:52---18:00，KGLHD两小时内逐渐增大，变化率165%，364MB

2014.10.17 13:00--14:00 相相对于上一个awr报告（两天前即15日的），shared pool在增长，buffer cache 在减少

其中KGLHD达到2847MB，已经增长了2847-364=2483MB

2014.10.20 05:00--06:00，可以看到shred pool已经使用了7G（7040MB），而buffer cache缩小到128MB

由以上3个awr报告中，SGA内存组件大小变化情况，可以判断即使在对数据库内存使用手工管理固定大小，但shared pool依然逐渐

增大至7G，之前分配的是3G ，从15号到20号增长了4G,具体是其中的KGLHD不停在增长（达到5.4G），和系统宕机前类似。

至此，内存手工管理实效，问题再现，alert日志继续报错如下

Errors in file /u01/app/oracle/diag/rdbms/upop/upop/trace/upop_smon_3007.trc:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","select f.file#, f.block#, f....","SQLA","tmp")

Mon Oct 20 08:23:26 2014

Errors in file /u01/app/oracle/diag/rdbms/upop/upop/trace/upop_smon_3007.trc:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","unknown object","KGLH0^f185eace","kglHeapInitialize:temp")

Mon Oct 20 08:23:38 2014

Errors in file /u01/app/oracle/diag/rdbms/upop/upop/trace/upop_j001_7116.trc:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","update sys.job$ set this_dat...","KGLH0^7a42409d","kglHeapInitialize:temp")

Mon Oct 20 08:23:38 2014

Errors in file /u01/app/oracle/diag/rdbms/upop/upop/trace/upop_j000_7114.trc:

ORA-12012: 自动执行作业 14 出错

ORA-04031: 无法分配 3480 字节的共享内存 ("shared pool","DXS","PLMCD^97454214","BAMIMA: Bam Buffer")

ORA-06508: PL/SQL: 无法找到正在调用 : "DBUSRBAS.DXS" 的程序单元

ORA-06512: 在 "DBUSRBAS.TASK", line 685

至此陷入山穷水尽

..........

在MOS上搜索ora-04031，文章有好几百篇，一筹莫展

偶然灵机一动，在MOS上使用关键字 KGLHD+ORA04031 搜索, 找到比较相近的bug（1534706.1）

Doc ID 4031.1

ORA-04031: ???? 2928 ??????? ("shared pool","unknown object","sga heap(1,1)","KGLHD")

Shared pool leak of "KGLHD" memory when using multiple subpools

bug

1534706.1 （High allocation under KGLHD in shared pool.）

This problem may be seen if both of the following are true:

(a) High allocation of KGLHD in shared pool which may lead to ORA-4031

(b) Multiple shared pool subpools must be in use.

(eg _kghdsidx_count > 1)

解决;打上相应PSU，解决问题

四后续观察

使用任务计划观察shared pool内存组件变化情况（尤其KGLHD），发现KGLHD大小稳定，至此解决问题

oracle@yingx ~]$ crontab -l

40 2 * * * "/home/oracle/oracleback.sh"

#0 */4 * * * sh /home/oracle/share_pool_check.sh >> share_pool_check.log

#20 3 * * 3,0 sh /home/oracle/share_pool_clean.sh >> share_pool_clean.log

#36 14 * * 3,5 sh /home/oracle/share_pool_clean.sh >> share_pool_clean.log

[oracle@yingx ~]$ cat /home/oracle/share_pool_check.sh

#!/bin/bash

#Author:lsl

cd --

#. ./.profile

. ./.bash_profile

#export ORACLE_SID=orcl

check_os(){

(uname -a|grep Linux)&&return 1;

(uname -a|grep AIX)&&return 2;

(uname -a|grep HP)&&return 3;

}

check_os

os=$?

DATE=$(date '+%Y%m%d')

DATE1=$(date '+%Y%m%d_%H%M%S')

SID=`echo $ORACLE_SID`

OUTPUT=sahred_pool_check

#OUTPUT=${DATE}_$(hostname)

if [ -d ${OUTPUT} ]; then

echo "dir is exties"

else mkdir -p ${OUTPUT}

fi

cd ${OUTPUT}

touch ${DATE1}_${SID}_share_pool_check.log

export file=${DATE1}_${SID}_share_pool_check.log

sqlplus / as sysdba <

set feedback off;

set linesize 100;

set wrap off;

set pagesize 5000;

set newpage 0;

set echo off;

set serveroutput on;

set long 40000;

set timing on;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

spool \$file

set line 150

COL COMPONENT FORMAT A25

COL INITIAL_SIZE FORMAT A10

COL FINAL_SIZE FORMAT A10

select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME

from v\$sga_resize_ops

where component = 'shared pool' and status = 'COMPLETE'

order by start_time, component;

select * from (select POOL, name, BYTES/1024/1024 as MB from v\$sgastat where pool like 'shared%' order by bytes desc) where rownum<=5;

select inst_id, bytes/1024/1024 KGLHD from gv\$sgastat where name='KGLHD' and pool='shared pool';

select inst_id, bytes/1024/1024 KGLH0 from gv\$sgastat where name='KGLH0' and pool='shared pool';

SELECT name, bytes/1024/1024 as MB FROM v\$sgastat WHERE pool = 'shared pool' AND name = 'free memory' ORDER BY bytes DESC;

SELECT SUM(bytes)/1024/1024 as "Total Shared Pool Usage" FROM v\$sgastat WHERE pool = 'shared pool' AND name != 'free memory';

spool off

EOF

free -m >> $file

ls -l /u01/app/oracle/diag/rdbms/upop/upop/trace |grep trc |wc -l >> $file

10.17 shared_pool 使用情况

SQL> SELECT SUM(bytes)/1024/1024 as "Total Shared Pool Usage" FROM v$sgastat WHERE pool = 'shared pool' AND name != 'free memory';

Total Shared Pool Usage

-----------------------

3265.23795

Elapsed: 00:00:00.01

SQL> select * from (select POOL, name, BYTES/1024/1024 as MB from v$sgastat where pool like 'shared%' order by bytes desc) where rownum<=5;

POOL NAME MB

------------ -------------------------- ----------

shared pool KGLH0 1196.00986

shared pool SQLA 732.630402

shared pool KGLHD 305.8918

shared pool free memory 247.295372

shared pool SQLP 182.102776

Elapsed: 00:00:00.01

SQL> select inst_id, bytes/1024/1024 KGLHD from gv$sgastat where name='KGLHD' and pool='shared pool';

INST_ID KGLHD

---------- ----------

1 305.889511

Elapsed: 00:00:00.00

SQL> select inst_id, bytes/1024/1024 KGLH0 from gv$sgastat where name='KGLH0' and pool='shared pool';

INST_ID KGLH0

---------- ----------

1 1196.00218

Elapsed: 00:00:00.01

SQL> SELECT SUM(bytes)/1024/1024 as "Total Shared Pool Usage" FROM v$sgastat WHERE pool = 'shared pool' AND name != 'free memory';

Total Shared Pool Usage

-----------------------

2824.73301

Elapsed: 00:00:00.00

oracle宕机ORA-04031 ("shared pool","unknown object","sga heap(1,1)","KGLHD")