OERR: ORA-1499 table/Index Cross Reference Failure - see trace file [ID 1499.1]

rongshiyuan發表於2013-01-22
IndexROSAI
OERR: ORA-1499 table/Index Cross Reference Failure - see trace file [ID 1499.1]

In this Document
Purpose
Scope
OERR: ORA-1499 table/Index Cross Reference Failure - see trace file
Description
What index is affected?
Identify all the affected keys
Cause
Solution
References

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.2.0.2 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.

Purpose

This article provides information about error ORA-1499 and possible actions.

Scope

This note is intended for general audience as initial starting point for beginning diagnosis of ORA-1499.

OERR: ORA-1499 table/Index Cross Reference Failure - see trace file


Error: ORA 1499
Text: table/Index Cross Reference Failure - see trace file
-------------------------------------------------------------------------------
Cause: An error occurred when validating an index or a table using the
ANALYZE command.
One or more entries does not point to the appropriate cross-reference.
Action: Check the trace file for more descriptive messages about the problem.
Correct these errors.

Description

Error ORA-1499 is produced by statement "ANALIZE TABLE|CLUSTER VALIDATE STRUCTURE CASCADE" to report an inconsistency between a table or a cluster and its index where an index key value is not found in the index or vice versa.

The content of the trace file has:

: tsn: rdba:

description:
"row not found in index"
"Table/Index row count mismatch"
"row mismatch in index dba"
"Table row count/Bitmap index bit count mismatch"
"kdavls: kdcchk returns %d when checking cluster dba 0x%08lx objn %d\n"
tsn: Tablespace Number where the INDEX is stored.
rdba: Relative data block address of the INDEX segment header.

SQL> analyze table DEPT validate structure cascade;
analyze table case7 validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Example from a trace file:

row not found in index tsn: 5 rdba: 0x02c00061

What index is affected?

The trace file for ORA-1499 provides the rdba for the segment header related to the index. Query dba_segments to identify the index:

SELECT owner, segment_name, segment_type, partition_name
FROM DBA_SEGMENTS
WHERE header_file = (SELECT file#
FROM v$datafile
WHERE rfile# = dbms_utility.data_block_address_file(to_number('&rdba','XXXXXXXX'))
AND ts#= &tsn)
AND header_block = dbms_utility.data_block_address_block(to_number('&rdba','XXXXXXXX'));

The &rdba value should be the rdba in hexadecimal above removing the '0x' portion and tsn is the tablespace number. Example:

SELECT owner, segment_name, segment_type, partition_name
FROM DBA_SEGMENTS
WHERE header_file = (SELECT file#
FROM v$datafile
WHERE rfile# = dbms_utility.data_block_address_file(to_number('02c00061','XXXXXXXX'))
AND ts#= 5)
AND header_block = dbms_utility.data_block_address_block(to_number('02c00061','XXXXXXXX'));


Identify all the affected keys

Optionally if it required to identify all the affected keys, a full table scan with an index scan can be run:

Rows in the table that are not in the index:

SELECT /*+ FULL(t1) */ rowid,
FROM t1
MINUS
SELECT /*+ index(t ) */ rowid,
FROM
t;
Rows in the index that are not in the table:

SELECT /*+ index(t ) */ rowid,
FROM
t
MINUS
SELECT /*+ FULL(t1) */ rowid,
FROM
t1
Example:

Table name = DEPT, Index name = I_DEPT1, Indexed columns in index I_DEPT1 are: DEPTNO, DNAME.

SELECT /*+ FULL(t1) */ rowid, deptno, dname
FROM dept t1
MINUS
SELECT /*+ index(t I_DEPT1) */ rowid, deptno, dname
FROM dept t;

Make sure that the execution plan for the query uses the affected index; e.g. Index I_DEPT1 is shown in the execution plan.

Cause

The root cause for an inconsistency between the table and its index can be and Oracle defect or a problem external to Oracle like Lost IO:

Oracle defect.

This inconsistency can be introduced by an Oracle bug.

Hardware or IO

Hardware or IO subsystem problems can cause Lost IO / Lost Write. When there is a Lost IO, the block modification containing the key either in the table or in the index does not arrive to the Oracle Datafile causing a missing key.

Solution

For message "Table/Index row count mismatch" reference Note 563070.1

When an inconsistency is identified between the table and the index where the row is not found in the index, dropping and recreating the index can be the appropriate action most of the times.

Known Bugs
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:



NBBugFixedDescription
1314618211.2.0.2.BP17, 11.2.0.3.BP07, 12.1.0.0ORA-1499 ORA-8102 ORA-600 [kdsgrp1] Bitmap Index / Table mismatch V11020002 V11020003 BITMAP CORR/IND ERROR OERI WRONGRES
1186542011.2.0.4, 12.1.0.0Insert as Select with LOG ERRORS INTO slower than expected / corruption using IGNORE_ROW_ON_DUPKEY_INDEX hint V11020001 V11020002 V11020003 CORR/IND CORR/LOG OPERF
1063384011.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3, 12.1.0.0ORA-1502 on insert statement on INTERVAL partitioned table. ORA-8102 / ORA-1499 Index inconsistency V11010006 V11010007 V11020001 V11020002 CORR/IND ERROR OERI PART
1024525911.2.0.2.BP03, 11.2.0.3, 12.1.0.0PARALLEL INSERT with +NOAPPEND hint or if PARALLEL INSERT plan is executed in SERIAL corrupts index and causes wrong results V11020001 V11020002 CORR/IND ERROR OERI PQO WRONGRES
+1020923211.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.0ORA-1578 / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM V11010006 V11010007 V11020001 V11020002 ASM CORR CORR/LOG CORR/PHY ERROR OERI
+973453911.2.0.2, 12.1.0.0ORA-8102 / ORA-1499 corrupt index after update/merge using QUERY REWRITE V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 CORR/IND ERROR MVIEW OERI WRONGRES
+946911710.2.0.5.4, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.0Corrupt index after PDML executed in serial. Wrong results. OERI[kdsgrp1]/ORA-1499 by analyze V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 CORR/IND ERROR OERI PQO UPSERT WRONGRES
+923160511.1.0.7.4, 11.2.0.1.3, 11.2.0.1.BP02, 11.2.0.2, 12.1.0.0Block corruption with missing row on a compressed table after DELETE V11010006 V11010007 V11020001 COMPRESS CORR CORR/IND CORR/LOG DUMP ERROR OERI WRONGRES
922989111.2.0.2, 12.1.0.0Bitmap index corruption by update or delete with error logging V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 BITMAP CORR/IND ERROR OERI WRONGRES
+895181211.2.0.2, 12.1.0.0Corrupt index by rebuild online. Possible OERI [kddummy_blkchk] by SMON V11010006 V11010007 V11020001 CORR/IND CRASH ERROR OERI ONLINE WRONGRES
+881563910.2.0.4.4, 10.2.0.5, 11.1.0.7.3, 11.2.0.1.2, 11.2.0.1.BP06, 11.2.0.2, 12.1.0.0Multiple INSERTS with allocation above HWM in ASSM with wrong results / ORA-1499 by analyze V09020008 V10010005 V10020002 V10020003 V10020004 V11010006 V11010007 V11020001 BITSEG CORR/LOG ERROR SPACELOC WRONGRES
871606411.2.0.2, 12.1.0.0Analyze Table Validate Structure fails on ADG standby with several errors V11010006 V11010007 V11020001 ADG CORR/FALSE ERROR OERI STANDBY
+859710611.2.0.1.BP06, 11.2.0.2, 12.1.0.0Lost Write in ASM when normal redundancy is used V11010006 V11010007 V11020001 ASM CORR CORR/PHY ERROR OERI
+854635610.2.0.5.1, 11.2.0.1.3, 11.2.0.1.BP07, 11.2.0.2, 12.1.0.0ORA-8102/ORA-1499/OERI[kdsgrp1] Composite Partitioned Index corruption after rebuild ONLINE in RAC V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 CORR/IND ERROR OERI ONLINE OPS PART UNUSABLE WRONGRES
771082711.2.0.2, 12.1.0.0Index rebuild or Merge partition causes wrong results in concurrent reads instead of ORA-8103 V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 CORR/IND ERROR PART WRONGRES
770559110.2.0.5, 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.0Corruption with self-referenced row in MSSM tablespace. Wrong Results / OERI[6749] / ORA-8102 V09020008 V10010005 V10020002 V10020003 V10020004 V11010006 V11010007 V11020001 CORR/LOG ERROR OERI WRONGRES
725104911.2.0.1.BP08, 11.2.0.2, 12.1.0.0Corruption in bitmap index introduced when using transportable tablespaces V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 BITMAP CORR/IND ERROR OERI TRANSPORT WRONGRES
P1233091112.1EXADATA LSI firmware for lost writes V11010006 V11010007 V11020001 V11020002 V11020003 ASM CORR/LOG CORR/PHY ERROR EXADATA OERI P226
1273194011.2.0.3.BP08Analyze fails with ORA-600 [kghfrh:ds] / ORA-600 [17147] / ORA-1499 V11020001 V11020002 V11020003 MEMCOR OERI
858854011.1.0.7.2, 11.2.0.1Corruption / ORA-8102 in RAC with loopback DB links between instances V11010006 V11010007 CORR CORR/IND CORR/LOG DBLINK ERROR OPS WRONGRES
750971410.2.0.5.5, 11.2.0.1Rebuild index of a subpartitioned index fails with ORA-1 or produces a corrupt index V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 CORR/DIC CORR/IND CORR/PHY ERROR HCHECK PART
+732925210.2.0.4.4, 10.2.0.5, 11.1.0.7.5, 11.2.0.1ORA-8102/ORA-1499/OERI[kdsgrp1] Index corruption after rebuild index ONLINE V10020002 V10020003 V10020004 V11010006 V11010007 CORR/IND ERROR OERI ONLINE WRONGRES
574746210.2.0.4.1, 10.2.0.5, 11.1.0.7Poor performance of ANALYZE TABLE VALIDATE STRUCTURE CASCADE or false ORA-1499 with ONLINE in RAC V10010005 V10020002 V10020003 V10020004 V11010006 CORR/FALSE ERROR ONLINE OPERF OPS
596786110.2.0.5, 11.1.0.6Bitmap index corruption from INSERT with DML error logging V10020002 V10020003 V10020004 BITMAP CORR/IND ERROR WRONGRES
562167710.2.0.4, 11.1.0.6Logical corruption with PARALLEL update V10020002 V10020003 COMPRESS CORR/IND CORR/LOG PQO WRONGRES
525327110.2.0.3, 11.1.0.6Wrong sort ordering for zeroes V10020002 CORR/IND NLS WRONGRES
518154710.2.0.4, 11.1.0.6Index corruption after insert-only merge /*+ append */ or PDML into table V10020002 V10020003 CORR/IND DPATH ERROR PQO UPSERT WRONGRES
517931310.2.0.4, 11.1.0.6INSERT /*append parallel*/ can corrupt an index V10010005 V10020002 V10020003 CORR/IND DPATH ERROR PQO WRONGRES
489868510.2.0.3, 11.1.0.6Analyze table raises false ORA-1499 on table with range partitioned/bitmap index V10020002 CORR/FALSE CORR/IND ERROR PART
488363510.2.0.4, 11.1.0.6MERGE (with DELETE) can produce wrong results or Logical corruption in chained rows V10020002 V10020003 CORR/LOG MVIEW UPSERT WRONGRES
45174709.2.0.8, 10.2.0.2, 11.1.0.6Table corruption with RAC when ASSM managed and truncate used V10010005 BITSEG CORR/LOG OPS TRUNCATE WRONGRES
*457079310.2.0.2Index corruption from array inserts (ORA-8102/ORA-1499) CORR/IND RA201 REGRESSION
40538889.2.0.8, 10.1.0.4, 10.2.0.1ORA-1499 diagnostic trace does not dump REDO for the table block CORR DIAG
38235509.2.0.7, 10.1.0.5, 10.2.0.1False ORA-1499 validating a table CASCADE ONLINE with a bitmap index BITMAP CORR/FALSE
36228759.2.0.6, 10.1.0.4, 10.2.0.1False ORA-1499 from ANALYZE TABLE VALIDATE STRUCTURE CASCADE ONLINE BITMAP CORR/FALSE ONLINE
34406899.2.0.7, 10.1.0.5, 10.2.0.1Block corruption possible inserting large row piece to empty block CORR/PHY
33650459.2.0.6, 10.1.0.3, 10.2.0.1Functional index on DATE column can depend on NLS_DATE_FORMAT (ORA-8102 on DML) CORR/LOG ERROR FUNCINDEX
30698189.2.0.6, 10.1.0.4, 10.2.0.1Corruption possible modifying a migrated or chained row CORR/PHY
20936709.2.0.5, 10.1.0.2OERI:12700 / BITMAP index corruption possible BITMAP CORR/IND
19129529.2.0.1ANALYZE TABLE .. VALIDATE STRUCTURE .. ONLINE may report false ORA-01499 CORR/FALSE ERROR ONLINE
13124258.1.7.0REVERSE KEY Global partitioned indexes are created as CORRUPT CORR/IND ERROR PART
12286588.1.6.2, 8.1.7.0Create INDEX on SNAPSHOT/MV can produce corrupt index (OERI:13004 / OERI:25012 / ORA-1499) CORR/IND ERROR MVIEW OERI REGRESSION
5331867.3.4.5, 8.0.4.0ORA-1499 / OERI:13011 after UPDATE of an inline view CORR/IND ERROR OERI
  • '*' indicates that an alert exists for that issue.
  • '+' indicates a particularly notable issue / bug.
  • 'P' indicates a port specific bug.
  • Fixed versions use "BPnn" to indicate Exadata bundle nn.
  • "OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].

References

NOTE:563070.1 - ORA-1499. Table/Index row count mismatch

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-753027/，如需轉載，請註明出處，否則將追究法律責任。

0
0
上一篇： Terminated With Error ORA-474: SMON Process Terminated With Error-1361872.1
下一篇： ORA-604、1632: Max. Extents (505) On Index SYS.I_OBJ2 Reached -1500033.1
OERR: ORA-1499 table/Index Cross Reference Failure - see trace file [ID 1499.1]
請登入後發表評論 登入
全部評論
rongshiyuan

註冊時間：2009-11-24

  • 博文量
    798
  • 訪問量
    3166266

最新文章

支援我們 作者招募 使用者協議 FAQ Contact Us

北京盛拓優訊資訊科技有限公司. 版權所有  京ICP備09055130號-4  北京市公安局海淀分局網監中心備案編號：11010802021510

廣播電視節目製作經營許可證(京) 字第1234號 中國網際網路協會會員

相關文章