Oracle Database 12.2/18c/19c ORA-00600: [qosdExpStatRead: expcnt mismatch]

Oracle database 12.2, 18c or 19c database alert log file reports ORA-00600: [qosdExpStatRead: expcnt mismatch]. The error message is something like following

2023-01-29T15:55:03.286965-05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/cdb01/trace/cdb01_mz00_513756.trc  (incident=97764) (PDBNAME=PDB1):
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [65561], [25], [3125688], [1], [], [], [], [], [], [], []
PDB1(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2023-01-29T15:55:03.291141-05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/cdb01/trace/cdb01_mz00_513756.trc:
ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [65561], [25], [3125688], [1], [], [], [], [], [], [], []

The error is due to mismatch between expression count (EXP_CNT) in SYS table SYS.EXP_OBJ$, and number of matching rows in table SYS.EXP_STAT$. For example, in the database which is reporting previous errors

sys@pdb1> show con_name
CON_NAME
---------
PDB1

sys@PDB1> select count(*) from sys.exp_stat$ where snapshot_id=1 and objn=3125688;

   COUNT(*)
___________
      65561

sys@PDB1> select exp_cnt from sys.exp_obj$ a where a.snapshot_id = 1 and a.objn = 3125688;

   EXP_CNT
__________
        25

The object (object number 3125688) has 65561 records with snapshot_id 1 in table sys.exp_stat$, but the value of EXP_CNT column in table sys.exp_obj$ for same object is 25. They are different, which trigger the ORA-600.

Solution

1. To stop the mismatch happening in the future, apply one-off patch 28681153. The fix is included in Database Release Update (DBRU) starting from 12.2.0.1.201020, 18.15, 19.7. The patch only fixes the issue with future occurrence, does not eliminate existing occurrence.

2. To eliminate the existing orrurrence (reporting ORA-600 against existing data in sys.exp_stat$ and sys.exp_obj$), apply patch 31143146 and set implicit parameter "_FIX_CONTROL" with follwoing command

ALTER SYSTEM SET "_FIX_CONTROL"='31143146:ON'

The fix for 31143146 is included in 19c DBRU starting from 19.11. The fix, no mattter installed by one-off patch or DBUR, is disabled by default. It has to be enabled with previous command to set parameter "_FIX_CONTROL".

If the fix is not applied or you do not want to set the parameter. You can manually reset existing data recordes to eliminate occuring errors with following steps,

Note: Run following in the container in which the error occures, it is pluggable database PDB1 in the previous example.

1). Find out objn and snapshot_id of mismatch records with sql

with b as (
   select count(*) cnt, objn, snapshot_id
     from sys.exp_stat$
     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;

Example output

sys@PDB1> With b as (
  2      select count(*) cnt, objn, snapshot_id
  3        from sys.exp_stat$
  4        group by objn,snapshot_id)
  5  select *
  6    from sys.exp_obj$ a, b
  7   where a.objn=b.objn
  8         and a.snapshot_id=b.snapshot_id
  9*        and a.EXP_CNT<>b.CNT;

      OBJN    SNAPSHOT_ID    EXP_CNT      CNT       OBJN    SNAPSHOT_ID
__________ ______________ __________ ________ __________ ______________
   3125688              1         25    65561    3125688              1

2). Delete all records, which groups of objn and snapshot_id are listed in step 1, from sys.exp_stat$

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

Replace <snapshot_id> and <objn> with the values listed in step 1, for example

sys@PDB1> delete from sys.exp_stat$ where snapshot_id=1 and objn=3125688;

65,561 rows deleted.

3). Reset the column EXP_CNT of sys.exp_obj$ with sql

  update sys.exp_obj$ set exp_cnt=0 where snapshot_id=<snapshot_ip> and objn =<objn>;

Replace <snapshot_id> and <objn> with the values listed in step 1, for example

sys@PDB1> update sys.exp_obj$ set EXP_CNT=0 where snapshot_id=1 and objn=3125688;

1 row updated.

Rember to commit the changes.