Analysis of database crash problems caused by ORA-07445 [qerhnFetch()+590] and ORA-00600 [qkexrXCopn1]

Service Overview

The error message of the user’s application program is displayed as ORA-01034 ORACLE not available, and the program cannot be used normally (the background log reports an error ORA-07445 [qerhnFetch() + 590] and ORA-00600 [qkexrXCopn1], and then the database instance goes down). After receiving the notification of the database failure, remote access to the database environment for troubleshooting, through the in-depth analysis of relevant logs and other information, finally find the problem point and deal with it, the database returned to normal, sort out and summarize this document according to the problem handling situation at that time .

Second, problem and related log analysis

Database alert log information

TT02: Standby redo logfile selected for thread 1 sequence 1299853 for destination LOG_ARCHIVE_DEST_2

2023-05-13T09:40:34.183347+08:00

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC] [PC:0x10E541BE, qerhnFetch() + 590] [flags: 0x0, count: 1]

Errors in file /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_ora_106511.trc (incident=658472):

ORA-07445: exception error occurred: core dumped [qerhnFetch() + 590] [SIGSEGV] [ADDR:0xC] [PC:0x10E541BE] [Address not mapped to object] []

Incident details in: /u02/prod/oracle/diag/rdbms/aps/aps/incident/incdir_658472/aps_ora_106511_i658472.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 /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_ora_106511.trc (incident=658473):

ORA-00600: internal error code, parameters: [qkexrXCopn1], [0], [], [], [], [], [], [], [], [], [], []

ORA-07445: exception error occurred: core dumped [qerhnFetch() + 590] [SIGSEGV] [ADDR:0xC] [PC:0x10E541BE] [Address not mapped to object] []

Incident details in: /u02/prod/oracle/diag/rdbms/aps/aps/incident/incdir_658473/aps_ora_106511_i658473.trc

2023-05-13T09:40:36.299843+08:00

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

2023-05-13T09:40:36.339912+08:00

Errors in file /u02/prod/oracle/diag/rdbms/aps/aps/incident/incdir_658472/aps_ora_106511_i658472.trc:

ORA-00600: internal error code, parameters: [qkexrXCopn1], [0], [], [], [], [], [], [], [], [], [], []

ORA-07445: exception error occurred: core dumped [qerhnFetch() + 590] [SIGSEGV] [ADDR:0xC] [PC:0x10E541BE] [Address not mapped to object] []

2023-05-13T09:40:36.782462+08:00

Dumping diagnostic data in directory=[cdmp_20230513094036], requested by (instance=1, osid=106511), summary=[incident=658473].

2023-05-13T09:40:43.967698+08:00

Thread 1 advanced to log sequence 1299854 (LGWR switch)

  Current log# 9 seq# 1299854 mem# 0: /u02/prod/oracle/oradata/aps/redo09.log

2023-05-13T09:40:44.362822+08:00

Archived Log entry 1948108 added for T-1.S-1299853 ID 0xfd032dae LAD:1

2023-05-13T09:40:45.633807+08:00

TT02: Standby redo logfile selected for thread 1 sequence 1299854 for destination LOG_ARCHIVE_DEST_2

2023-05-13T09:41:00.322186+08:00

Errors in file /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_clmn_313617.trc (incident=656584):

ORA-00600: internal error code, arguments: [17147], [0x07C8EB4E0], [], [], [], [], [], [], [], [], [], []

Incident details in: /u02/prod/oracle/diag/rdbms/aps/aps/incident/incdir_656584/aps_clmn_313617_i656584.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

2023-05-13T09:41:01.777942+08:00

Errors in file /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_clmn_313617.trc:

ORA-00600: internal error code, arguments: [17147], [0x07C8EB4E0], [], [], [], [], [], [], [], [], [], []

Errors in file /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_clmn_313617.trc (incident=656585):

ORA-501 [][][][][][][][][][][][]

Incident details in: /u02/prod/oracle/diag/rdbms/aps/aps/incident/incdir_656585/aps_clmn_313617_i656585.trc

2023-05-13T09:41:02.251159+08:00

Dumping diagnostic data in directory=[cdmp_20230513094102], requested by (instance=1, osid=313617 (CLMN)), summary=[incident=656584].

2023-05-13T09:41:02.391419+08:00

USER (ospid: 313617): terminating the instance due to error 501

2023-05-13T09:41:03.896169+08:00

System state dump requested by (instance=1, osid=313617 (CLMN)), summary=[abnormal instance termination].

System State dumped to trace file /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_diag_313642_20230513094103.trc

2023-05-13T09:41:08.681829+08:00

Instance terminated by USER, pid = 313617

2023-05-13T09:51:29.648377+08:00

Starting ORACLE instance (normal) (OS id: 112533)

Related TRACE file analysis

ORA-07445 wrong TRACE file:

Dump file /u02/prod/oracle/diag/rdbms/aps/aps/incident/incdir_658472/aps_ora_106511_i658472.trc

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125

ORACLE_HOME: /u02/prod/oracle/product/12.2.0/db_1

System name: Linux

Node name: aps

Release: 2.6.32-696.el6.x86_64

Version: #1 SMP Tue Feb 21 00:53:17 EST 2017

Machine: x86_64

Instance name: aps

Redo thread mounted by this instance: 1

Oracle process number: 239

Unix process pid: 106511, image: oracle@aps





***2023-05-13T09:40:34.275856+08:00

*** SESSION ID:(1826.31190) 2023-05-13T09:40:34.275871+08:00

*** CLIENT ID:(litianhong) 2023-05-13T09:40:34.275877+08:00

*** SERVICE NAME:(aps) 2023-05-13T09:40:34.275884+08:00

*** MODULE NAME:(mdl) 2023-05-13T09:40:34.275889+08:00

*** ACTION NAME:() 2023-05-13T09:40:34.275894+08:00

*** CLIENT DRIVER:(ODPM.NET : 12.0.0.0.0) 2023-05-13T09:40:34.275899+08:00

 

[TOC00000]

Jump to table of contents

Dump continued from file: /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_ora_106511.trc

[TOC00001]

ORA-07445: exception error occurred: core dumped [qerhnFetch() + 590] [SIGSEGV] [ADDR:0xC] [PC:0x10E541BE] [Address not mapped to object] []



[TOC00001-END]

[TOC00002]

========= Dump for incident 658472 (ORA 7445 [qerhnFetch]) ========

[TOC00003]

-----Beginning of Customized Incident Dump(s)-----

Dumping swap information

Memory (Avail / Total) = 1091.59M / 63994.07M

Swap (Avail / Total) = 28781.46M / 32064.00M

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC] [PC:0x10E541BE, qerhnFetch() + 590] [flags: 0x0, count: 1]

Registers:

%rax: 0x00000000000000001 %rbx: 0x0000000000000000 %rcx: 0x00007ffcd3edc470

%rdx: 0x0000000000000107 %rdi: 0x0000000000000000 %rsi: 0x00007f548eb72e68

%rsp: 0x00007ffcd3edc230 %rbp: 0x00007ffcd3edc3f0 %r8: 0x0000000000007ffe

 %r9: 0x00007ffcd3edc470 %r10: 0x00000000044e0048 %r11: 0x00000000044e0048

%r12: 0x000000007c8ebf40 %r13: 0x0000000000007ffe %r14: 0x00000000b0d56158

%r15: 0x00007f548eb72e68 %rip: 0x0000000010e541be ?l: 0x0000000000010202

  qerhnFetch() + 572 (0x10e541ac) mov 0x30(%r15),?x

  qerhnFetch() + 576 (0x10e541b0) test $0x4,%dl

  qerhnFetch() + 579 (0x10e541b3) jz 0x10e55cd6

  qerhnFetch() + 585 (0x10e541b9) mov 0x20(%r12),%rdi

> qerhnFetch() + 590 (0x10e541be) testb $0x1,0xc(%rdi)

  qerhnFetch() + 594 (0x10e541c2) jz 0x10e55cc0

  qerhnFetch() + 600 (0x10e541c8) testb $0x2,0x2(%r14)

  qerhnFetch() + 605 (0x10e541cd) jnz 0x10e55c74

  qerhnFetch() + 611 (0x10e541d3) movb $0x0,-0x78(%rbp)



***2023-05-13T09:40:34.282027+08:00

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)

[TOC00005]

Select * From AAAAA_v t Where nvl(manual_entry_flag,'N') = 'N' and t.plant_id = '152' and t.top_make_order_id>0 Order by t.top_make_order_id

[TOC00004-END]

…

----- Plan Table -----



=============

Plan Table

=============
……………………

dbkedDefDump: errors signaled while dumping diagnostics

ORA-00600: ?<86><85>é<83>¨é<94><99>èˉˉ <81>, ?<8f><82>?<95>°: [qkexrXCopn1], [ 0], [], [], [], [], [], [], [], [], [], []

ORA-07445: ?<87>o?<8e>°<82>?é<94><99>èˉˉ: <83>è?<82>¨ [qerhnFetch() + 590] [SIGSEGV] [ADDR:0xC] [PC:0x10E541BE] [Address not mapped to object] []

Shadow_Core_Dump = partial

ORA-00600 error TRACE file:

[root@aps ~]# more /u02/prod/oracle/diag/rdbms/aps/aps/incident/incdir_656584/aps_clmn_313617_i656584.trc
Dump file /u02/prod/oracle/diag/rdbms/aps/aps/incident/incdir_656584/aps_clmn_313617_i656584.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /u02/prod/oracle/product/12.2.0/db_1
System name: Linux
Node name: aps
Release: 2.6.32-696.el6.x86_64
Version: #1 SMP Tue Feb 21 00:53:17 EST 2017
Machine: x86_64
Instance name: aps
Redo thread mounted by this instance: 1
Oracle process number: 3
Unix process pid: 313617, image: oracle@aps (CLMN)
***2023-05-13T09:41:00.323452+08:00
*** SESSION ID:(364.40525) 2023-05-13T09:41:00.323462+08:00
*** CLIENT ID: () 2023-05-13T09:41:00.323468 + 08:00
*** SERVICE NAME:(SYS$BACKGROUND) 2023-05-13T09:41:00.323474+08:00
***MODULE NAME:() 2023-05-13T09:41:00.323479+08:00
*** ACTION NAME:() 2023-05-13T09:41:00.323485+08:00
*** CLIENT DRIVER:() 2023-05-13T09:41:00.323490+08:00
[TOC00000]
Jump to table of contents
Dump continued from file: /u02/prod/oracle/diag/rdbms/aps/aps/trace/aps_clmn_313617.trc
[TOC00001]
ORA-00600: internal error code, arguments: [17147], [0x07C8EB4E0], [], [], [], [], [], [], [], [], [], []

Analysis Conclusion

After analyzing the related logs of the database, the failure of the database is consistent with the failure of 2023-05-10. The execution plan of the problematic SQL has not changed, and if the SQL cannot be optimized, only a patch can be installed to solve the BUG: 30403763

The knowledge points of the article match the official knowledge files, and you can further learn related knowledge MySQL entry skill tree SQL advanced skillsCTE and recursive query 55245 people are studying systematically