国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

Oracle數據庫4031故障分析報告

IT那活兒 / 2416人閱讀
Oracle數據庫4031故障分析報告

點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！

一

問題描述

數據庫業務出現超時，數據庫出現enq: TX - index contention等等待事件。

二

問題分析

數據庫出現enq: TX - index contention等待：
通過查看問題前的ash，發現在數據庫異常之前，數據庫的主要等待事件是：SGA: allocation forcing component growth。
Alert.log：
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.
通過ash趨勢和alert.log發現，數據庫在出現enq: TX - index contention之前，數據庫主要等待是SGA: allocation forcing component growth和library cache lock，并且alert.log從0點開始出現ora-4031持續到異常時段。
查看數據庫內存使用情況：
Buffer cache的內存非常小，只有128m，而shared pool占用了32G。
Shared pool中ges resource dynamic和ges enqueues分別占用了12G和8.9G，這通常是不正常的。說明由于這兩個組件占用了過多的內存，導致數據庫的ORA-4031，進而出來sga resize，進而影響了數據庫的整體性能。
分析lmhb.trc：
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
參考文檔：Error ORA-04031 in the Shared Pool with Huge Allocation in Memory Type "ges resource dynamic" or "ges enqueues" memory" (Doc ID 2631592.1)
For Oracle Versions >= 12.2 but BELOW 19.1
Bug:26405036 - VERY HIGH "GES ENQUEUES" ON THE SHARED POOL
If 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).
認為命中Bug:26405036 - VERY HIGH "GES ENQUEUES" ON THE SHARED POOL和Bug:27824540 - ORA-04031 ("SHARED POOL","UNKNOWN OBJECT","SGA HEAP(1,0)","GES RESOURCE DYNAMIC"。
其中Bug:26405036 fix在12.2.0.1.200114  ，Bug:27824540 fix在12.2.0.1.190716。
對應的解決方案是：
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.
三

問題總結

數據庫由于shared pool中ges resource dynamic和ges enqueues占用內存過多，shared pool不夠用引起ora-4031和sga resize，進而影響了數據庫整體性能。
通過重啟實例后，ges resource dynamic空間釋放，但ges enqueues還有8g，目前看已經緩解問題。最終建議通過補丁解決。

建 議：

oracle 12.2已過支持期，但基于hp-ux平臺oracle 12.2.0.1.180417的26405036、27824540的merge patch不存在并且可能無法申請出來。

  • 建議1：只安裝補丁26405036 - VERY HIGH "GES ENQUEUES" ON THE SHARED POOL看問題是否解決。
  • 建議2：升級RU到12.2.0.1.200114以上版本，已修復以上問題。
  • 建議3：嘗試使用bug中的workaround或重啟實例做為臨時的解決方案：
SQL> oradebug setmypid
SQL> oradebug lkdebug -m reconfig lkdebug
優先建議使用建議1，小補丁窗口時間短并且可以rolling。


本文作者：張 芳(上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129144.html

相關文章

  • Oracle數據庫4031故障分析

    Oracle數據庫4031故障分析 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; m...

    不知名網友 評論0 收藏2316

  • 大數據面臨的挑戰：當大數據遭遇云計算

    摘要：在未來十年，管理數據倉庫的服務器的數量將增加倍以便迎合倍的大數據增長。毫無疑問，大數據將挑戰企業的存儲架構及數據中心基礎設施等，也會引發云計算數據倉庫數據挖掘商業智能等應用的連鎖反應。 大數據正在徹底改變IT世界。那么，什么樣的數據談得上數據呢? ? 根據IDC的報告，未來十年全球大數據將增加50倍。僅在2011年，我們就將看到1.8ZB(也就是1.8萬億GB)的大數據創建產生。這相當...

    forsigner 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<