ORA-08102: Index Key Not Found

Symptom :

ORA-08102,ORA-00600,DBSQL_SQL_ERROR,CX_SY_OPEN_SQL_DB,

Index Corruption

Solution:

1. Check if you have any runtime errors in ST22:

For Eg : 

Parameters:

P1 "DD03L"

P2 "SQL code: 8102"

P3 "SQL message: ORA-08102: index key not found, obj# 612873, file 161, block

 170560 (2)"

P4 "SQL dbsl rc: 99"

2. Check if you can identify the table having the issue from the runtime error:

For Eg:

Here issue is with table "DD03L"

3. Finding out the table and related indexes:

select object_name,owner, object_type from dba_objects where object_name like 'DD03L%';

select INDEX_NAME,owner from dba_indexes where table_name='DD03L';

4. Analyzing the table and all the related index to check if any corruption:

ANALYZE TABLE <OWNER>."<TABLE_NAME>" VALIDATE STRUCTURE ONLINE;

ANALYZE INDEX <OWNER>."<INDEX_NAME>" VALIDATE STRUCTURE ONLINE;

Eg:

SQL> ANALYZE table SAPSR3."DD03L" VALIDATE STRUCTURE ONLINE;

Table analyzed  ->(This means table is successfully analyzed and no issues)

SQL> ANALYZE INDEX SAPSR3."DD03L~1" VALIDATE STRUCTURE ONLINE;

Index analyzed->(This means index is successfully analyzed and no issues)

In this case the table and the indexes usually get successfully analyzed.

5. Identify the object in the SAP runtime error:

Here the obj# is 612873

SQL> select object_name,object_type from all_objects where object_id=612873;

This will return the index which is having the issue

6.  Save the structure of  the indexe having the issues:

SQL> set pagesize 0

SQL> set long 90000

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','<INDEX_NAME>','<OWNER>') from dual;

7. Drop the index having the issue:

SQL> DROP INDEX "OWNER"."INDEX_NAME";

8. Recreate the index with the saved structure in point 6.

9. Analyze the index again to find if the issue is solved:

ANALYZE INDEX <OWNER>."<INDEX_NAME>" VALIDATE STRUCTURE ONLINE;

Refer SAP Note : 2373505 - 12c: Corrupt index with CREATE INDEX ONLINE

Hope this helps!!!

Abraham