記一次ORA-4030報錯分析

IT那活兒發布于2023-01-11 13:20 / 901人閱讀

記一次ORA-4030報錯分析

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

ORA-4030是PGA的報錯，表示Oracle服務器進程達到的內存限制，無法從操作系統獲取到足夠的內存。

某日收到ORA-4030報錯信息...

01

報錯分析

ORA-4030報錯，查看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].

獲得報錯信息：

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進程發現是job運行導致的，由于嘗試分配進程內存時發生錯誤，無法從操作系統獲取到足夠的內存。

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 ------》進程號
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)

得知是進程號116進程執行過程產生內存不足的報錯。

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 ---------------------進程號
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號進程是維護計劃中自動sql調優任務的進程，服務器進程的pga超過4G異常終止。

在trace文件中可以看到流程圖轉儲達到65530行的限制，由于操作系統中的映射條目設置每個進程只有65530個內存映射條目。

cat /proc/sys/vm/max_map_count
65530

數據庫默認的realfree heap pagesize為64K(65536)，64K*65530約等于4G。

02

故障處理

可以通過修改操作系統內核參數或數據庫隱含參數解決該問題：

1. 操作系統層面

more /proc/sys/vm/max_map_count
sysctl -w vm.max_map_count=262144

2. 修改數據庫參數

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那活兒（上海新炬王翦團隊）

云服務器 GPU云服務器記一次在阿里云搭建服務器記一次阿里云服務器集體中毒談一次解一次

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

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

Webpack下莫名其妙出現的jQuery與報錯，記一次奇妙的Debug旅程

摘要：在過程中，發現的報錯是在中兩個頁面的無刷切換中出現的。看向網址等等網址的前綴是，這個是谷歌瀏覽器插件的前綴。難不成，這個文件是谷歌瀏覽器插件的于是看向了中間的那一串神秘字符串。場景重現項目是一個SPA，使用了Vue+Vue-Router+Webpack+jQuery。報錯的場景如下：showImg(http://7xk109.com1.z0.glb.clouddn.com/blog...

Scliang 2019-08-19 17:13 評論0 收藏0
記一次PHP級別報錯的處理方法

摘要：里的并不是萬能的，因為他只能夠捕獲異常，而不能夠捕獲級別的報錯。如果想捕獲級的報錯，并且像異常處理一樣，做法如下報錯嘗試獲得結果參考本站的一個問答 php里的 try{}catch(Exception $e){} 并不是萬能的，因為他只能夠捕獲異常，而不能夠捕獲PHP級別的報錯。如果想捕獲PHP級的報錯，并且像異常處理一樣，做法如下： set_error_handler(func...

chinafgj 2019-06-27 13:40 評論0 收藏0
記一次tornado QPS 優化

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

Doyle 2019-07-30 14:30 評論0 收藏0
記一次Nginx－Primary script unknown的報錯解決

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

graf 2019-07-25 13:40 評論0 收藏0