Sunday, August 31, 2008

ORA-08102: index key not found, obj# ... Solution to resolve

When updated table, found error:

ORA-08102: index key not found, obj# 116528, file 96, block 8795 (2)

This problem I had resolved by rebuild indexes (ORA-08102 on index objects). But today I can not use this solution:

What is about this error?

Error: ORA-08102

Oracle10g Message
Text: index key not found, obj# %s, file %s, block %s (%s)
Oracle 9.2 or Earlier Error Message
Text: index key not found, obj# %s, dba %s (%s)

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

What typically happens is the index is built and at some future time, some type of corruption occurs, either in the table or index, to cause the mismatch.

When know about is, what is the way to resolve it? How?

-> Check object, that have the problem, if it's index... try to rebuild:

SQL>select object_name, object_type
from dba_objects
where object_id = [obj# in ORA-08102]

Example:

SQL> select object_name, object_type
from dba_objects
where object_id = 116528;

OBJECT_NAME OBJECT_TYPE
--------------- -------------------
XXX_IDX07 INDEX PARTITION

And then rebuild it.

SQL> alter index [index_name] rebuild [online];

OR "partition index"

SQL> select partition_name from dba_segments where TABLESPACE_NAME = (select tablespace_name from dba_data_files where file_id=[file_id from ORA-08102] ) and SEGMENT_NAME=[object_name from dba_objects];

SQL> select partition_name from dba_segments where TABLESPACE_NAME = (select tablespace_name from dba_data_files where file_id=96) and SEGMENT_NAME='XXX_IDX07';

PARTITION_NAME
------------------------------
XXX_PART1


SQL> alter index [index_name] rebuild partition [partition_name] [online];

if ORA-08102 on table objects -> resolve by "analyze table ... validate structure.. or use dbv to check corrupt on table" ) don't forget check error on OS , trace file and alert log file.

-> (ORA-08102 on index objects), if rebuilt indexes, But still error ORA-08102: Drop that index and recreate it.


3 comments:

Paweł Barut said...

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.

/Paweł

Jimmy said...

Dropping the index and recreating it seems to resolve the error.

BaLAJi said...

Rebuilding the Index resolved the issue.Nice Post