成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

記一次ORA-4030報(bào)錯(cuò)分析

IT那活兒 / 931人閱讀
記一次ORA-4030報(bào)錯(cuò)分析

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了?。?！


ORA-4030是PGA的報(bào)錯(cuò)，表示Oracle服務(wù)器進(jìn)程達(dá)到的內(nèi)存限制，無法從操作系統(tǒng)獲取到足夠的內(nèi)存。

某日收到ORA-4030報(bào)錯(cuò)信息...



01


報(bào)錯(cuò)分析


ORA-4030報(bào)錯(cuò)，查看Alert日志：
Wed Mar 13 22:00:05 2019
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Wed Mar 13 22:28:00 2019
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_j002_516089.trc (incident=208929):
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208929/orcl2_j002_516089_i208929.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_j002_516089.trc (incident=208930):
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208930/orcl2_j002_516089_i208930.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208929/orcl2_j002_516089_i208929.trc:
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_j002_516089.trc (incident=208931):
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208931/orcl2_j002_516089_i208931.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Mar 13 22:28:02 2019
Dumping diagnostic data in directory=[cdmp_20190313222802], requested by (instance=2, osid=516089 (J002)), summary=[incident=208930].
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208929/orcl2_j002_516089_i208929.trc:
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Dumping diagnostic data in directory=[cdmp_20190313222804], requested by (instance=2, osid=516089 (J002)), summary=[incident=208931].
獲得報(bào)錯(cuò)信息：
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_j002_516089.trc (incident=208929):
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208929/orcl2_j002_516089_i208929.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_j002_516089.trc (incident=208930):
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
通過j002進(jìn)程發(fā)現(xiàn)是job運(yùn)行導(dǎo)致的，由于嘗試分配進(jìn)程內(nèi)存時(shí)發(fā)生錯(cuò)誤，無法從操作系統(tǒng)獲取到足夠的內(nèi)存。
a. 查看orcl2_j002_516089.trc文件：
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_j002_516089.trc
。。。。。
Instance name: orcl2
Redo thread mounted by this instance: 2
Oracle process number: 116 ------》進(jìn)程號(hào)
Unix process pid: 516089, image: oracle@orcldb06 (J002)
。。。。。
mmap(offset=239841280, len=8192) failed with errno=12 for the file ora_j002_orcl2
mmap(offset=239841280, len=8192) failed with errno=12 for the file ora_j002_orcl2
mmap(offset=239841280, len=8192) failed with errno=12 for the file ora_j002_orcl2
mmap(offset=239841280, len=8192) failed with errno=12 for the file ora_j002_orcl2
。。。。。
Incident 208929 created, dump file: /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208929/orcl2_j002_516089_i208929.trc
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
Incident 208930 created, dump file: /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208930/orcl2_j002_516089_i208930.trc
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)

Incident 208931 created, dump file: /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208931/orcl2_j002_516089_i208931.trc
ORA-04030: out of process memory when trying to allocate 824504 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 432 bytes (kxs-heap-c,kprbalo temp memory)
得知是進(jìn)程號(hào)116進(jìn)程執(zhí)行過程產(chǎn)生內(nèi)存不足的報(bào)錯(cuò)。
b. 查看orcl2_j002_516089_i208929.trc文件：
Dump file /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_208929/orcl2_j002_516089_i208929.trc
Oracle process number: 116 ---------------------進(jìn)程號(hào)
Top 10 processes:
-------------------------
(percentage is of 5276 MB total allocated memory)
78% pid 116: 3087 MB used of 4108 MB allocated <= CURRENT PROC
1% pid 56: 41 MB used of 45 MB allocated
1% pid 57: 41 MB used of 45 MB allocated
1% pid 60: 41 MB used of 45 MB allocated
1% pid 15: 33 MB used of 33 MB allocated
1% pid 16: 32 MB used of 33 MB allocated
1% pid 13: 32 MB used of 33 MB allocated
1% pid 14: 32 MB used of 33 MB allocated
1% pid 18: 32 MB used of 33 MB allocated
1% pid 17: 32 MB used of 32 MB allocated
。。。。。。
------------------------------------
Begin session detail for pid 116
sid: 1277 ser: 2099 audsid: 2720431 user: 105/TSSH
flags: (0x8010041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 116 O/S info: user: oracle, term: UNKNOWN, ospid: 516089
image: oracle@orcldb06 (J002)
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 516089
machine: orcldb06 program: oracle@orcldb06 (J002)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: ORA$AT_SQ_SQL_SW_1429, hash value=2161650343
current SQL:
/* SQL Analyze(1277,1) */ SELECT A.ROWID, B.SB_YM_NO FROM
。。。。。。
AND C.UUID IS NULL
End session detail for pid 116
。。。。。。
----- Process Map Dump -----
******************* Dumping process map ****************
00400000-0bd0a000 r-xp 00000000 fd:04 2102282 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle ------------------第一行
0bf0a000-0bf0b000 r--p 0b90a000 fd:04 2102282 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
0bf0b000-0c0fa000 rw-p 0b90b000 fd:04 2102282 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
0c0fa000-0c151000 rw-p 00000000 00:00 0
0c3ec000-0c492000 rw-p 00000000 00:00 0 [heap]
60000000-78000000 rw-s 00000000 00:0d 262149 /SYSV00000000 (deleted)
78000000-b60000000 rw-s 00000000 00:0d 294918 /SYSV00000000 (deleted)
b60000000-b60200000 rw-s 00000000 00:0d 327687 /SYSVee5f381c (deleted)
7fd3001ec000-7fd3001fc000 rw-p 00000000 00:05 2054 /dev/zero
7fd3001fc000-7fd30020c000 rw-p 00000000 00:05 2054 /dev/zero
。。。。
7fd40488b000-7fd40488c000 rw-p 00000000 00:00 0
7ffdc8e66000-7ffdc8ebb000 rw-p 00000000 00:00 0 [stack]
7ffdc8eeb000-7ffdc8eed000 r-xp 00000000 00:00 0 [vdso]
ffffffffff600000-ffffffffff601000 r-xp 00000000 00:00 0 [vsyscall] ---------------》65530行
******************* End of process map dump ************
。。。。。。
可以看出116號(hào)進(jìn)程是維護(hù)計(jì)劃中自動(dòng)sql調(diào)優(yōu)任務(wù)的進(jìn)程，服務(wù)器進(jìn)程的pga超過4G異常終止。
在trace文件中可以看到流程圖轉(zhuǎn)儲(chǔ)達(dá)到65530行的限制，由于操作系統(tǒng)中的映射條目設(shè)置每個(gè)進(jìn)程只有65530個(gè)內(nèi)存映射條目。
cat /proc/sys/vm/max_map_count
65530
數(shù)據(jù)庫(kù)默認(rèn)的realfree heap pagesize為64K(65536)，64K*65530約等于4G。

02


故障處理

可以通過修改操作系統(tǒng)內(nèi)核參數(shù)或數(shù)據(jù)庫(kù)隱含參數(shù)解決該問題：
1. 操作系統(tǒng)層面
more /proc/sys/vm/max_map_count
sysctl -w vm.max_map_count=262144
2. 修改數(shù)據(jù)庫(kù)參數(shù)

  • 11.2.0.4及更低版本：


_use_realfree_heap = TRUE
_realfree_heap_pagesize_hint = 262144


  • 12.1及更高版本：


_use_realfree_heap = TRUE
_realfree_heap_pagesize = 262144



end



本文作者：談龍鳳

本文來源：IT那活兒（上海新炬王翦團(tuán)隊(duì)）

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/129651.html

相關(guān)文章

  • Webpack下莫名其妙出現(xiàn)的jQuery與報(bào)錯(cuò)，記一次奇妙的Debug旅程

    摘要：在過程中，發(fā)現(xiàn)的報(bào)錯(cuò)是在中兩個(gè)頁(yè)面的無刷切換中出現(xiàn)的?？聪蚓W(wǎng)址等等網(wǎng)址的前綴是，這個(gè)是谷歌瀏覽器插件的前綴。難不成，這個(gè)文件是谷歌瀏覽器插件的于是看向了中間的那一串神秘字符串。 場(chǎng)景重現(xiàn) 項(xiàng)目是一個(gè)SPA，使用了Vue+Vue-Router+Webpack+jQuery。報(bào)錯(cuò)的場(chǎng)景如下：showImg(http://7xk109.com1.z0.glb.clouddn.com/blog...

    Scliang 評(píng)論0 收藏0

  • 記一次PHP級(jí)別報(bào)錯(cuò)的處理方法

    摘要：里的并不是萬能的，因?yàn)樗荒軌虿东@異常，而不能夠捕獲級(jí)別的報(bào)錯(cuò)。如果想捕獲級(jí)的報(bào)錯(cuò)，并且像異常處理一樣，做法如下報(bào)錯(cuò)嘗試獲得結(jié)果參考本站的一個(gè)問答 php里的 try{}catch(Exception $e){} 并不是萬能的，因?yàn)樗荒軌虿东@異常，而不能夠捕獲PHP級(jí)別的報(bào)錯(cuò)。 如果想捕獲PHP級(jí)的報(bào)錯(cuò)，并且像異常處理一樣，做法如下： set_error_handler(func...

    chinafgj 評(píng)論0 收藏0

  • 記一次tornado QPS 優(yōu)化

    摘要：初步分析提升可從兩方面入手，一個(gè)是增加并發(fā)數(shù)，其二是減少平均響應(yīng)時(shí)間。大部分的時(shí)間花在系統(tǒng)與數(shù)據(jù)庫(kù)的交互上，到這，便有了一個(gè)優(yōu)化的主題思路最大限度的降低平均響應(yīng)時(shí)間。不要輕易否定一項(xiàng)公認(rèn)的技術(shù)真理，要拿數(shù)據(jù)說話。 本文最早發(fā)表于個(gè)人博客：PylixmWiki 應(yīng)項(xiàng)目的需求，我們使用tornado開發(fā)了一個(gè)api系統(tǒng)，系統(tǒng)開發(fā)完后，在8核16G的虛機(jī)上經(jīng)過壓測(cè)qps只有200+。與我們當(dāng)...

    Doyle 評(píng)論0 收藏0

  • 記一次Nginx－Primary script unknown的報(bào)錯(cuò)解決

    摘要：報(bào)錯(cuò)在的中遭到定義腳本文件的地方修改成如下方式代表當(dāng)前請(qǐng)求在指令中指定的值上面配置中的就是針對(duì)目錄下的文件進(jìn)行解析。 報(bào)錯(cuò)： [error] 12691#0: *6 FastCGI sent in stderr: Primary script unknown while reading response header from upstream, client: 192.168.168...

    graf 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<