error.png (399.89 KB, 下载次数: 448)
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、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |