暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 记一次ORA-4030报错分析

记一次ORA-4030报错分析

IT那活儿 2022-02-27
1145

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！


ORA-4030是PGA的报错，表示Oracle服务器进程达到的内存限制，无法从操作系统获取到足够的内存。

某日收到ORA-4030报错信息...



01

报错分析

ORA-4030报错，查看Alert日志：
Wed Mar 13 22:00:05 2019Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"Wed Mar 13 22:28:00 2019Errors 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.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 /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.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 /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.trcUse 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 2019Dumping 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.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 /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: orcl2Redo thread mounted by this instance: 2Oracle 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_orcl2mmap(offset=239841280, len=8192) failed with errno=12 for the file ora_j002_orcl2mmap(offset=239841280, len=8192) failed with errno=12 for the file ora_j002_orcl2mmap(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.trcORA-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.trcORA-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.trcORA-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.trcOracle 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 PROC1% pid 56: 41 MB used of 45 MB allocated1% pid 57: 41 MB used of 45 MB allocated1% pid 60: 41 MB used of 45 MB allocated1% pid 15: 33 MB used of 33 MB allocated1% pid 16: 32 MB used of 33 MB allocated1% pid 13: 32 MB used of 33 MB allocated1% pid 14: 32 MB used of 33 MB allocated1% pid 18: 32 MB used of 33 MB allocated1% pid 17: 32 MB used of 32 MB allocated。。。。。。------------------------------------Begin session detail for pid 116sid: 1277 ser: 2099 audsid: 2720431 user: 105/TSSHflags: (0x8010041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-flags2: (0x40009) -/-/INCpid: 116 O/S info: user: oracle, term: UNKNOWN, ospid: 516089image: oracle@orcldb06 (J002)client details:O/S info: user: oracle, term: UNKNOWN, ospid: 516089machine: orcldb06 program: oracle@orcldb06 (J002)application name: DBMS_SCHEDULER, hash value=2478762354action name: ORA$AT_SQ_SQL_SW_1429, hash value=2161650343current SQL:/* SQL Analyze(1277,1) */ SELECT A.ROWID, B.SB_YM_NO FROM。。。。。。AND C.UUID IS NULLEnd 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/oracle0bf0b000-0c0fa000 rw-p 0b90b000 fd:04 2102282 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle0c0fa000-0c151000 rw-p 00000000 00:00 00c3ec000-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/zero7fd3001fc000-7fd30020c000 rw-p 00000000 00:05 2054 /dev/zero。。。。7fd40488b000-7fd40488c000 rw-p 00000000 00:00 07ffdc8e66000-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_count65530
数据库默认的realfree heap pagesize为64K(65536)，64K*65530约等于4G。

02

故障处理

可以通过修改操作系统内核参数或数据库隐含参数解决该问题：
1. 操作系统层面
more /proc/sys/vm/max_map_countsysctl -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那活儿（上海新炬王翦团队）

oracle
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论