Note 1413928 - Index corruption/wrong results after rebuild index ONLINE

Summary

Symptom

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
This note is released as a Hot News.
You must check the note regularly for updates. Otherwise, you will not be aware of important changes regarding prerequisites, consequences and solutions in the latest release. If this note is updated, no new Hot News is issued.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Change log:

• 17.06.2010: New Version of Checkscript attached
  New version contains 2 scripts,

• check_index_all.sql - for run the check over all tables/indexes

• check_index_one.sql - for run thec check for only 1 table

• 11.12.2009: Checkscript attached

• 04.12.2009: Restriction "Validity for Oracle 10 only" removed. The problem can occur on all Oracle releases with rebuild online

• 03.12.2009: Note released as Hot News

If there is a heavy concurrent change activity on a table during online index rebuild, the index CAN get corrupt missing keys.

Consequences:

• a select via a problematic index does not terminate with an error but potentially returns to few rows

• attempts to delete or update table rows that are not present in the index anymore lead to errors

• if a unique index is affected, duplicate keys in the table are possible if a row with a key is inserted, that is already in the table but was lost in the index. Recreation of the index will lead to ORA-01452 "cannot CREATE UNIQUE INDEX; duplicate keys found"

Other terms

Wrong Results, Corruption (Index), ORA-08102 / ORA-01499,
ORA-00600 [kdsgrp1], ORA-00600 [qertbFetchByRowID], ora-01452

Reason and Prerequisites

Wrong results or 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]

This is Oracle Bug 7329252 which is fixed in Oracle release 10.2.0.5.

Solution

Install the relevant patch for your platform.
You can find this on SAP Service Marketplace in the software center under: http://service.sap.com/swcenter-3pmain in the following directories:

Oracle 10.2.0.4

Based on the announced Patch release strategy (see SAP note 1137346) all already released single patches and merge patches will be removed from the SAP Service Marketplace and substituted by the SAP Bundle Patch (SBP). You will find the currently released SBP in SAP note 1137346.

MS Windows (32-bit)
MS Windows Itanium (64-bit)
MS Windows x86-64 (64-bit)

Patch 7329252 will be included in one of the next
Windows patch collection for Oracle 10.2.0.4.


Oracle 10.2.0.2

Based on a conflict with a still recommended Mergefix (see SAP note 1020225) an new Mergefix is requested where 7329252 is included.

For information about availability, see SAP Note 1020225.


Oracle 9.2.0.8

AIX 5L with Oracle 64-bit
/Oracle/Oracle 64-Bit/Oracle 9.2.0. 64-Bit/Oracle 9.2.0.8. 64-Bit/
AIX_5L_64/p7329252_92080_AIX64-5L.zip

HP-UX PA-RISC 64-bit
/Oracle/Oracle 64-Bit/Oracle 9.2.0. 64-Bit/Oracle 9.2.0.8 64-Bit/
HP_UX_64/p7329252_92080_HP64.zip

HP-UX Itanium 64-bit
/Oracle/Oracle 64-Bit/Oracle 9.2.0. 64-Bit/Oracle 9.2.0.8 64-Bit/
HP_IA_64/p7329252_92080_HPUX-IA64.zip

LINUX 32-bit
/Oracle/Oracle 32-Bit/Oracle 9.2.0. 32-Bit/Oracle 9.2.0.8. 32-Bit/
Linux/p7329252_92080_LINUX.zip

LINUX x86-64
/Oracle/Oracle 64-Bit/Oracle 9.2.0. 64-Bit/Oracle 9.2.0.8. 64-Bit/
Linux_x86_64/p7329252_92080_Linux-x86-64.zip

SUN Solaris 64-bit
/Oracle/Oracle 64-Bit/Oracle 9.2.0. 64-Bit/Oracle 9.2.0.8 64-Bit/
SOLARIS_64/p7329252_92080_SOLARIS64.zip

Windows 32-bit / Windows 64-bit (Windows 2003)
6826661 ist ab Patch29 für Oracle 9.2.0.8 enthalten.
Für die Verfügbarkeit siehe SAPNet Hinweis 539921.



Checkscript:

Attached to this note a checkscript is provided that counts via each index and via table the number of rows in the table. In case of a difference the different number of table and index rows are returned meaning that the error described in this note was found.
Tables are processed in alphabetical order. A logfile is written. Processing time can be several days. The script is restartable. No resources are locked. To check the DB with the script:

• unzip the attachment

• place check_index_all.sql and check_index_one.sql in a directory on the DB server

• change to directory the scripts were copied to

• connect to sqlplus with / as sysdba

• enter "create directory NOTE_1413928 as '';"
  Replace with the OS directory scripts were copied to

• enter "Grant read, write on directory NOTE_1413928 to " ;

• leave sqlplus

Run script check_index_all

• call: sqlplus "/ as sysdba" @check_index_all.sql

• The script will request two input parameters:

• directory:
  enter NOTE_1413928

• maximum_parallel:
  depending on the hardware of the machine and the load; if you choose a too high or too low value you can terminate the script later and restart it with another parallel degree

Run script check_index_one.sql
This script has to be executed using the SAP schema owner!

• call: sqlplus @check_index_one.sql

• connect with SAPSR3 or whatever the schema owner is defined.

• The script will request three input parameters:

• directory:
  enter NOTE_1413928

• maximum_parallel:
  depending on the hardware of the machine and the load; if you choose a too high or too low value you can terminate the script later and restart it with another parallel degree

• table_name:
  the check will run only for that table and all its indexes

Check the logfile

• before table processing is started the tablename is written to the logfile.

• if an index-table row count mismatch is detected the count via table and index is written to the logfile. Search in the logfile for keyword 'Table' for this kind of errors.
  In case of this error the sql statement to recreate the index is written to the index_check*.sql file. Attention: Don´t drop and recreate unique indexes while the SAP System is running.

• if any ORA error comes up during processing the tables the error is written to the logfile and processing continues with the next table. Search the logfile for keyword 'ORA-' for this kind of errors. Attention: the problem this note was created for results during check usually NOT in an "ORA-" error.

Remarks:

• dbv does not detect this kind of corruption because the index blocks are valid Oracle blocks. Only a crosscheck between table and index is able to detect this kind of corruption.
  A crosscheck is done e.g. by the command "analyze table validate structure cascade online;". For details how to check the DB with this command check note 23345.

• As long as the fix is not applied it is not safe to try to get rid of the corruption by a further "rebuild online".
  Because a "rebuild offline" will read the corrupt index with missing rows it will not help, too.
  Currently it is investigated if the "create online" shows the problem, too. Until further notice we therefore strongly recommend not to create indexes with the "online" option. Only a drop/create index without the online option guarantees to get a non corrupt index, if the index to be recreated is already corrupt.
  ATTENTION: DO NOT DROP AND CREATE UNIQUE INDEXES WHILE THE SAP SYSTEM IS USED.

• Don´t do mass index rebuilds with the online option until the
  bug is fixed. Especially don´t use reports like RSORAISQ, RSORAISQN etc.

Header Data

Release Status:	Released for Customer
Released on:	17.06.2010 11:09:52
Master Language:	English
Priority:	HotNews
Category:	External error
Primary Component:	BC-DB-ORA Oracle
Secondary Components:	BW-SYS-DB-ORA BW ORACLE