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

[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 邮箱：[email protected]

若已经确认是由于表和索引间的不一致引起的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.

Posted

February 13, 2013

mac

Tags:

Comments

One response to “【Oracle数据恢复】诊断ORA-08102错误”

Ask_Maclean_liu_Oracle

February 13, 2014

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

Reply