ORA-8102

[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.

【Oracle数据恢复】诊断ORA-08102错误

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

【Oracle数据恢复】ORA-08102错误分析:JOB$与索引I_JOB_NEXT数据不一致

【Oracle数据恢复】诊断ORA-08102错误

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

【Oracle数据恢复】ORA-08102错误分析:JOB$与索引I_JOB_NEXT数据不一致

服务热线： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com