【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    邮箱: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.

Comments

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

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号