プロのOracle Databaseの復旧サービスを提供
携帯番号： +86 13764045638 メール：service@parnassusdata.com
[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. |
Comment