ORA-00600: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []

ORA-00600: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []

问题

-rw-r----- 1 oracle asmadmin 147096 Aug 31 07:58 claimdb1_ora_37048.trm
-rw-r----- 1^C
-rw-r----- 1 oracle asmadmin 226534 Aug 31 07:47 claimdb1_ora_37131.trc
[oracle@phqlipeirac1 trace]$ tail -100f alert_claimdb.log
tail: cannot open ‘alert_claimdb.log’ for reading: No such file or directory
tail: no files remaining
[oracle@phqlipeirac1 trace]$ tail -100f alert_claimdb1.log
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/claimdb/claimdb1/trace/claimdb1_j000_59012.trc (incident=733859):
ORA-00600: ▒ڲ▒▒▒▒▒▒▒▒, ▒▒▒▒: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: ▒▒ “SYS.DBMS_STATS”, line 1976
ORA-06512: ▒▒ “SYS.DBMS_STATS”, line 39108
ORA-06512: ▒▒ line 1
Incident details in: /u01/app/oracle/diag/rdbms/claimdb/claimdb1/incident/incdir_733859/claimdb1_j000_59012_i733859.trc
2021-08-31T05:27:59.444921+08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
SYS_AUTO_SQL_TUNING_TASK exiting with error “600” for execution “EXEC_255494”. See DBA_ADVISOR_EXECUTIONS for more details.
End automatic SQL Tuning Advisor run for special tuning task “SYS_AUTO_SQL_TUNING_TASK”
Errors in file /u01/app/oracle/diag/rdbms/claimdb/claimdb1/trace/claimdb1_j000_59012.trc (incident=733860):
ORA-00600: ▒ڲ▒▒▒▒▒▒▒▒, ▒▒▒▒: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: ▒▒ “SYS.DBMS_STATS”, line 1976
ORA-06512: ▒▒ “SYS.DBMS_STATS”, line 39108
ORA-06512: ▒▒ line 1
ORA-06512: ▒▒ “SYS.DBMS_SQLTUNE_INTERNAL”, line 13887
ORA-06512: ▒▒ “SYS.DBMS_SQLTUNE_INTERNAL”, line 13907
ORA-06512: ▒▒ “SYS.WRI${}_{A}D{V}_{S}QLTUNE",line8ORA-06512:▒▒"SYS.PRV{T}_{A}DVISOR",line739ORA-06512:▒▒"SYS.PRV{T}_{A}DVISOR",line3238ORA-06512:▒▒"SYS.DBM{S}_{A}DVISOR",line262ORA-06512:▒▒"SYS.DBM{S}_{S}QLTUNE",line1186ORA-06512:▒▒line19UseADRCIorSupportWorkbenchtopackagetheincident.SeeNote411.1atMyOracleSupportforerrorandpackagingdetails.2021-08-31T05:27:59.486607+08:00Errorsinfile/u01/app/oracle/diag/rdbms/claimdb/claimdb1/trace/claimdb{1}_{j}000_{5}9012.trc:ORA-00600:▒ڲ▒▒▒▒▒▒▒▒,▒▒▒▒:[qosdExpStatRead:expcntmismatch],[],[],[],[],[],[],[],[],[],[],[]ORA-06512:▒▒"SYS.DBM{S}_{S}TATS",line1976ORA-06512:▒▒"SYS.DBM{S}_{S}TATS",line39108ORA-06512:▒▒line1ORA-06512:▒▒"SYS.DBM{S}_{S}QLTUN{E}_{I}NTERNAL",line13887ORA-06512:▒▒"SYS.DBM{S}_{S}QLTUN{E}_{I}NTERNAL",line13907ORA-06512:▒▒"SYS.WRI$_ADV_SQLTUNE”, line 8
ORA-06512: ▒▒ “SYS.PRVT_ADVISOR”, line 739
ORA-06512: ▒▒ “SYS.PRVT_ADVISOR”, line 3238
ORA-06512: ▒▒ “SYS.DBMS_ADVISOR”, line 262
ORA-06512: ▒▒ “SYS.DBMS_SQLTUNE”, line 1186
ORA-06512: ▒▒ line 19
2021-08-31T05:27:59.488652+08:00
opidrv aborting process J000 ospid (59012) as a result of ORA-600
2021-08-31T05:27:59.795301+08:00
Dumping diagnostic data in directory=[cdmp_20210831052759], requested by (instance=1, osid=59012 (J000)), summary=[incident=733859].
2021-08-31T05:37:04.537044+08:00
Dumping diagnostic data in directory=[cdmp_20210831053704], requested by (instance=2, osid=17305 (J001)), summary=[incident=206016].
2021-08-31T05:37:13.621573+08:00
Dumping diagnostic data in directory=[cdmp_20210831053713], requested by (instance=2, osid=17520 (J002)), summary=[incident=205482].
2021-08-31T06:00:00.432145+08:00
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
2021-08-31T06:10:26.924989+08:00
LOGMINER: End mining logfile for session 5 thread 2 sequence 51593, +DATADG/CLAIMDB/ONLINELOG/group_10.36888.986209193
2021-08-31T06:10:26.939778+08:00
LOGMINER: Begin mining logfile for session 5 thread 2 sequence 51594, +DATADG/CLAIMDB/ONLINELOG/group_8.36894.986209163
2021-08-31T06:10:27.439287+08:00
LOGMINER: End mining logfile for session 3 thread 2 sequence 51593, +DATADG/CLAIMDB/ONLINELOG/group_10.36888.986209193
2021-08-31T06:10:27.452213+08:00
LOGMINER: Begin mining logfile for session 3 thread 2 sequence 51594, +DATADG/CLAIMDB/ONLINELOG/group_8.36894.986209163
2021-08-31T06:14:55.071704+08:00
Thread 1 advanced to log sequence 64909 (LGWR switch)
Current log# 6 seq# 64909 mem# 0: +DATADG/CLAIMDB/ONLINELOG/group_6.36902.986209065
2021-08-31T06:14:55.312959+08:00
TT02: Standby redo logfile selected for thread 1 sequence 64909 for destination LOG_ARCHIVE_DEST_4
TT02: Standby redo logfile selected for thread 1 sequence 64909 for destination LOG_ARCHIVE_DEST_2
2021-08-31T06:14:57.084353+08:00
Archived Log entry 225682 added for T-1.S-64908 ID 0x54b61165 LAD:1
2021-08-31T06:14:58.334094+08:00
LOGMINER: End mining logfile for session 5 thread 1 sequence 64908, +DATADG/CLAIMDB/ONLINELOG/group_2.260.986210291
2021-08-31T06:14:58.345755+08:00
LOGMINER: Begin mining logfile for session 5 thread 1 sequence 64909, +DATADG/CLAIMDB/ONLINELOG/group_6.36902.986209065
2021-08-31T06:14:58.515287+08:00
LOGMINER: End mining logfile for session 3 thread 1 sequence 64908, +DATADG/CLAIMDB/ONLINELOG/group_2.260.986210291
2021-08-31T06:14:58.527255+08:00
LOGMINER: Begin mining logfile for session 3 thread 1 sequence 64909, +DATADG/CLAIMDB/ONLINELOG/group_6.36902.986209065
2021-08-31T07:52:22.380101+08:00
Thread 1 advanced to log sequence 64910 (LGWR switch)
Current log# 7 seq# 64910 mem# 0: +DATADG/CLAIMDB/ONLINELOG/group_7.36898.986209659
2021-08-31T07:52:22.622019+08:00
TT02: Standby redo logfile selected for thread 1 sequence 64910 for destination LOG_ARCHIVE_DEST_4
TT02: Standby redo logfile selected for thread 1 sequence 64910 for destination LOG_ARCHIVE_DEST_2
2021-08-31T07:52:24.607995+08:00
Archived Log entry 225685 added for T-1.S-64909 ID 0x54b61165 LAD:1
2021-08-31T07:52:25.895062+08:00
LOGMINER: End mining logfile for session 5 thread 1 sequence 64909, +DATADG/CLAIMDB/ONLINELOG/group_6.36902.986209065
2021-08-31T07:52:25.895655+08:00
LOGMINER: End mining logfile for session 3 thread 1 sequence 64909, +DATADG/CLAIMDB/ONLINELOG/group_6.36902.986209065
2021-08-31T07:52:25.908050+08:00
LOGMINER: Begin mining logfile for session 3 thread 1 sequence 64910, +DATADG/CLAIMDB/ONLINELOG/group_7.36898.986209659

解决

问题描述：数据库alert日志报ORA-00600错误，只在数据库收集统计信息的时候报这个错误。从trace文件可以看到就是在收集某一张表统计信息出现问题。在我的数据库环境中，单实例，RAC都出现了这个问题。在MOS提过SR后，Oracle给出了解决办法。12C很多数据库都会遇到该问题，这里记录一下解决过程。

数据库版本：12.2.0.1.0

系统：CentOS Linux release 7.5/Red Hat Enterprise Linux Server release 7.5

报错信息：

Errors in file /opt/app/oracle/diag/rdbms/prod/prod1/trace/prod1_j000_80466.trc (incident=1007661):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at “SYS.DBMS_STATS”, line 1976
ORA-06512: at “SYS.DBMS_STATS”, line 46935
ORA-06512: at “SYS.DBMS_STATS”, line 47168
Incident details in: /opt/app/oracle/diag/rdbms/prod/prod1/incident/incdir_1007661/prod1_j000_80466_i1007661.trc

prod1_j000_80466_i1007661.trc

Dump file /opt/app/oracle/diag/rdbms/prod/prod1/incident/incdir_1007661/prod1_j000_80466_i1007661.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: /opt/app/oracle/product/12.2.0/db_1
System name: Linux
Node name: prodblsg51
Release: 4.1.12-112.16.4.el7uek.x86_64
Version: #2 SMP Mon Mar 12 23:57:12 PDT 2018
Machine: x86_64
Instance name: prod1
Redo thread mounted by this instance: 1
Oracle process number: 357
Unix process pid: 80466, image: oracle@prodblsg51 (J000)

*** 2019-01-26T19:34:42.282056+08:00
*** SESSION ID:(574.16808) 2019-01-26T19:34:42.282084+08:00
*** CLIENT ID:() 2019-01-26T19:34:42.282089+08:00
*** SERVICE NAME:(SYS$USERS)2019-01-26T19:34:42.282094+08:00\ast \ast \ast MODULENAME:(DBM{S}_{S}CHEDULER)2019-01-26T19:34:42.282099+08:00\ast \ast \ast ACTIONNAME:(ORA$AT_OS_OPT_SY_4192) 2019-01-26T19:34:42.282104+08:00
*** CLIENT DRIVER:() 2019-01-26T19:34:42.282109+08:00

[TOC00000]
Jump to table of contents
Dump continued from file: /opt/app/oracle/diag/rdbms/prod/prod1/trace/prod1_j000_80466.trc
[TOC00001]
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at “SYS.DBMS_STATS”, line 1976
ORA-06512: at “SYS.DBMS_STATS”, line 46935
ORA-06512: at “SYS.DBMS_STATS”, line 47168

[TOC00001-END]
[TOC00002]
========= Dump for incident 1007661 (ORA 600 [ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [],]) ========

*** 2019-01-26T19:34:42.282600+08:00
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
[TOC00003-END]
根据Oracle给出的检查SQL，执行结果如下：

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

---

587745 1 5 65541 587745 1　　
该问的引起原因：
＝＝＝＝＝＝＝＝＝＝＝＝
由于 sys.exp_obj$.EX{P}_{C}NT与sys.ex{p}_{s}tat$的行数不匹配引起。

解决方案：
＝＝＝＝＝＝＝＝＝＝＝＝＝
1.备份数据库<<<<<<<<<涉及到修改数据字典，我们还是要备份一下数据库的。

2. 修复问题
   sqlplus / as sysdba
   alter session set container=<container_name>;
   update sys.exp_obj$ a set exp_cnt=(select count(*) from sys.exp_stat$ b where
   a.objn=b.objn and a.snapshot_id=b.snapshot_id ) where a.objn=587745;

3. 修改后，执行如下SQL确认是否还有记录，如果没有记录，说明已经修改成功，提交。
   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;

----如果没有记录,执行提交

commit;
————————————————
版权声明：本文为CSDN博主「perfeader」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u012987186/article/details/87865190