问题描述
数据库业务出现超时,数据库出现enq: TX - index contention等等待事件。
问题分析
2022-10-03T00:16:41.029338+08:00
Errors in file /u01/app/oracle/diag/rdbms/*/trace/db2_ora_6519.trc
ORA-04031: unable to allocate 63496 bytes of shared memory ("shared pool","DBMS_BACKUP_RESTORE","PLMCD^a5f238a0","BAMIMA: Bam Buffer")
Incident details in: /u01/app/oracle/diag/rdbms/*/incident/incdir_218692/db2_ora_6519_i218692.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-10-03T00:16:41.321084+08:00
ORA-04031 heap dump being written to trace file /u01/app/oracle/diag/rdbms/*/incident/incdir_218692/db2_ora_6519_i218692.trc
2022-10-03T00:19:38.948488+08:00
Errors in file /u01/app/oracle/diag/rdbms/*/trace/b2_m000_8030.trc (incident=218693):
ORA-04031: unable to allocate 57768 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer")
Incident details in: /u01/app/oracle/diag/rdbms/*/incident/incdir_218693/b2_m000_8030_i218693.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-10-03T00:19:42.233916+08:00
Errors in file /u01/app/oracle/diag/rdbms/*/trace/db2_ora_8166.trc (incident=218700):
ORA-04031: unable to allocate 63496 bytes of shared memory ("shared pool","DBMS_BACKUP_RESTORE","PLMCD^a5f238a0","BAMIMA: Bam Buffer")
Incident details in: /u01/app/oracle/diag/rdbms/*/incident/incdir_218700/db2_ora_8166_i218700.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
…
2022-10-03T04:23:42.937853+08:00
Errors in file /u01/app/oracle/diag/rdbms/*/trace/db2_lmd0_7330.trc (incident=215852):
ORA-04031: unable to allocate 13840 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges resource dynamic")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-10-03T04:23:49.345977+08:00
Errors in file /u01/app/oracle/diag/rdbms/*/trace/db2_lmd0_7330.trc (incident=215853):
ORA-04031: unable to allocate 13840 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","ges resource dynamic")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
…
2022-10-03T08:58:07.793554+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2022-10-03T09:08:09.957747+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2022-10-03T09:18:12.005452+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2022-10-03T09:28:12.835118+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
022-10-03T09:38:13.206769+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2022-10-03T09:44:00.615729+08:00
Thread 2 advanced to log sequence 29428 (LGWR switch)
Current log# 8 seq# 29428 mem# 0: +DG_DATA_SSD_1/N*/ONLINELOG/group_8.276.1008547021
Current log# 8 seq# 29428 mem# 1: +DG_DATA_SSD_1/N*/ONLINELOG/group_8.277.1008547025
2022-10-03T09:44:01.334950+08:00
TT03: Standby redo logfile selected for thread 2 sequence 29428 for destination LOG_ARCHIVE_DEST_2
2022-10-03T09:44:03.799630+08:00
Archived Log entry 119834 added for T-2.S-29427 ID 0x79a6de54 LAD:1
2022-10-03T09:48:17.418439+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2022-10-03T09:58:19.513479+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2022-10-03T10:08:21.022510+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2022-10-03T10:14:50.702688+08:00
LCK1 (ospid: 7342) waits for event libcache interrupt action by LCK for 73 secs.
2022-10-03T10:14:50.702867+08:00
LCK1 (ospid: 7342) is hung in an acceptable location (libcache 0x41.02).
2022-10-03T10:17:11.454316+08:00
LCK1 (ospid: 7342) waits for event libcache interrupt action by LCK for 214 secs.
2022-10-03T10:17:11.454494+08:00
LCK1 (ospid: 7342) is hung in an acceptable location (libcache 0x41.02).
2022-10-03T10:18:23.645094+08:00
DDE: Problem Key ORA 4031 was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
2022-10-03T10:21:42.845553+08:00
LCK1 (ospid: 7342) waits for event libcache interrupt action by LCK for 79 secs.
2022-10-03T10:21:42.845731+08:00
LCK1 (ospid: 7342) is hung in an acceptable location (libcache 0x41.02).
2022-10-03T10:24:20.692124+08:00
LCK1 (ospid: 7342) waits for event libcache interrupt action by LCK for 80 secs.
2022-10-03T10:24:20.692681+08:00
LCK1 (ospid: 7342) is hung in an acceptable location (libcache 0x41.02).
2022-10-03T10:27:57.458418+08:00
CKPT (ospid: 7350) waits for event enq: XR - database force logging for 78 secs.
2022-10-03T10:28:07.628285+08:00
CKPT (ospid: 7350) waits for event enq: XR - database force logging for 88 secs.
2022-10-03T10:28:17.668429+08:00
CKPT (ospid: 7350) waits for event enq: XR - database force logging for 98 secs.
grep "library cache pin wait check" n*2_lmhb_7340.trc
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 succeed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 failed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 succeed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 failed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 succeed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 failed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 succeed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 failed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 succeed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 failed
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric library cache pin wait check, id 11 succeed
…
grep "check lck heartbeat" n*2_lmhb_7340.trc
kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname N*, inst 2, node 2
kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname N*, inst 2, node 2
kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname N*, inst 2, node 2
kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname N*, inst 2, node 2
kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname N*, inst 2, node 2
kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname N*, inst 2, node 2
kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname N*, inst 2, node 2
kjgcr_ChkGlobalMetric: metric 7 (check lck heartbeat) failed for dbname N*, inst 2, node 2
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
kjgcr_GrowResourceCache: LCP waits high, allowing res cache growth
For Oracle Versions >= 12.2 but BELOW 19.1
Bug:26405036 - VERY HIGH "GES ENQUEUES" ON THE SHARED POOLIf a problem fulfills all of the condition below, it is a duplicate of this problem.
1. The LMHB process keeps reporting "memory load check" failure.
[Example]
============================================================
kjgcr_StatCheckMEM: memory is low, free memory 18%, average 19%
kjgcr_ServiceGCR: KJGCR_METRICS: Local metric memory load check, id 10 failed
============================================================
2. The LMHB process reported "library cache pin wait check" failure.
[Example]
============================================================
kjgcr_ServiceGCR: KJGCR_METRICS: Global metric library cache pin wait check, id 11 failed
============================================================
3. The heap dump of the shared pool contains many object of description "ges resource dynamic".
Bug:27824540 - ORA-04031 ("SHARED POOL","UNKNOWN OBJECT","SGA HEAP(1,0)","GES RESOURCE DYNAMIC"
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).
For Oracle Versions >= 12.2 but BELOW 19.1
Apply fix for Bug:26405036; see NOTE:26405036.8
Workaround: on R12.2 or above, Start pseudo reconfiguration by below command is workaround.
SQL> oradebug setmypid
SQL> oradebug lkdebug -m reconfig lkdebug
Apply fix for Bug:27824540; see NOTE:27824540.8
Workaround: There are 2 possible workarounds.
1) Disable the action 11.
SQL> oradebug dyn_gcr -a 11 -disable
Note: This oradebug command is available on 12.2 and later.
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.
问题总结
建 议:
oracle 12.2已过支持期,但基于hp-ux平台oracle 12.2.0.1.180417的26405036、27824540的merge patch不存在并且可能无法申请出来。
只安装补丁26405036 - VERY HIGH "GES ENQUEUES" ON THE SHARED POOL看问题是否解决。
升级RU到12.2.0.1.200114以上版本,已修复以上问题。
SQL> oradebug setmypid
SQL> oradebug lkdebug -m reconfig lkdebug
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129090.html
...间通信过程图 客户端输入sql语句,sql语句通过网络到达数据库实例,server process接受sql语句。Server process接受到SQL语句后将sql语句解析成执行计划,然后才能执行。需要说明的是在将SQL语句解析成执行计划的过程中会消耗计算机...
...间通信过程图 客户端输入sql语句,sql语句通过网络到达数据库实例,server process接受sql语句。Server process接受到SQL语句后将sql语句解析成执行计划,然后才能执行。需要说明的是在将SQL语句解析成执行计划的过程中会消耗计算机...
...照生产库对比的情况 控制文件的定义及引入的目的 Oracle数据库的控制文件是数据库中极其重要的文件,该文件是一个比较小的二进制文件,它记载了物理数据库的当前状态,每一个控制文件只属于一个数据库,但为了防止控制...
...伪无用的数据,是数据挖掘技术的最重要的应用。传统的数据库中的数据结构性很强,即其中的数据为完全结构化的数据,而目前数据较大特点就是半结构化,因此此类数据挖掘比面向单个数据仓库的数据挖掘要复杂得多。 ...
...伪无用的数据,是数据挖掘技术的最重要的应用。传统的数据库中的数据结构性很强,即其中的数据为完全结构化的数据,而目前数据 较大特点就是半结构化,因此此类数据挖掘比面向单个数据仓库的数据挖掘要复杂得多。 ...
阅读 2787·2023-01-11 11:02
阅读 3329·2023-01-11 11:02
阅读 1853·2023-01-11 11:02
阅读 3589·2023-01-11 11:02
阅读 3082·2023-01-11 11:02
阅读 3527·2023-01-11 11:02
阅读 3227·2023-01-11 11:02
阅读 1576·2023-01-11 11:02