Block Corruption – ORA-08102: index key not found

We had a refresh job which started failing with the following error:

Error at line 2
ORA-12008: error in materialized view refresh path ORA-08102: index key not found, obj# 6104122, file 364, block 27923 (2) ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 2

ORA-08102 is a mismatch between the key(s) stored in the index and the values stored in the table.

To resolve i first found out which object was affected:

select owner, object_name, object_type
from dba_objects
where object_id = 6104122;

Which returned the schema.I_MY_OBJECT

This was an index so then the next step was to rebuild the index

ALTER INDEX SCHEMA.I_MY_OBJECT rebuild;

but this didn’t resolve the error. Research found the following:

Rebuilding index will not work on most (if not in all) cases. Rebuilding index does not visit table at all. It uses existing index structure, to create new segment, so this problem will be transferred to new index as well.

In the end i dropped and recreated the index which seemed to resolve the error.

Advertisements