ORA600[13013], [5001]报错处理

某日，业务反馈两个跑批程序出现ORA600错误，检查数据库alert日志后发现对应时间日志中存在相应ORA600报错

错误信息

2023-05-11T06:38:28.865508+08:00
Errors in file /u01/app/oracle/diag/rdbms/mydb_dg1/mydb1/trace/mydb1_ora_366724.trc  (incident=4763607):
ORA-00600: internal error code, arguments: [13013], [5001], [5452069], [676509773], [13], [676509773], [17], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/mydb_dg1/mydb1/incident/incdir_4763607/mydb1_ora_366724_i4763607.trc
2023-05-11T06:38:30.964513+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-05-11T06:38:31.186731+08:00
Dumping diagnostic data in directory=[cdmp_20230511063831], requested by (instance=1, osid=366724), summary=[incident=4763607].
2023-05-11T06:38:44.653489+08:00
Errors in file /u01/app/oracle/diag/rdbms/mydb_dg1/mydb1/trace/mydb1_ora_372825.trc  (incident=4763479):
ORA-00600: internal error code, arguments: [13013], [5001], [5452069], [676509773], [13], [676509773], [17], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/mydb_dg1/mydb1/incident/incdir_4763479/mydb1_ora_372825_i4763479.trc

日志分析

查看alert日志涉及的两个trace文件，发现Current SQL Statement for this session 部分为同一条SQL语句，语句结构如下：

MERGE INTO (SELECT COL1,COL2 FROM TABLE1 WHERE CREATETIME >= :B1 AND COL2 IS NULL) M 
  USING L_TABLE2 N ON (M.COL1 = N.COL1) 
WHEN MATCHED THEN 
  UPDATE SET M.COL2 = N.COL3

其中L_TABLE2为上一步提供数据的临时表

另外根据ORA600错误的参数[13013], [5001]判断索引和表可能存在问题，检查dba_objects视图中是否存在以第三个参数为data_object_id的对象（此处为[5452069]），检查发现存在对应的数据库对象（TABLE1）

select * from dba_objects where data_object_id=5452069;

对象检查

使用如下方法检查数据库对象是否存在异常

analyze table table1 validate structure online;
analyze table table1 validate structure cascade online;

检查结果未输出报错，即“数据库对象无异常，非bug出现数据坏块”

与业务人员确认TABLE1表数据变化频繁，说明本次报错是属于merge变化导致的索引和数据不一致 ，将程序代码中merge into后的子查询进行改写后恢复正常

MERGE INTO TABLE1 M 
  USING L_TABLE2 N ON (M.COL1 = N.COL1) 
WHEN MATCHED THEN 
  UPDATE SET M.COL2 = N.COL3 WHERE M.CREATETIME >= :B1 AND M.COL2 IS NULL

问题总结

程序代码中避免在merge into后使用数据变化频繁的表作为子查询使用
待验证：merge into后使用子查询进行数据筛选的效果与不使用子查询进行数据筛选效果无差异

参考资料

博客：https://blog.csdn.net/songyundong1993/article/details/116780640
ORACLE MOS文档：How to resolve ORA-00600 [13013], [5001] (文档 ID 816784.1)