ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1] ORA-00600: 内部错误代码, 参数: [17147] ORA-48216: 字段长度超

问题现象：

v$diag_alert_ext 引发的ORA-00600告警

数据库监控告警发出ORA-00600的告警信息，查看alert日志有大量大600错误

Fri Jan 10 15:30:04 2025
Errors in file d:\app\administrator\diag\rdbms\production\prod\trace\prod_ora_15280.trc:
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []

Fri Jan 10 15:30:04 2025
Process debug not enabled via parameter _debug_enable
Fri Jan 10 15:39:40 2025
Thread 1 advanced to log sequence 28267 (LGWR switch)
  Current log# 4 seq# 28267 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\prod\REDO04.LOG
  Current log# 4 seq# 28267 mem# 1: D:\APP\ADMINISTRATOR\ORADATA\prod\REDO04_2.LOG
Fri Jan 10 15:39:41 2025
LNS: Standby redo logfile selected for thread 1 sequence 28267 for destination LOG_ARCHIVE_DEST_2
Fri Jan 10 15:39:42 2025
Archived Log entry 82524 added for thread 1 sequence 28266 ID 0x958e9f7a dest 1:
Fri Jan 10 15:51:41 2025
Thread 1 advanced to log sequence 28268 (LGWR switch)
  Current log# 5 seq# 28268 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\prod\REDO05.LOG
  Current log# 5 seq# 28268 mem# 1: D:\APP\ADMINISTRATOR\ORADATA\prod\REDO05_2.LOG
Fri Jan 10 15:51:41 2025
LNS: Standby redo logfile selected for thread 1 sequence 28268 for destination LOG_ARCHIVE_DEST_2
Fri Jan 10 15:51:47 2025
Archived Log entry 82526 added for thread 1 sequence 28267 ID 0x958e9f7a dest 1:
Fri Jan 10 16:02:45 2025
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
Fri Jan 10 16:03:59 2025
Thread 1 advanced to log sequence 28269 (LGWR switch)
  Current log# 6 seq# 28269 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\prod\REDO06.LOG
  Current log# 6 seq# 28269 mem# 1: D:\APP\ADMINISTRATOR\ORADATA\prod\REDO06_2.LOG
Fri Jan 10 16:04:00 2025
LNS: Standby redo logfile selected for thread 1 sequence 28269 for destination LOG_ARCHIVE_DEST_2
Fri Jan 10 16:04:03 2025
Archived Log entry 82528 added for thread 1 sequence 28268 ID 0x958e9f7a dest 1:
Fri Jan 10 16:08:15 2025
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_13980.trc  (incident=61289):
ORA-00600: 内部错误代码, 参数: [17147], [0x0D74BF398], [], [], [], [], [], [], [], [], [], []
ORA-48216: 字段长度超过了字段最大长度 [5] [30] [23328] [66]
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\incident\incdir_61289\prod_ora_13980_i61289.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Exception [type: ACCESS_VIOLATION, UNABLE_TO_WRITE] [ADDR:0x0] [PC:0xD2B8E4, kgidmp()+1440]
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_13980.trc  (incident=61290):
ORA-07445: 出现异常错误: 核心转储 [kgidmp()+1440] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0xD2B8E4] [UNABLE_TO_WRITE] []
ORA-00600: 内部错误代码, 参数: [17147], [0x0D74BF398], [], [], [], [], [], [], [], [], [], []
ORA-48216: 字段长度超过了字段最大长度 [5] [30] [23328] [66]
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\incident\incdir_61290\prod_ora_13980_i61290.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Jan 10 16:13:07 2025
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=61337):
ORA-00600: 内部错误代码, 参数: [17147], [0x0D490F0F8], [], [], [], [], [], [], [], [], [], []
ORA-48216: 字段长度超过了字段最大长度 [5] [30] [23328] [66]
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\incident\incdir_61337\prod_ora_18136_i61337.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Fri Jan 10 16:13:09 2025
Dumping diagnostic data in directory=[cdmp_20250110161309], requested by (instance=1, osid=18136), summary=[incident=61337].
Fri Jan 10 16:13:09 2025
Sweep [inc][61337]: completed
Sweep [inc][61290]: completed
Sweep [inc][61289]: completed
Sweep [inc2][61337]: completed
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=61339):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\incident\incdir_61339\prod_ora_18136_i61339.trc
Fri Jan 10 16:13:11 2025
Sweep [inc][61339]: completed
Fri Jan 10 16:13:13 2025
Sweep [inc2][61339]: completed
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=61340):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\incident\incdir_61340\prod_ora_18136_i61340.trc
Dumping diagnostic data in directory=[cdmp_20250110161313], requested by (instance=1, osid=18136), summary=[incident=61339].
Fri Jan 10 16:13:15 2025
Sweep [inc][61340]: completed
Sweep [inc2][61340]: completed
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=61341):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\incident\incdir_61341\prod_ora_18136_i61341.trc
Dumping diagnostic data in directory=[cdmp_20250110161316], requested by (instance=1, osid=18136), summary=[incident=61340].
Fri Jan 10 16:13:18 2025
Sweep [inc][61341]: completed
Fri Jan 10 16:13:18 2025
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=61342):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Sweep [inc2][61341]: completed
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\incident\incdir_61342\prod_ora_18136_i61342.trc
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=61343):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Fri Jan 10 16:13:20 2025
Sweep [inc][61342]: completed
Sweep [inc][61343]: completed
Sweep [inc2][61342]: completed
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=61344):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=61344):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=80001):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=80002):
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [dbgfps_get_proddef_by_id:1], [0], [], [], [], [], [], [], [], [], [], []
Fri Jan 10 16:13:21 2025
Dumping diagnostic data in directory=[cdmp_20250110161321], requested by (instance=1, osid=18136), summary=[incident=61342].
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\trace\prod_ora_18136.trc  (incident=80004):
ORA-07445: 出现异常错误: 核心转储 [slgfn()+102] [ACCESS_VIOLATION] [ADDR:0x0] [PC:0x14D36C4] [UNABLE_TO_READ] []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\production\prod\incident\incdir_80004\prod_ora_18136_i80004.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20250110161324], requested by (instance=1, osid=18136), summary=[incident=80004].
Fri Jan 10 16:14:12 2025

问题调查

经过询问管理员，他正在运行数据库巡检，执行了一些sql语句，经过查看SQL语句以及TRC日志信息，触发告警的语句是：

尝试手工查询该视图的大小select count(*) from v$diag_alert_ext; 出现同样的告警

问题原因

经过查询MOS，该问题主要触发了ORACLE 的BUG导致。
ORA-600 [dbgfps_get_proddef_by_id:1] (Doc ID 1940620.1)

ORA-00600 [17147] ORA-48216 When Querying V$DIAG_ALERT_EXT View (Doc ID 2119059.1) To BottomTo Bottom

如何清理v$diag_alert_ext

How to Purge x$dia{g}_{a}ler{t}_{e}xttables?(DocID2179263.1)V$DIAG_ALERT_EXT does Not Get Cleared after adrci purge (Doc ID 2237144.1)

19:10:54 SQL> select count(*) from x$diag_alert_ext ;
 COUNT(*)
----------
 45881462

Elapsed: 00:16:26.34

> adrci

ADRCI: Release 11.2.0.4.0 - Production on Fri Aug 26 19:28:59 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = "<ADR Base>"
adrci>
adrci>
adrci> show home
ADR Homes:
<list of ADR homes>
adrci> SET HOMEPATH <ADR HOME Path>
adrci> purge -age 10080 -type alert; ==========> to manually purge files older than 1 week:
adrci> exit

SQL> set timing on ;
SQL> set time on ;
19:30:56 SQL> select count(*) from x$diag_alert_ext ;

 COUNT(*)
----------
  499844

Elapsed: 00:00:10.62

清理过程中，发现执行了purge了但是记录任然没有减少。

根据MOS的说法，可能的原因是清理的目录不对，尝试把所有的目录都清理一遍

再次查看，清理成功。

查询当前的最小时间

SQL> select to_char(min(ORIGINATING_TIMESTAMP),'yyyy-mm-dd hh24:mi:ss') min_time from V$DIAG_ALERT_EXT;

MIN_TIME
-------------------
2023-04-24 22:12:27