ORA-08102 means there is a mismatch between keys (values) stored in an index and values of indexed column in the table.

[oracle@joedb~]$ oerr ora 08102
08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)"
// *Cause:  Internal error: possible inconsistency in index
// *Action:  Send trace file to your customer support representative, along
//           with information on reproducing the error

After an index has been created for a while, some kind of corruption happens which causes either the data in table or index changed — mismatching.

When this happens, the application will get the ORA-08102 errors during accessing the corresponding table(index). You won’t see the error explicitly in the alert.log, but something like:

*****************************************************************
2021-03-18T02:06:22.424310-05:00
Errors in file /opt/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_4611.trc:
2021-03-18T02:06:27.408745-05:00
*****************************************************************

The trace file actually has the information about it like:

oer 8102.2 - obj# <object id>, rdba: <rdba value>(afn <file#>, blk# <block#>)

So searching trace file with the pattern “oer 8102” will tell how many trace files generated during a period for this error. With the information of object#, file#, block# we can easily identify which file and which index has this mismtach:

SQL> select file#, TS#, name, STATUS, enabled from v$datafile where file#=34;

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

SQL> select tablespace_name, segment_type, owner, segment_name from dba_extents where file_id = 34 and 5830 between block_id AND block_id + blocks-1;

DBV might not find anything if you run it against the data file affected unless there is a real block corruption.It is the same for RMAN validate.

[oracle@joedb ]$ dbv file=/dbase/data/PROD/data_ind_115.dbf BLOCKSIZE=8192

DBVERIFY: Release 19.0.0.0.0 - Production on Thu Mar 18 17:04:58 2021

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /dbase/data/PROD/data_ind_115.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 2097152
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 1365090
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 732061
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 347811302333 (80.4213918653)

Additionally we can run “analyze index/table <index/table name> validate structure” to detect the corruption. Nevertheless, rebuild the index is sufficient if the table is good.

When considering rebuild an index, there are two options,

1. use rebuild clause with alter index. It’s faster and when using with online clause, the application can still access the index. However, it uses the existing index as the data source. In this case, it might not solve the mismatch.
2. drop the index, then create. It needs to re-scan the table, slower, but will solve the mismatch issue if the underlying table is good.

An important thing here is when the index is a function-based index, double check the index defintion and make sure the function is deterministic, especially for a user defined function which must be declared as DETERMINISTIC and the plsql exception handler should not ignore error ORA-4091. That’s because index keys is based on the returned values of the fucntion used in the index definition.

A deterministic function must return the same value on two distinct invocations if the arguments provided to the two invocations are the same.

Oracle doesn’t verify if a function is DETERMINISTIC when it is declared so. That means whoever writes the function has to make sure the function is actually deterministic, not just declare it is.

Non-deterministic Functional indexes like the ones depending on NLS settings are exposed to ORA-8102.

References:

1. Master Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)
2. OERR: ORA-8102 “index key not found, obj# %s, file %s, block %s (%s)” (Doc ID 8102.1)
3. ORA-8102 On Select / Update with Function Based Index using Timestamp Column (Doc ID 568005.1)
4. ORA-8102 at update when having a FunctionBasedIndex(FBI) index present based on incorrect function (Doc ID 1067112.1)

• Oracle