[oracle@mlab2 ~]$ oerr ora 8102 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
ORA-8102错误出现的原理是当表或者LOB SEGMENT上存在一个键值，但是该键值在索引上却找不到时，则出现错误。
其TRACE部分类似于：
oer 8102.<code> - obj# <object id>, rdba: <rdba value>(afn <file#>, blk# <block#>) kdk key 8102.2: ncol: <number of columns in the key including the rowid>, len: <key length> key: (<length>):<hexadecimal value>
其中 obj#为 受影响对象的object_id， rdba为相对数据块地址，AFN为绝对文件号，blk#为 该key应当存放在的索引的块号。
如下面的例子：
SQL> DELETE dept WHERE deptno=10; DELETE dept WHERE deptno=10 * ERROR at line 1: ORA-08102: index key not found, obj# 46115, file 5, block 90 (2) trace文件中出现： oer 8102.2 - obj# 46115, rdba: 0x02c0005a(afn 5, blk# 90) kdk key 8102.2: ncol: 3, len: 16 key: (16): 06 c5 02 01 01 27 02 04 c3 02 32 33 06 02 c0 00 4a 00 05
首先定位受影响的是哪个索引
错误信息和trace中都指出了受影响的索引的obj#:
SELECT * FROM dba_objects WHERE object_id = 46115;
使用ANALYZE TABLE
VALIDATE STRUCTURE CASCADE;命令来验证，如果确实存在表和索引的不一致则会出现ORA-1499错误：
ANALYZE TABLE
VALIDATE STRUCTURE CASCADE;
也可以选择 通过全表扫描的结果与索引扫描的结果对比：
SELECT /*+ FULL(t1) */ <indexed column list> FROM <Table name> t1 MINUS SELECT /*+ index(t <Index name>) */ <indexed column list> FROM <Table name> t;
例如表名 为 DEPT， Index Name 为I_DEPT1， 索引I_DEPT1 上的字段为DEPTNO, DNAME.
SELECT /*+ FULL(t1) */ deptno, dname FROM dept t1 MINUS SELECT /*+ index(t I_DEPT1) */ deptno, dname FROM dept t;
需要保证该查询的执行计划确实使用了受损的索引，可以通过查看执行计划中是否有I_DEPT1来确认。
ORA-8102即可能是ORACLE的bug，也可能是由于硬件I/O错误所引起。
硬件或者I/O子系统由于丢失写 Lost Write造成块的逻辑上讹误，当一个Lost Io发生，包含对key的修改或者没有写入到ORACLE数据文件上，这即可能发生在表块上也可能发生在索引块上。
对于 ORA-8102的解决
如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
若已经确认是由于表和索引间的不一致引起的ORA-8102，则drop和重建索引可以解决大部分情况。
但是如果确认是表上存在的损坏，则解决方法可以是 单独修复表上的损坏块 或者 考虑重建表。
如果发生错误的是LOB Index，则移动LOB并重建LOB INDEX
alter table &table_owner.&table_with_lob move LOB (&&lob_column) store as (tablespace &tablespace_name);
| NB | Bug | Fixed | Description |
| 14222244 | 11.2.0.4, 12.1.0.1 | Adding a column with DEFAULT and NOT NULL constraint disabled causes problems – superseded | |
| 13073122 | 11.2.0.4, 12.1.0.1 | ORA-8102 signaled by q000* processes operating on queues with retention | |
| + | 17761775 | 11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4.BP03, 12.1.0.2 | ORA-600 [kclchkblkdma_3] ORA-600 [3020] or ORA-600 [kcbchg1_16] Join of temp and permanent table in RAC might lead to corruption |
| 17449815 | 12.1.0.2, 12.2.0.0 | ORA-8102 ORA-1499 after ORA-1/ORA-2291 by MERGE with DML ERROR LOGGING | |
| 16844448 | 11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4, 12.1.0.2 | ORA-600 [3020] after flashback database in a RAC | |
| 13708951 | 11.2.0.4, 12.1.0.1 | ORA-8102 on UPDATE statement with subquery for an indexed column | |
| 13146182 | 11.2.0.2.11, 11.2.0.2.BP17, 11.2.0.3.BP07, 11.2.0.4, 12.1.0.1 | ORA-1499 ORA-8102 ORA-600 [kdsgrp1] Bitmap Index / Table mismatch | |
| P | 12330911 | 12.1.0.1 | EXADATA LSI firmware for lost writes |
| 11778458 | 11.2.0.3, 12.1.0.1 | Wrong Results / ORA-1802 on TO_CHAR with CURSOR_SHARING | |
| 10633840 | 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3, 12.1.0.1 | ORA-1502 on insert statement on INTERVAL partitioned table. ORA-8102 / ORA-1499 Index inconsistency | |
| 10245259 | 11.2.0.2.BP03, 11.2.0.3, 12.1.0.1 | PARALLEL INSERT with +NOAPPEND hint or if PARALLEL INSERT plan is executed in SERIAL corrupts index and causes wrong results | |
| + | 10209232 | 11.1.0.7.7, 11.2.0.1.BP08, 11.2.0.2.1, 11.2.0.2.BP02, 11.2.0.2.GIBUNDLE01, 11.2.0.3, 12.1.0.1 | ORA-1578 / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM |
| + | 9734539 | 11.2.0.2, 12.1.0.1 | ORA-8102 / ORA-1499 corrupt index after update/merge using QUERY REWRITE |
| + | 9469117 | 10.2.0.5.4, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.1 | Corrupt index after PDML executed in serial. Wrong results. OERI[kdsgrp1]/ORA-1499 by analyze |
| + | 9231605 | 11.1.0.7.4, 11.2.0.1.3, 11.2.0.1.BP02, 11.2.0.2, 12.1.0.1 | Block corruption with missing row on a compressed table after DELETE |
| + | 8951812 | 11.2.0.2, 12.1.0.1 | Corrupt index by rebuild online. Possible OERI [kddummy_blkchk] by SMON |
| 8847637 | 11.2.0.3, 12.1.0.1 | ORA-7445[kxibPut] caused by merge stmt and online index rebuild | |
| 8720802 | 10.2.0.5, 11.2.0.1.BP07, 11.2.0.2, 12.1.0.1 | Add check for row piece pointing to itself (db_block_checking,dbv,rman,analyze) | |
| + | 8546356 | 10.2.0.5.1, 11.2.0.1.3, 11.2.0.1.BP07, 11.2.0.2, 12.1.0.1 | ORA-8102/ORA-1499/OERI[kdsgrp1] Composite Partitioned Index corruption after rebuild ONLINE in RAC |
| 7710827 | 11.2.0.2, 12.1.0.1 | Index rebuild or Merge partition causes wrong results in concurrent reads instead of ORA-8103 | |
| 7705591 | 10.2.0.5, 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.1 | Corruption with self-referenced row in MSSM tablespace. Wrong Results / OERI[6749] / ORA-8102 | |
| + | 17752121 | 11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4.BP03 | ORA-600 [kclchkblkdma_3] ORA-600 [3020] RAC diagnostic/fix to avoid a block being modified in Shared Mode and prevent corruption |
| 16922996 | 11.2.0.4 | ORA-8102 ORA-1499 Internal rollback in Parallel DML may cause index inconsistency | |
| 8588540 | 11.1.0.7.2, 11.2.0.1 | Corruption / ORA-8102 in RAC with loopback DB links between instances | |
| 8514561 | 11.2.0.1 | ORA-8102 updating a table with function based index and TYPE columns and a TRIGGER | |
| + | 7329252 | 10.2.0.4.4, 10.2.0.5, 11.1.0.7.5, 11.2.0.1 | ORA-8102/ORA-1499/OERI[kdsgrp1] Index corruption after rebuild index ONLINE |
| 6057203 | 10.2.0.4, 11.1.0.7, 11.2.0.1 | Corruption with zero length column (ZLC) / OERI [kcbchg1_6] from Parallel update | |
| 5621677 | 10.2.0.4, 11.1.0.6 | Logical corruption with PARALLEL update | |
| 5181547 | 10.2.0.4, 11.1.0.6 | Index corruption after insert-only merge /*+ append */ or PDML into table | |
| 5179313 | 10.2.0.4, 11.1.0.6 | INSERT /*append parallel*/ can corrupt an index | |
| 4883635 | 10.2.0.4, 11.1.0.6 | MERGE (with DELETE) can produce wrong results or Logical corruption in chained rows | |
| * | 4570793 | 10.2.0.2 | Index corruption from array inserts (ORA-8102/ORA-1499) |
| 4246090 | 9.2.0.8, 10.1.0.5, 10.2.0.1 | IOT corruption from buffered INSERT with function based index (ORA-8102) | |
| 3573604 | 10.1.0.4, 10.2.0.1 | A transported bitmap index can give various OERI errors / ORA-8102 | |
| 3365045 | 9.2.0.6, 10.1.0.3, 10.2.0.1 | Functional index on DATE column can depend on NLS_DATE_FORMAT (ORA-8102 on DML) | |
| 3352413 | 9.2.0.6, 10.1.0.3, 10.2.0.1 | An ORA-8102 error can occur on ATEMPIND$ during a user UPDATE with CONSTRAINTS | |
| 3069818 | 10.1.0.4, 10.2.0.1, 9.2.0.6 | Corruption possible modifying a migrated or chained row | |
| 2485931 | 9.2.0.2, 10.1.0.2 | ORA-8102 from IOT DML with concurrent MOVE ONLINE | |
| 2293492 | 9.0.1.4, 9.2.0.2, 10.1.0.2 | Fatal error during COMMIT / ROLLBACK may cause permanent corruption (eg: ORA-8102) | |
| 2511906 | 9.2.0.2 | ORA-8102 possible on update of IOT | |
| 2405013 | 9.2.0.2 | ORA-8102 on ALTER TABLE MOVE PARTITION COMPRESS UPDATE GLOBAL INDEXES | |
| 2271722 | 9.0.1.4, 9.2.0.1 | ORA-8102 possible on update of IOT with OVERFLOW | |
| 2165461 | 9.2.0.1 | Direct load to table with DESCENDING index may cause subsequent ORA-8102 errors | |
| 2131767 | 9.2.0.1 | Parallel create of FUNCTIONAL INDEX on PARTITION table can product corrupt index (ORA-8102) | |
| 2456255 | 9.0.1.0 | ORA-8102 on DELETE from PARTITIONED table with index | |
| 1667103 | 8.1.7.2, 9.0.1.0 | Update of an IOT with CONCATENATION using a SECONDARY index signals ORA-8102 | |
| 1388843 | 8.1.7.3, 9.0.1.0 | UNIQUE/PK constraints ENFORCED with NON-UNIQUE COMPRESSED indexes allow duplicates / ORA-8102 | |
| 536567 | 7.3.4.4, 8.0.4.3, 8.0.5.1, 8.0.6.0 | Corrupt index from PARALLEL Index build/rebuild of CONCAT index if FFS used and leading columns are NULL. |
Steps to Diagnose the issue: 1) Find the index select * from dba_objects where object_id=; 2) Find the table select table_name from dba_indexes where index_name=’object_name from 1′; 3) analyze index validate structure; analyze table validate structure cascade; analyze table validate structure; 4) ORA-8102 trace file will show you at the beginning what row is not found in the index Eg:Excerpt from the ora-8102 trace file:*** SERVICE NAME:(SYS$USERS) 2009-05-05 20:37:21.144 *** SESSION ID:(64.22684) 2009-05-05 20:37:21.144 oer 8102.2 – obj# 61828, rdba: 0x01c05fc4(afn 80, blk# 24516) kdk key 8102.2: ncol: 8, len: 43 key: (43): 0c 63 6f 6e 66 69 72 6d 61 74 69 6f 6e 01 30 ff 03 43 53 45 ff 08 4e 65 77 20 59 6f 72 6b 05 32 30 2d 33 30 06 0f c4 29 be 00 00 The key contains all the columns from the index: 0c 63 6f 6e 66 69 72 6d 61 74 69 6f 6e 01 30 ff 03 43 53 45 ff 08 4e 65 77 20 59 6f 72 6b 05 32 30 2d 33 30 06 0f c4 29 be 00 00 —-> last column in this case is the rowid 0x0fc429be slot 0 a) dump the rdba: 0x01c05fc4(afn 80, blk# 24516) alter system dump datafile 80 block 24516; b) check if the above key is in the index block c) if it is not drop and recreate the index d) if the key it is in the index than you will have to check the table using the rowid Causes:==========There are a number of possibilities for corruption: – memory problems cause dropped bits – disk repair utilities zeroing out blocks – system crashes – in 10.2 Bug:7329252 Index corruption after rebuild index ONLINE Fixed-Releases: B200 Details: If there is a heavy concurrency on a table during online index rebuild, the index can be corrupt missing some keys. The next errors can be produced by sql statements: ORA-8102 by a delete/update ORA-1499 by “analyze table validate structure cascade” ORA-600 [kdsgrp1] ORA-600 [qertbFetchByRowID] REFERENCES