1xxxx313 ora-4031错误下面是故障告警信息

主要告警内容如下2023-04-06T06:05:09.483485+08:00Errors in file /oracle/diag/rdbms/xxxx31/xxxx313/trace/xxxx313_lmd0_146293.trc (incident=160185):ORA-04031: unable to allocate 13840 bytes of shared memory ("shared pool","unknown object","sga heap(5,0)","ges resource dynamic")Incident details in: /oracle/diag/rdbms/xxxx31/xxxx313/incident/incdir_160185/xxxx313_lmd0_146293_i160185.trcUse ADRCI or Support Workbench to package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Errors in file /oracle/diag/rdbms/xxxx31/xxxx313/trace/xxxx313_lmd0_146293.trc (incident=160186):从ORA-04031报错信息看是ges resource dynamic对象分配过多导致无法分配足够大的chunk资源（13k),SQL> select inst_id,name, round(bytes/(1024*1024*1024),1) in_gb from gv$sgastat where name='ges resource dynamic';

INST_ID NAME IN_GB---------- -------------------------- ----------3 ges resource dynamic 21.21 ges resource dynamic 61.52 ges resource dynamic 15.7这里确实显示 ges resource dynamic对象占用较大内存。下面继续查看incident文件oracle@n-pc-sr850-325:[/oracle/diag/rdbms/xxxx31/xxxx313/incident/incdir_160185]pwd/oracle/diag/rdbms/xxxx31/xxxx313/incident/incdir_160185oracle@n-pc-sr850-325:[/oracle/diag/rdbms/xxxx31/xxxx313/incident/incdir_160185]ls -lrttotal 4068-rw-r----- 1 oracle oinstall 171998 Apr 6 06:05 xxxx313_lmd0_146293_i160185.trm-rw-r----- 1 oracle oinstall 3992464 Apr 6 06:05 xxxx313_lmd0_146293_i160185.trc

####################TOC00000]Jump to table of contentsDump continued from file: /oracle/diag/rdbms/xxxx31/xxxx313/trace/xxxx313_lmd0_146293.trc[TOC00001]ORA-04031: unable to allocate 13840 bytes of shared memory ("shared pool","unknown object","sga heap(5,0)","ges resource dynamic")

。。。。。。*********************** End of process map dump ****************Maximum map count configured per process: 262144[TOC00005-END]==============================================t0p 10 MEMORY USES FOR SGA HEAP SUB POOL 1----------------------------------------------"ges resource dynamic " 2746 MB 40%"ges enqueues " 1639 MB 24%"free memory " 983 MB 14%"gcs resources " 296 MB 4%"SQLA " 256 MB 4%"gcs shadows " 185 MB 3%"kglsim object batch " 102 MB 1%"db_block_hash_buckets " 102 MB 1%"KGLH0 " 97 MB 1%"gc name table " 64 MB 1%-----------------------------------------free memory 983 MBmemory alloc. 5929 MBSub total 6912 MB==============================================t0p 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 1----------------------------------------------"ges resource dynamic " 2746 MB"ges enqueues " 1639 MB"free memory " 1535 MB"SQLA " 963 MB"gcs resources " 296 MB"KGH: NO ACCESS " 256 MB"KGLH0 " 199 MB"gcs shadows " 185 MB"kglsim object batch " 102 MB"db_block_hash_buckets " 102 MB==============================================t0p 10 MEMORY USES FOR SGA HEAP SUB POOL 2----------------------------------------------"ges resource dynamic " 16 GB 72%"free memory " 3623 MB 15%"ges enqueues " 1639 MB 7%"gcs resources " 298 MB 1%"SQLA " 232 MB 1%"gcs shadows " 183 MB 1%"kglsim object batch " 103 MB 0%"db_block_hash_buckets " 101 MB 0%"kglsim heap " 53 MB 0%"Checkpoint queue " 53 MB 0%-----------------------------------------free memory 3623 MBmemory alloc. 19 GBSub total 23 GB==============================================t0p 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 2----------------------------------------------"ges resource dynamic " 16 GB"free memory " 4189 MB"SQLA " 2652 MB"ges enqueues " 1639 MB"gcs resources " 298 MB"KGH: NO ACCESS " 278 MB"KGLH0 " 210 MB"gcs shadows " 183 MB"kglsim object batch " 103 MB"db_block_hash_buckets " 101 MB==============================================t0p 10 MEMORY USES FOR SGA HEAP SUB POOL 3----------------------------------------------"ges resource dynamic " 2827 MB 44%"ges enqueues " 1640 MB 26%"free memory " 488 MB 8%"gcs resources " 300 MB 5%"SQLA " 239 MB 4%"gcs shadows " 181 MB 3%"db_block_hash_buckets " 103 MB 2%"kglsim object batch " 102 MB 2%"KGLH0 " 85 MB 1%"kglsim heap " 53 MB 1%-----------------------------------------free memory 488 MBmemory alloc. 5912 MBSub total 6400 MB==============================================t0p 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 3----------------------------------------------"ges resource dynamic " 2827 MB"ges enqueues " 1640 MB"free memory " 1031 MB"SQLA " 478 MB"gcs resources " 300 MB"KGH: NO ACCESS " 256 MB"gcs shadows " 181 MB"KGLH0 " 157 MB"db_block_hash_buckets " 103 MB"kglsim object batch " 102 MB==============================================t0p 10 MEMORY USES FOR SGA HEAP SUB POOL 4----------------------------------------------"ges resource dynamic " 2790 MB 44%"ges enqueues " 1640 MB 26%"free memory " 474 MB 7%"gcs resources " 298 MB 5%"SQLA " 198 MB 3%"gcs shadows " 180 MB 3%"kglsim object batch " 102 MB 2%"db_block_hash_buckets " 102 MB 2%"KGLH0 " 98 MB 2%"gc name table " 64 MB 1%-----------------------------------------free memory 474 MBmemory alloc. 5926 MBSub total 6400 MB==============================================t0p 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 4----------------------------------------------"ges resource dynamic " 2790 MB"ges enqueues " 1640 MB"free memory " 1015 MB"SQLA " 474 MB"gcs resources " 298 MB"KGH: NO ACCESS " 256 MB"gcs shadows " 180 MB"KGLH0 " 149 MB"kglsim object batch " 102 MB"db_block_hash_buckets " 102 MB==============================================t0p 10 MEMORY USES FOR SGA HEAP SUB POOL 5----------------------------------------------"ges resource dynamic " 16 GB 74%"free memory " 3124 MB 14%"ges enqueues " 1640 MB 7%"gcs resources " 296 MB 1%"gcs shadows " 181 MB 1%"kglsim object batch " 102 MB 0%"db_block_hash_buckets " 101 MB 0%"gc name table " 64 MB 0%"Checkpoint queue " 55 MB 0%"kglsim heap " 53 MB 0%-----------------------------------------free memory 3124 MBmemory alloc. 19 GBSub total 22 GB==============================================t0p 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 5----------------------------------------------"ges resource dynamic " 16 GB"free memory " 3380 MB"SQLA " 1926 MB"ges enqueues " 1640 MB"KGH: NO ACCESS " 323 MB"gcs resources " 296 MB"KGLH0 " 239 MB"gcs shadows " 181 MB"kglsim object batch " 102 MB"db_block_hash_buckets " 101 MB==============================================t0p 10 MEMORY USES FOR SGA HEAP SUB POOL 6----------------------------------------------"ges resource dynamic " 17 GB 80%"ges enqueues " 1639 MB 8%"free memory " 1465 MB 7%"gcs resources " 295 MB 1%"gcs shadows " 183 MB 1%"kglsim object batch " 103 MB 0%"db_block_hash_buckets " 102 MB 0%"gc name table " 64 MB 0%"Checkpoint queue " 55 MB 0%"kglsim heap " 53 MB 0%-----------------------------------------free memory 1465 MBmemory alloc. 20 GBSub total 21 GB==============================================t0p 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 6----------------------------------------------"ges resource dynamic " 17 GB"free memory " 1833 MB"ges enqueues " 1639 MB"SQLA " 473 MB"gcs resources " 295 MB"KGH: NO ACCESS " 278 MB"gcs shadows " 183 MB"KGLH0 " 159 MB"kglsim object batch " 103 MB"db_block_hash_buckets " 102 MB==============================================t0p 10 MEMORY USES FOR SGA HEAP SUB POOL 7----------------------------------------------"ges resource dynamic " 3039 MB 44%"ges enqueues " 1639 MB 24%"free memory " 943 MB 14%"gcs resources " 295 MB 4%"gcs shadows " 185 MB 3%"kglsim object batch " 103 MB 1%"db_block_hash_buckets " 101 MB 1%"keomg: entry list " 72 MB 1%"gc name table " 64 MB 1%"kglsim heap " 53 MB 1%-----------------------------------------free memory 943 MBmemory alloc. 5969 MBSub total 6912 MB==============================================t0p 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 7----------------------------------------------"ges resource dynamic " 3039 MB"ges enqueues " 1639 MB"free memory " 1048 MB"SQLA " 473 MB"gcs resources " 295 MB"KGH: NO ACCESS " 256 MB"gcs shadows " 185 MB"KGLH0 " 132 MB"kglsim object batch " 103 MB"db_block_hash_buckets " 101 MBTOTALS ---------------------------------------Total free memory 11 GBTotal memory alloc. 81 GBGrand total 92 GB这里我们看到几个子池确实占用较大内存，我们的SGA是100g ,我们通过告警 ORA-04031: unable to allocate 13840 bytes of shared memory ("shared pool","unknown object","sga heap(5,0)","ges resource dynamic")找到一篇MOS 文章还是很有启发性：Bug 27824540 - ORA-4031 Error In Shared Pool Due To Leakage Of 'ges resource dynamic' Chunk In RAC Env (Doc ID 27824540.8) 目前看是bug导致的内存泄露。

从视图gv$sgastat的统计以及incident跟踪文件分析，由于共享池的子池分配了过多的“ges resource dynamic' 对象，终导致了ORA-04031错误。目前分析看应该是bug27824540导致，目前该bug在19.1.0, 18.7.0.0.190716,12.2.0.1190716,12.2.0.1.20014(for Windows platform)中修复，而目前xxxx31的数据补丁为12.2.0.1.181016 (28662603)修复方式，Disable GES资源cache ,修改隐藏参数 "_ges_direct_free" 为true修改副作用会导致insert操作的TM锁争用。由于目前没有影响现有业务运行，建议先不通过workround方式解决，观察一段时间。

下面我们将MOS原文贴出供大家使用：

Bug 27824540 ORA-4031 Error In Shared Pool Due To Leakage Of 'ges resource dynamic' Chunk In RAC Env This note gives a brief overview of bug 27824540. The content was last updated on: 08-MAR-2022 Click here for details of each of the sections below.

Affects:Product (Component) Oracle Server (Rdbms)Range of versions believed to be affected Versions >= 12.2 but BELOW 19.1Versions confirmed as being affected 18.6.018.5.018.4.018.3.018.1.012.2.0.1 (Base Release)12.1.0.2 (Server Patch Set)Platforms affected Generic (all / most platforms affected)Fixed:The fix for 27824540 is first included in 19.1.018.7.0.0.190716 (Jul 2019) Database Jul2019 Release Update (DB RU)12.2.0.1.190716 (Jul 2019) Database Jul2019 Release Update (DB RU)12.2.0.1.200114 (Jan 2020) Bundle Patch for Windows Platforms

Interim patches may be available for earlier versions - click here to check.Symptoms:Related To:Leak (Memory Leak / Growth)Shared Pool AffectedORA-4031Memory type "ges resource dynamic"RAC (Real Application Clusters) / OPSDescriptionToo many objects "ges resource dynamic" were allocated in the shared pool eventually failing with errors like: ORA-04031

Monitoring "ges resource dynamic" growth via the following shows a general upward trend:

select inst_id, name, round(bytes/(1024*1024*1024),1) in_gb from gv$sgastat where name = 'ges resource dynamic';

- example after an instance restart:

INST_ID NAME IN_GB ---------- -------------------------- ---------- 1 ges resource dynamic .2 2 ges resource dynamic .4 3 ges resource dynamic .7

- example after several days of uptime shows upward trend :

INST_ID NAME IN_GB ---------- -------------------------- ---------- 1 ges resource dynamic 8.6 2 ges resource dynamic 13.2 3 ges resource dynamic 16.1

Note: When not getting 4031, but proactive monitoring DB performance using AWR diff reports, just check in section : "SGA Breakdown Difference", and notice the %Diff values that will show the memory leak

Rediscovery Notes

-- instance alert log shows :Errors in file //diag/rdbms///trace/_lmd1_.trc (incident=nnnnnnn):ORA-04031: unable to allocate 13840 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges resource dynamic")Incident details in: //diag/rdbms///incident/incdir_NNNNNN/_lmd1__.trc

-- incident trc file shows :

ORA-04031: unable to allocate 13840 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges resource dynamic")

============================================= t0p 10 MEMORY USES FOR SGA HEAP SUB POOL 1 ---------------------------------------------- "ges resource dynamic " 10 GB 55% "free memory " 4426 MB 25% "ges enqueues " 1204 MB 7% "gcs resources " 371 MB 2%

============================================ t0p 10 MAXIMUM MEMORY USES FOR SGA HEAP SUB POOL 1 ---------------------------------------------- "ges resource dynamic " 10 GB "SQLA " 7254 MB "free memory " 5676 MB

-- If a problem fulfills all of the conditions below, it is a duplicate of this problem.

1. Many objects "ges resource dynamic" are allocated in the shared pool.

2. Trace file of LMHB process traced Action 11 (kjgcr_GrowResourceCache) was executed, and was not resetted. ============================================================ kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth ============================================================

3. Trace file of LMHB process keep tracing failure of metric 7 (check lck heartbeat). ============================================================ kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname ISPP01, inst 2, node 2 ============================================================

Workaround There are 2 possible workarounds.

1) Disable the action 11. SQL> oradebug setorapname LMHB SQL> oradebug dyn_gcr -a 11 -disable

Note: - This oradebug command is available on 12.2 and later. - To keep it persistent across instance restarts one can do as below : $ sqlplus "/ as sysdba" SQL> alter system set "_oradebug_cmds_at_startup"='dyn_gcr -a 11 -disable' scope=spfile sid='*'; Then restart database to make change take effect.

2) Disable the GES resource cache

Set the initialization parameter "_ges_direct_free" to TRUE.

Note: - Completely disabling GES resource cache may lead to some other side affects like contention on TM lock for insert statements, so use this workaround with caution if there is still a need