Index issue core dumping

I had seen the following issue in the trace files while it was core dumping every few secs.The offending statement was a delete statement with where clause.On examining the trace files it looked like an issue of key mismatch.Note that this is key mismatch issue not a corrupt index issue so all the tools used for finding the corrupt blocks will not return anything.eg-database Validate,dbverify and analyze on index will come clean since the logical structure of the index is valid.To identify the key mismatch issue proceed as follwing.

From the trace files see following error

oer 8102.2 - obj# 155099, rdba: 0x53037ad2(afn 332, blk# 363218)
kdk key 8102.2:
  ncol: 2, len: 14
  key: (14):  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  mask: (4096)

Clearly the delete staement was not able to identify a key which was there in the table but not in the index.Next find the offending index using the object id reported in the trace file


--> select name from obj$ where obj# = 155099 

To identify the keys that are missing from the index we ran the following queries.

Following query will give the keys which are there in the table but not in the index.

SELECT /*+ FULL(t1) */ rowid,ORDER_ID
FROM scott.Task_activity t1
MINUS
SELECT /*+ index(t XIE4TASK_ACTIVITY$C) */ rowid,ORDER_ID
FROM scott.Task_activity t;

Following query will give the keys which are there in the index but not in the table.

SELECT /*+ index(t XIE4TASK_ACTIVITY$C) */ rowid,ORDER_ID
FROM scott.Task_activity t
MINUS
SELECT /*+ FULL(t1) */ rowid,ORDER_ID
FROM scott.Task_activity t1;

The resolution is to drop and recreate the index don't use the online rebuild of the index.