ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch]

故障描述

• 1）数据库安装目录/u01空间告警，登录数据库主机查看，有大量的cdmp目录
• 2）数据库alert日志中，出现以下报错

2022-02-25T15:16:17.129881+08:00
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_mz00_282244.trc  (incident=2715710):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2022-02-25T15:16:17.133510+08:00
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_mz00_282244.trc:
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
复制

参考MOS

Bug 28681153 - ORA-600: [qosdexpstatread: expcnt mismatch] (Doc ID 28681153.8)
复制

故障排查

• 当前数据库版本为 19.3
• 通过查询MOS文章，发现是19.3版本的Bug 28681153，sys.exp_obj$.exp_cnt与sys.exp_stat$的行数据不匹配所造成的
• 解决方法

1 - 应用Patch 28681153 

2 - 应用19.7.0.0.200414 (Apr 2020) Database Release Update (DB RU) 

3 - Workaround 
With b as ( 
select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by 
objn,snapshot_id) 
select * from sys.exp_obj$ a, b where a.objn=b.objn and 
a.snapshot_id=b.snapshot_id 
and a.EXP_CNT<>b.CNT; 

delete from sys.exp_stat$ b 
where b.snapshot_id = <snapshot_id> and b.objn = <objn> ; 

update sys.exp_obj$ a 
set a.EXP_CNT=0 
where a.SNAPSHOT_ID= <snapshot_id> 
and a.objn = <objn>; 

commit; 
复制

• 暂时没有停机维护窗口，采用第三种方式解决

解决步骤

• 涉及到修改数据字典，我们还是需要备份一下数据库的
• 1）数据库中查询不一致的EXP_CNT值

SQL> With b as (
  2  select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id)
  3  select * from sys.exp_obj$ a, b where a.objn=b.objn and a.snapshot_id=b.snapshot_id 
  4  and a.EXP_CNT<>b.CNT;

      OBJN SNAPSHOT_ID	  EXP_CNT	 CNT	   OBJN SNAPSHOT_ID
---------- ----------- ---------- ---------- ---------- -----------
   1134722	     1	    10825      76361	1134722 	  1
复制

• 2）修复问题

delete from sys.exp_stat$ b where b.snapshot_id=1 and b.objn=1134722 ;
update sys.exp_obj$ a set a.EXP_CNT=0 where a.SNAPSHOT_ID=1 and a.objn=1134722;
commit;
复制

• 3）再次执行查询，无输出，即表示修复完成