ORA-08102: index key not found, obj# 255898, file 15, block 758743 (2)

We encountered this issue couple of times during database migration or in client environment. This issue can be due to block corruption of index(or may be table as well)

I noticed that rebuild index online or dropping or re-creating the index maynot help in some of the cases.

We did following operations and it did not work.

1. Drop FCST_SELECT and HIST_SELECT indexes
2. Gather stats on HIST and FCST tables
3. Re-create FCST_SELECT and HIST_SELECT indexes

Below was the underlying query which was resulting the error:

DELETE /* HistPurgeDAODeleteHint */

 /* HistPurgeDAODeleteHint */

FROM TAB1 h

WHERE (h.dmdunit, h.dmdgroup, h.loc, h.histstream) IN

 (SELECT /* HistPurgeDAOSelectHint */

 /* HistPurgeDAOSelectHint */

 dm.todmdunit,

 dm.todmdgroup,

 dm.todfuloc,

 m.histstream

 FROM XXXX dm, YYYYY pr, ZZZZ m

 WHERE pr.map = :1

 AND pr.action_number = :2

 AND pr.action_group = :3

 AND pr.action_group_set_id = :4

 AND pr.map = dm.map

 AND pr.dmdunit = dm.todmdunit

 AND pr.dmdgroup = dm.todmdgroup

 AND pr.loc = dm.todfuloc

 AND pr.model = dm.tomodel

 AND dm.tomodel = m.model

 AND dm.histtype = 1)

 AND h.TYPE <> 3;

Note that FCST_SELECT is function based index but function always results into deterministic value 

CREATE INDEX HIST_SELECT ON TAB1

(DMDGROUP, DMDUNIT, LOC, HISTSTREAM, STARTDATE, 

"STARTDATE"+"DUR"/1440)

To resolve this issue we tried below approach and it worked.

1. Create a back up table with same structure and data

2. Drop existing table. 

3. Rename back up table to existing table name. 

As it was dev environment above mentioned approach is good but I'm still looking for actual root cause of this issue.

Note: If we remove function from the index it was working fine. 

Also we noticed that in dev environment index was enabled for parallel. Also there is another PK index defined that is overlapping most of the columns with this index.