【Oracleデータリカバリ】ORA-08102エラを診断する

プロのOracle Databaseの復旧サービスを提供
携帯番号: +86 13764045638 メール:[email protected]

 

 

 

[oracle@mlab2 ~]$ oerr ora 81020

 

8102, 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: 16key: (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_objectsWHERE  object_id = 46115;

 

ANALYZE TABLEを使って

VALIDATE STRUCTURE CASCADE;コマンドで検証して、テーブルとインディクスが一致していないであればORA-1499エラになる:

ANALYZE TABLE

VALIDATE STRUCTURE CASCADE;

全テーブルスキャンとインディクススキャンの結果も比べられる:

 

SELECT /*+ FULL(t1) */ <indexed column list>FROM <Table name> t1MINUSSELECT /*+ index(t <Index name>) */ <indexed column list>FROM <Table name> t;

 

 

例えばテーブルの名前は DEPT, Index Name 为I_DEPT1, インディクスI_DEPT1 はDEPTNO, DNAME.

SELECT /*+ FULL(t1) */ deptno, dnameFROM dept t1MINUSSELECT /*+ index(t I_DEPT1) */ deptno, dnameFROM dept t;

 

そのクエリの実行計画は損害したインディクスを使ったことを確保する必要がある。実行計画にI_DEPT1があるかどうかという方法で確認できる。

 

ORA-8102はORACLEのbugあるいはハードウェアI/Oエラで引き起こした。

ハードウェアあるいはI/Oサブシステムが書き込みをなくしたので、ブロックロジックエラになる。Lost Ioが起きて、keyに対する修正あるいはOracleのデータファイルに書き込まれていない。

 

 ORA-8102の解決策

 

もしテーブルとインディクスが一致していないからORA-8102エラを引き起こしたことを確認できたら、dropとインディクス再構造で大体解決できる。

 

けど損害がテーブルに起きたら、解決策はテーブルの損害ブロックを独立で修正するあるいはテーブルを再構造する。

エラが起こったのはLOB Indexの場合、LOBを移動してLOB INDEXを再構造する。

 

alter table &table_owner.&table_with_lobmove 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

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *