来自ORACLE 19.12的一个JSON_TABLE的BUG:ORA-07445 [kauxalo]

郑全 · 发表于 2023-7-31 21:57:20

本帖最后由郑全于 2023-7-31 22:05 编辑

现象：
一用户提示说最近老是比较频繁的报错，让看看是什么问题，我也是郁闷，最近没有听说什么问题吧，于是登录到用户端，看到对方发过来的前端错误：

error.png (399.89 KB, 下载次数: 335)

2023-7-31 21:28 上传

   一看报错信息，看到ORA-12537，比较常见的错误

分析：
   网上搜了一下，大部分都是这个说法：
   ORA-12537 TNS:连接关闭错误是Oracle数据库出现的一种错误，通常是由于网络通信问题引起的。该错误发生时，表示数据库在进行网络通信时，连接被意外终止或关闭，导致无法访问数据库。可能的原因包括网络故障、防火墙阻止连接、数据库服务器重启或关闭等等，用户可以参考以下解决方法：
1. 检查网络连接是否可用，包括防火墙、路由器、交换机等设备是否正常工作。
2. 检查数据库服务器是否可连接，可以使用ping命令测试是否可以ping通。
3. 如果数据库服务器在重新启动或关闭后发生错误，请确保数据库服务已经完全启动，可以使用lsnrctl status命令查看监听器是否正在运行。
4. 检查TNSNAMES.ORA文件中的连接字符串是否正确，如果没有找到目标地址，则可能会引发ORA-12537错误。
5. 如果以上步骤无法解决问题，可以尝试重新安装Oracle客户端或数据库软件，确保软件版本与操作系统版本兼容。

总之，ORA-12537错误是一种常见的网络通信错误，用户需要仔细检查网络连接、配置文件和服务状态等因素，才能有效解决问题。

简单的看了一下，尝试连接，也没有问题，以前都好好的，怎么不行了呢，检查数据库监听，数据库等待事件，也没有发现什么意外，于是检查报警文件，发现报警文件一直不停的再报以下错误：

2023-07-28T12:45:12.680935+08:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0x3812331, kauxalo()+353] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/webemr/webemr/trace/webemr_ora_28860.trc  (incident=165170):
ORA-07445: 出现异常错误: 核心转储 [kauxalo()+353] [SIGSEGV] [ADDR:0x4] [PC:0x3812331] [Address not mapped to object] []
Incident details in: /u01/app/oracle/diag/rdbms/webemr/webemr/incident/incdir_165170/webemr_ora_28860_i165170.trc
。。。

查看跟踪文件：
[oracle@webemr trace]$ cat /u01/app/oracle/diag/rdbms/webemr/webemr/trace/webemr_ora_28860.trc

Trace file /u01/app/oracle/diag/rdbms/webemr/webemr/trace/webemr_ora_28860.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label: RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME: /u01/app/oracle/product/19.0.0/dbhome_1
System name:       Linux
Node name:       webemr
Release:       3.10.0-1160.el7.x86_64
Version:       #1 SMP Mon Oct 19 16:18:59 UTC 2020
Machine:       x86_64
Instance name: webemr
Redo thread mounted by this instance: 1
Oracle process number: 285
Unix process pid: 28860, image: oracle@webemr

*** 2023-07-28T12:45:12.680722+08:00
*** SESSION ID:(1401.61510) 2023-07-28T12:45:12.680772+08:00
*** CLIENT ID:() 2023-07-28T12:45:12.680780+08:00
*** SERVICE NAME:(webemr) 2023-07-28T12:45:12.680784+08:00
*** MODULE NAME:(w3wp.exe) 2023-07-28T12:45:12.680790+08:00
*** ACTION NAME:() 2023-07-28T12:45:12.680795+08:00
*** CLIENT DRIVER:(ODPC.NET : 21.1.0.0.0) 2023-07-28T12:45:12.680799+08:00

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0x3812331, kauxalo()+353] [flags: 0x0, count: 1]
2023-07-28T12:45:12.736073+08:00
Incident 165170 created, dump file: /u01/app/oracle/diag/rdbms/webemr/webemr/incident/incdir_165170/webemr_ora_28860_i165170.trc
ORA-07445: 出现异常错误: 核心转储 [kauxalo()+353] [SIGSEGV] [ADDR:0x4] [PC:0x3812331] [Address not mapped to object] []

ssexhd: crashing the process...
Shadow_Core_Dump = partial
ksdbgcra: writing core file to directory '/u01/app/oracle/diag/rdbms/webemr/webemr/cdump'
[oracle@webemr trace]$

继续看 webemr_ora_28860_i165170.trc

[oracle@webemr trace]$ more /u01/app/oracle/diag/rdbms/webemr/webemr/incident/incdir_165170/webemr_ora_28860_i165170.trc
Dump file /u01/app/oracle/diag/rdbms/webemr/webemr/incident/incdir_165170/webemr_ora_28860_i165170.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label: RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME: /u01/app/oracle/product/19.0.0/dbhome_1
System name:       Linux
Node name:       webemr
Release:       3.10.0-1160.el7.x86_64
Version:       #1 SMP Mon Oct 19 16:18:59 UTC 2020
Machine:       x86_64
Instance name: webemr
Redo thread mounted by this instance: 1
Oracle process number: 285
Unix process pid: 28860, image: oracle@webemr

*** 2023-07-28T12:45:12.736338+08:00
*** SESSION ID:(1401.61510) 2023-07-28T12:45:12.736357+08:00
*** CLIENT ID:() 2023-07-28T12:45:12.736363+08:00
*** SERVICE NAME:(webemr) 2023-07-28T12:45:12.736367+08:00
*** MODULE NAME:(w3wp.exe) 2023-07-28T12:45:12.736373+08:00
*** ACTION NAME:() 2023-07-28T12:45:12.736378+08:00
*** CLIENT DRIVER:(ODPC.NET : 21.1.0.0.0) 2023-07-28T12:45:12.736382+08:00

[TOC00000]
Jump to table of contents
Dump continued from file: /u01/app/oracle/diag/rdbms/webemr/webemr/trace/webemr_ora_28860.trc
[TOC00001]
ORA-07445: 出现异常错误: 核心转储 [kauxalo()+353] [SIGSEGV] [ADDR:0x4] [PC:0x3812331] [Address not mapped to object] []

[TOC00001-END]
[TOC00002]
========= Dump for incident 165170 (ORA 7445 [kauxalo]) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
Dumping swap information
Memory (Avail / Total) = 717.93M / 64261.73M
Swap (Avail / Total) = 32256.00M /  32256.00M
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0x3812331, kauxalo()+353] [flags: 0x0, count: 1]
Registers:
%rax: 0x0000000000000000 %rbx: 0x0000000000000000 %rcx: 0x00000000ca29d360
%rdx: 0x000000000000000a %rdi: 0x00007f30804bd9c0 %rsi: 0x0000000000000000
%rsp: 0x00007ffd3bbd7b40 %rbp: 0x00007ffd3bbd7ba0  %r8: 0x0000000000000000
%r9: 0x0000000000002000 %r10: 0x0000000000000260 %r11: 0x00000000ca29d360
%r12: 0x00007f308050e140 %r13: 0x0000000151b23320 %r14: 0x00000000ca29d938
%r15: 0x00000001538b06e8 %rip: 0x0000000003812331 %efl: 0x0000000000010246
  kauxalo()+333 (0x381231d) movzbl %bl,%edx
  kauxalo()+336 (0x3812320) movzww 0x172(%r15,%rdx,2),%edx
  kauxalo()+345 (0x3812329) mov 0x10(%r13),%rcx
  kauxalo()+349 (0x381232d) mov (%r14,%rdx,8),%rsi
> kauxalo()+353 (0x3812331) movzww 0x4(%rsi),%edx
  kauxalo()+357 (0x3812335) dec %edx
  kauxalo()+359 (0x3812337) mov %dx,(%rcx,%rax,2)
  kauxalo()+363 (0x381233b) cmpb $0xd0,0xa(%rsi)
  kauxalo()+367 (0x381233f) je 0x381245b

*** 2023-07-28T12:45:12.738778+08:00
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
[TOC00004]
----- Current SQL Statement for this session (sql_id=5qjd66fx9qq0s) -----
insert into pending_activity(PENDING_ACTIVITY_ID,
                                                      ACTIVITY_ID,
                                                      ACTIVITY_RECORD_ID,
                                                      PATIENT_ID,
                                                      ENCOUNTER_TYPE,
                                                      ENCOUNTER_ID,
                                                      CREATE_TIME,BEYOND_TIME)
      select PENDING_ACTIVITY_ID ,
            ACTIVITY_ID,
            ACTIVITY_RECORD_ID,
            PATIENT_ID,
            ENCOUNTER_TYPE,
            ENCOUNTER_ID,
            sysdate,case when BEYOND_TIME is null or BEYOND_TIME ='0' then null else to_date(BEYOND_TIME,'yyyy-mm-dd hh24:mi:ss') end
         from json_table(:pendingActivityList,'$'
         columns(PENDING_ACTIVITY_ID VARCHAR2(36) path '$.PENDING_ACTIVITY_ID',
                  ACTIVITY_ID VARCHAR2(36) path '$.ACTIVITY_ID',
                  ACTIVITY_RECORD_ID VARCHAR2(36) path '$.ACTIVITY_RECORD_ID',
                  PATIENT_ID VARCHAR2(36) path '$.PATIENT_ID',
                  ENCOUNTER_TYPE NUMBER(1) path '$.ENCOUNTER_TYPE',
                  ENCOUNTER_ID VARCHAR2(36) path '$.ENCOUNTER_ID',
                  BEYOND_TIME VARCHAR2(36) path '$.BEYOND_TIME'))
[TOC00004-END]

---- Call Stack Trace -----
calling             call    entry             argument values in hex
location          type    point             (? means dubious value)
-------------------- -------- -------------------- ----------------------------

*** 2023-07-28T12:45:12.964860+08:00
ksedst1()+95       call    kgdsdst()          7F307B7D3CF0 000000003
                                                7F307B7CE020 ? 7F307B7CE138 ?
                                                000000000 000000083 ?
ksedst()+58       call    ksedst1()          000000001 000000001
                                                7F307B7CE020 ? 7F307B7CE138 ?
                                                000000000 ? 000000083 ?
dbkedDefDump()+2344  call    ksedst()          000000001 000000001 ?
8                                                 7F307B7CE020 ? 7F307B7CE138 ?
                                                000000000 ? 000000083 ?
ksedmp()+577       call    dbkedDefDump()    000000003 000000003
                                                7F307B7CE020 ? 7F307B7CE138 ?
                                                000000000 ? 000000083 ?
ssexhd()+2859       call    ksedmp()          00000044F 000000003 ?
                                                7F307B7CE020 ? 7F307B7CE138 ?
                                                000000000 ? 000000083 ?
sslssSynchHdlr()+41  call    ssexhd()          00000000B 000000000
1                                                 7F307B7D54B0 7F307B7D5380
                                                7F307B7D52F0 000000083 ?
sslsshandler()+94 call    sslssSynchHdlr()    000002000 000000000 000000000
                                                7F307B7D5380 ? 7F307B7D52F0 ?
                                                000000083 ?
__sighandler()    call    sslsshandler()    000000000 7F307B7D54B0
                                                7F307B7D5380 7F307B7D5380 ?
                                                7F307B7D52F0 ? 000000083 ?
kauxalo()+353       signal __sighandler()    7F30804BD9C0 000000000
                                                00000000A 0CA29D360 ?
                                                000000000 ? 000002000 ?
insLoadIndexes()+28  call    kauxalo()          1538B06E8 ? 077D1B440
4                                                 0CA29D938 ? 0CA29D4B8
                                                151B23320 ? 1538B06E8
inspreCG()+1425    call    insLoadIndexes()    077D1BDF0 ? 077D1B440 ?
                                                0CA29D9A8 ? 0CA29D4B8 ?
                                                151B23320 ? 1538B06E8 ?
opitca()+10800    call    inspreCG()          077D1B7F8 ? 7F307B565298 ?
                                                0CA29D9A8 ? 0CA29D4B8 ?
                                                151B23320 ? 1538B06E8 ?
kksFullTypeCheck()+  call    opitca()          7F307AA14218 077D1BDF0
81                                              7FFD3BBD9F60 0CA29D4B8 ?
                                                151B23320 ? 1538B06E8 ?
rpiswu2()+2004    call    kksFullTypeCheck() 7FFD3BBD91F0 ? 000000000
                                                7FFD3BBD9F60 ? 0CA29D4B8 ?
                                                151B23320 ? 1538B06E8 ?
kksSetBindType()+17  call    rpiswu2()          7FFD3BBD91F0 ? 000000000 ?
83                                              7FFD3BBD9F60 ? 0CA29D4B8 ?
                                                7F308050E140 ? 1538B06E8 ?
kksfbc()+14323    call    kksSetBindType()    7FFD3BBD91F0 ? 7F307AA14218
                                                7FFD3BBD9F60 ? 000000102
                                                7F308050E140 ? 1538B06E8 ?
opiexe()+2821       call    kksfbc()          7F307AA14218 7F307AA14218 ?
                                                7FFD3BBD9F60 ? 000000102 ?
                                                7F308050E140 ? 1538B06E8 ?
kpoal8()+2226       call    opiexe()          000000049 7F307AA14218 ?
                                                7FFD3BBDB6B0 000000102 ?
                                                7F308050E140 ? 1538B06E8 ?
opiodr()+1202       call    kpoal8()          00000005E 000000026
                                                7FFD3BBDF130 000000102 ?
                                                7F308050E140 ? 1538B06E8 ?
ttcpip()+1231       call    opiodr()          00000005E 000000026
                                                7FFD3BBDF130 ? 000000000
                                                7F308050E140 ? 1538B06E8 ?
opitsk()+1900       call    ttcpip()          7F30804E7B30 ? 000000026 ?
                                                7FFD3BBDF130 000000000 ?
                                                7FFD3BBDEB90 7FFD3BBDF380 ?
opiino()+936       call    opitsk()          000000000 000000000
                                                7FFD3BBDF130 ? 000000000 ?
                                                7FFD3BBDEB90 ? 7FFD3BBDF380 ?
opiodr()+1202       call    opiino()          00000003C 000000004
                                                7FFD3BBE0D18 000000000 ?
                                                7FFD3BBDEB90 ? 7FFD3BBDF380 ?
opidrv()+1094       call    opiodr()          00000003C 000000004
                                                7FFD3BBE0D18 ? 000000000
                                                7FFD3BBDEB90 ? 7FFD3BBDF380 ?
sou2o()+165       call    opidrv()          00000003C 000000004
                                                7FFD3BBE0D18 000000000 ?
                                                7FFD3BBDEB90 ? 7FFD3BBDF380 ?
opimai_real()+422 call    sou2o()             7FFD3BBE0C31 00000003C
                                                000000004 7FFD3BBE0D18
                                                7FFD3BBDEB90 ? 7FFD3BBDF380 ?
ssthrdmain()+417    call    opimai_real()       000000000 7FFD3BBE1508
                                                000000004 ? 7FFD3BBE0D18 ?
                                                7FFD3BBDEB90 ? 7FFD3BBDF380 ?
main()+256          call    ssthrdmain()       000000000 000000002
                                                7FFD3BBE1508 000000001
                                                000000000 7FFD3BBDF380 ?
__libc_start_main()  call    main()             000000002 7FFD3BBE16E8
+245                                              7FFD3BBE1508 ? 000000001 ?
                                                000000000 ? 7FFD3BBDF380 ?
_start()+41       call    __libc_start_main()  000DC04F0 000000002
                                                7FFD3BBE16E8 000000000 ?
                                                000000000 ? 7FFD3BBDF380 ?

。。。
*** 2023-07-28T12:45:16.846766+08:00
ksedsts()+426<-kjzduptcctx()+724<-kjzdpcrshnfy()+353<-dbkedKstDump()+27<-dbgdaExecuteAction()+354<-dbgerRunAction()+83<-dbgerRunActions()+1352<-dbgexPhaseII()+549<-dbgexExplicitEndInc()+285<-dbgeEndDDEInvocationImpl()+314<-ssexhd()+2938<-sslssSynchHdlr()+411
<-sslsshandler()+94<-__sighandler()<-kauxalo()+353<-insLoadIndexes()+284<-inspreCG()+1425<-opitca()+10800<-kksFullTypeCheck()+81<-rpiswu2()+2004
----- End of Abridged Call Stack Trace -----

想到是否是这个问题呢，报警文件中，这个问题是从7月28日12点开始的，以前收集的资料中都没有这个问题，本次是否是这个问题呢，于是上MOS上搜了一下
ORA-07445 [kauxalo]，第一个文档连接就是：(Doc ID 33367909.8) ，说是BUG。

Bug 33367909  19.12 ORA-7445 [kauxalo] with json table/index
This note gives a brief overview of bug 33367909.
The content was last updated on: 07-MAR-2022
Click here for details of each of the sections below.
Affects:
Product (Component)       Oracle Server (Xdb)
Range of versions believed to be affected       Versions BELOW 23.1
Versions confirmed as being affected
(None Specified)
Platforms affected       Generic (all / most platforms affected)
Fixed:
The fix for 33367909 is first included in
23.1.0.0.0
21.4.0.0.DBRU:211019 (OCT 2021) DB Release Update (DB RU)
19.14.0.0.DBRU:220118 (JAN 2022) DB Release Update (DB RU)
19.13.0.0.DBRU:211019 (OCT 2021) DB Release Update (DB RU)
19.12.1.0.0 (OCT 2021) DB Release Update Revision(DB RUR)

Interim patches may be available for earlier versions - click here to check.
Symptoms:
Related To:
Internal Error May Occur (ORA-600)
ORA-7445
Dump in or under KAUXALO
XDB
Description
This bug is only relevant when using XDB
ORA-7445 in kauxalo when running INSERT AS SELECT into a table with an index from JSON_TABLE source

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3,
mask=0x0)
[TOC00004]
----- Current SQL Statement for this session (sql_id=c180k5u8by8cn) -----
INSERT /*+ APPEND */ INTO STG_SYNCHRO_AL( IDENTIFIANT_UNIQUE_LOT,
CODE_ORGANISME, ID_OPA, DTD_OPA, DTF_OPA, NUMERO_DOSSIER, FOYERINFOS,
FOYERALLOCATAIRE, FOYERECHEANCES, FOYERCGOD, SITUATIONDOSSIER,............

[TOC00005]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object    line  object
  handle number  name
0x187be38608    881  package body
LIQ1.PKG_SYNCHRO_AL.PR_LOTNUM_STG_SPLIT_JSON

Workaround
-----------
_simple_view_merging = false

RELATED BUGS:
-------------
32480559

REPRODUCIBILITY:
----------------
Reproduces at will on 19.12 test server

TEST CASE:
----------
Shared

STACK TRACE:
------------
[06]: kauxalo [Data]<-- Signaling
[07]: insLoadIndexes [DML]
[08]: inspreCG [DML]
[09]: opitca [OPI]
[10]: kksFullTypeCheck [cursor]
[11]: rpiswu2 [RPI]
[12]: kksSetBindType [cursor]
[13]: kksfbc [cursor]
[14]: opiexe [Time_Limit]

我的环境，正好是19.12，看报错中，确实是提取了json_table，而且报错都是完全匹配，于是按文档中给的Workaround,修改参数 _simple_view_merging = false，再让应用测试一下，不再报错。

后续，问对方，对方才说，确实是应用在7月28日升级了应用，加了JSON_TABLE用于消息发送，一般应用不受影响，但这个消息应用一直频繁的报错，由于不是主流应用，也没有重视。
以为是网络的问题，让看一下，原来是这个问题。所以工作中，就怕交流中沟通不充分，如果不看报警文件，还一直再找ora-12537的问题。

解决办法：
临时解决：
   alter system set "_simple_view_merging"=false;

打补丁33367909，或者升级到19.12以后的RU即可。

帐号		自动登录	找回密码
密码			注册

[Oracle] 来自ORACLE 19.12的一个JSON_TABLE的BUG:ORA-07445 [kauxalo]