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.2Information 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.
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"tsn: Tablespace Number where the INDEX is stored.
"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"
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
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'));
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'));
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 Rows in the index that are not in the table:
FROM Rows in the index that are not in the table:
SELECT /*+ index(t ) */ rowid,
FROM
Make sure that the execution plan for the query uses the affected index; e.g. Index I_DEPT1 is shown in the execution plan.
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
FROM
SELECT /*+ FULL(t1) */ rowid, deptno, dname
FROM dept t1
MINUS
SELECT /*+ index(t I_DEPT1) */ rowid, deptno, dname
FROM dept t;
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.1When 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:
NB Bug Fixed Description 13146182 11.2.0.2.BP17, 11.2.0.3.BP07, 12.1.0.0 ORA-1499 ORA-8102 ORA-600 [kdsgrp1] Bitmap Index / Table mismatch V11020002 V11020003 BITMAP CORR/IND ERROR OERI WRONGRES 11865420 11.2.0.4, 12.1.0.0 Insert 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 10633840 11.2.0.2.7, 11.2.0.2.BP17, 11.2.0.3, 12.1.0.0 ORA-1502 on insert statement on INTERVAL partitioned table. ORA-8102 / ORA-1499 Index inconsistency V11010006 V11010007 V11020001 V11020002 CORR/IND ERROR OERI PART 10245259 11.2.0.2.BP03, 11.2.0.3, 12.1.0.0 PARALLEL 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 + 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.0 ORA-1578 / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM V11010006 V11010007 V11020001 V11020002 ASM CORR CORR/LOG CORR/PHY ERROR OERI + 9734539 11.2.0.2, 12.1.0.0 ORA-8102 / ORA-1499 corrupt index after update/merge using QUERY REWRITE V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 CORR/IND ERROR MVIEW OERI WRONGRES + 9469117 10.2.0.5.4, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.0 Corrupt 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 + 9231605 11.1.0.7.4, 11.2.0.1.3, 11.2.0.1.BP02, 11.2.0.2, 12.1.0.0 Block corruption with missing row on a compressed table after DELETE V11010006 V11010007 V11020001 COMPRESS CORR CORR/IND CORR/LOG DUMP ERROR OERI WRONGRES 9229891 11.2.0.2, 12.1.0.0 Bitmap index corruption by update or delete with error logging V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 BITMAP CORR/IND ERROR OERI WRONGRES + 8951812 11.2.0.2, 12.1.0.0 Corrupt index by rebuild online. Possible OERI [kddummy_blkchk] by SMON V11010006 V11010007 V11020001 CORR/IND CRASH ERROR OERI ONLINE WRONGRES + 8815639 10.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.0 Multiple 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 8716064 11.2.0.2, 12.1.0.0 Analyze Table Validate Structure fails on ADG standby with several errors V11010006 V11010007 V11020001 ADG CORR/FALSE ERROR OERI STANDBY + 8597106 11.2.0.1.BP06, 11.2.0.2, 12.1.0.0 Lost Write in ASM when normal redundancy is used V11010006 V11010007 V11020001 ASM CORR CORR/PHY ERROR OERI + 8546356 10.2.0.5.1, 11.2.0.1.3, 11.2.0.1.BP07, 11.2.0.2, 12.1.0.0 ORA-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 7710827 11.2.0.2, 12.1.0.0 Index 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 7705591 10.2.0.5, 11.2.0.1.1, 11.2.0.1.BP04, 11.2.0.2, 12.1.0.0 Corruption 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 7251049 11.2.0.1.BP08, 11.2.0.2, 12.1.0.0 Corruption in bitmap index introduced when using transportable tablespaces V10020002 V10020003 V10020004 V10020005 V11010006 V11010007 V11020001 BITMAP CORR/IND ERROR OERI TRANSPORT WRONGRES P 12330911 12.1 EXADATA LSI firmware for lost writes V11010006 V11010007 V11020001 V11020002 V11020003 ASM CORR/LOG CORR/PHY ERROR EXADATA OERI P226 12731940 11.2.0.3.BP08 Analyze fails with ORA-600 [kghfrh:ds] / ORA-600 [17147] / ORA-1499 V11020001 V11020002 V11020003 MEMCOR OERI 8588540 11.1.0.7.2, 11.2.0.1 Corruption / ORA-8102 in RAC with loopback DB links between instances V11010006 V11010007 CORR CORR/IND CORR/LOG DBLINK ERROR OPS WRONGRES 7509714 10.2.0.5.5, 11.2.0.1 Rebuild 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 + 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 V10020002 V10020003 V10020004 V11010006 V11010007 CORR/IND ERROR OERI ONLINE WRONGRES 5747462 10.2.0.4.1, 10.2.0.5, 11.1.0.7 Poor 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 5967861 10.2.0.5, 11.1.0.6 Bitmap index corruption from INSERT with DML error logging V10020002 V10020003 V10020004 BITMAP CORR/IND ERROR WRONGRES 5621677 10.2.0.4, 11.1.0.6 Logical corruption with PARALLEL update V10020002 V10020003 COMPRESS CORR/IND CORR/LOG PQO WRONGRES 5253271 10.2.0.3, 11.1.0.6 Wrong sort ordering for zeroes V10020002 CORR/IND NLS WRONGRES 5181547 10.2.0.4, 11.1.0.6 Index corruption after insert-only merge /*+ append */ or PDML into table V10020002 V10020003 CORR/IND DPATH ERROR PQO UPSERT WRONGRES 5179313 10.2.0.4, 11.1.0.6 INSERT /*append parallel*/ can corrupt an index V10010005 V10020002 V10020003 CORR/IND DPATH ERROR PQO WRONGRES 4898685 10.2.0.3, 11.1.0.6 Analyze table raises false ORA-1499 on table with range partitioned/bitmap index V10020002 CORR/FALSE CORR/IND ERROR PART 4883635 10.2.0.4, 11.1.0.6 MERGE (with DELETE) can produce wrong results or Logical corruption in chained rows V10020002 V10020003 CORR/LOG MVIEW UPSERT WRONGRES 4517470 9.2.0.8, 10.2.0.2, 11.1.0.6 Table corruption with RAC when ASSM managed and truncate used V10010005 BITSEG CORR/LOG OPS TRUNCATE WRONGRES * 4570793 10.2.0.2 Index corruption from array inserts (ORA-8102/ORA-1499) CORR/IND RA201 REGRESSION 4053888 9.2.0.8, 10.1.0.4, 10.2.0.1 ORA-1499 diagnostic trace does not dump REDO for the table block CORR DIAG 3823550 9.2.0.7, 10.1.0.5, 10.2.0.1 False ORA-1499 validating a table CASCADE ONLINE with a bitmap index BITMAP CORR/FALSE 3622875 9.2.0.6, 10.1.0.4, 10.2.0.1 False ORA-1499 from ANALYZE TABLE VALIDATE STRUCTURE CASCADE ONLINE BITMAP CORR/FALSE ONLINE 3440689 9.2.0.7, 10.1.0.5, 10.2.0.1 Block corruption possible inserting large row piece to empty block CORR/PHY 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) CORR/LOG ERROR FUNCINDEX 3069818 9.2.0.6, 10.1.0.4, 10.2.0.1 Corruption possible modifying a migrated or chained row CORR/PHY 2093670 9.2.0.5, 10.1.0.2 OERI:12700 / BITMAP index corruption possible BITMAP CORR/IND 1912952 9.2.0.1 ANALYZE TABLE .. VALIDATE STRUCTURE .. ONLINE may report false ORA-01499 CORR/FALSE ERROR ONLINE 1312425 8.1.7.0 REVERSE KEY Global partitioned indexes are created as CORRUPT CORR/IND ERROR PART 1228658 8.1.6.2, 8.1.7.0 Create INDEX on SNAPSHOT/MV can produce corrupt index (OERI:13004 / OERI:25012 / ORA-1499) CORR/IND ERROR MVIEW OERI REGRESSION 533186 7.3.4.5, 8.0.4.0 ORA-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/,如需转载,请注明出处,否则将追究法律责任。
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%if(items[i].items.total > 5) { %>
<%}%> <%}%>
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
最新文章
- ASM Di Show PROVISIONED On One RAC,UNKNOWN Other Node,Cannot Add To DG_1468642.1
- Execution of DBMS_METADAT.GET_DDL results in ORA-19206,ORA-6512_292266.1
- Calling Dbms_metadata.Get_ddl From Stored Procedure Results Ora-31603_463483.1
- Linux: How To Setup UDEV Rules For RAC OCR Voting Dev On SLES10,RHEL5_414897.1
- Config DS devices for use Oracle ASM 11.2/12.1 IBM:Linux on System z_1377392.1
- Setting up ASM on linux with LVM (Doc ID 292348.1)
- Configuring device-mapper for CRS/ASM (Doc ID 357472.1)
- Manage ASM instance-creating diskgroup,adding/dropping/resizing disks_270066.1
- Oracle Linux and External Storage Systems (Doc ID 753050.1)
- Config and Use of Device Mapper Multipathing on Oracle Linux (OL)_555603.1
转载于:http://blog.itpub.net/17252115/viewspace-753027/