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

[uwe_theisinger]

Hi all,

has anyone Problems with index rebuild online with Oracle 9.2.0.8?

We use this feature since 2004 on our Production System periodically after archive Steps (weekly)

So we rebuild Indices online on Table with Long Raw, but there are no Problems in the last five years.

Nevertheless SAP Support update this Hot News with the following Statement:

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

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

During the Index Online rebuild, i'm sure, there are " heavy concurrent change activity on a table during online index rebuild".

But i can't find any references on Oracle Metalink that this problem accours also with Oracle 9.2.

thanks for answers

Uwe

[Former Member]

Hi

Are you seeing any incorrect usage of the Index after the rebuild ? and has the statistics been updated for this index ( and also the Table ) after the rebuild Online ?

Rebuild the statistics freshly using Brconnect and then check if this works. ?

If it does not , then seemingly ur Index is corrupted and you will need to build freshly again, during a less loaded time of Database Activity.

Also compare execution plans in ST04 for this index usage with your Quality Box , to determine if any discrepency.

Thanks

Rishi Das

[uwe_theisinger]

Hi Rishi,

thanks for the answer,

there is no incorrect usage of the index till this day.

But see SAP Hot News 1413928

My question was:

Has someone the same issue described in this note:

"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"

So the risk, to get this error, is to high to rebuild online during SAP User works or Jobs runs which update/insert the source table.

regards

Uwe

[Former Member]

Hello Uwe,

in order to hit the problem several conditions have to be met, e.g.:

- Implicit (not visible) "snapshot too old" problem during REBUILD ONLINE that causes the rebuild to restart its consistent read at the point where the snapshot problem happened

- ITL locks due to massive concurrent I/O, so that the restart described above is "confused" and skips some blocks

The risk of running into this problem is not too high, but it is high enough that meanwhile some SAP customers faced this problem.

Kind regards

Martin

[uwe_theisinger]

Hello Martin,

sorry for the late response...

thanks for the deatailed answer.

So i hope that "these conditions never met" together )

thanks

Uwe