Mohamed Houri’s Oracle Notes

February 6, 2012

How to solve ORA-08102: index key not found

Filed under: Index — hourim @ 2:28 pm

In one of our test databases we encountered the following error during an overnight PL/SQL purge job:

ORA-08102: index key not found, obj# 124885, file 48, block 55492 (2) 01-FEB-12

The purpose of the job is to delete data introduced since more than 6 days respecting for that several hierarchical parent-child relationships.  It has been working without any relatives problems since more than 2 years until it started crashing out with the above ORA-08102 error. I aim via this blog article to show how I have resolved this problem.

The first thing I did is of course to identify the corrupted object via the following select:

mhouri.world> ed
    Wrote file afiedt.buf
  1  select substr(object_name,1,30), object_type
  2  from user_objects
  3* where object_id = 154711
;

 SUBSTR(OBJECT_NAME,1,30)       OBJECT_TYPE
------------------------------ ----------------
XXX_ZZZ_UK                        INDEX

The naming standard (_UK) is as such that I immediately identified this index as an automatic index created to enforce a unique constraint. I thought first that I need to rebuild this unique index:


mhouri.world> alter index XXX_ZZZ_UK rebuild;

Index altered.

Unfortunately, when I launched again the purge batch the same error has been raised again. So my second tentative was to drop and recreate the unique constraint and implicitly the unique index.


mhouri.world> alter table t1 drop constraint XXX_ZZZ_UK;

Table altered.

mhouri.world > alter table t1 add constraint XXX_ZZZ_UK unique (id1, id2, dat1);

Table altered.

Of course that you have first to get the DDL definition of the constraint before dropping it so that you will be able to re-created it again.

When the purge process has been re-started following this last intervention, it, unfortunately, raised again the same ORA-08102 on the same object. Finally I have decided to work on the underneath table instead of its unique constraint and unique index.

mhouri.world>> alter table t1 move;

 Command that has invalidated all attached indexes and which obliged me to re-build them:


mhouri.world > select 'alter index ' || index_name || ' rebuild;'
from user_indexes
where status != 'VALID';

Once all indexes rebuilt, I launched again the purge job which surprisingly completed successfully.

Frankly speaking I don’t know if I would have been able to work around this error if I have tried instead of the move command a rebuild or shrink command. But what I have learnt from this experience is that when an index is corrupted it could be repaired by moving table data instead of dropping and recreating the corrupted index as initially indicated by the ORA-08102 error. I have also to admit that I don’t know yet the side effect of the move command and will certainly write something about that if eventually I will come to learn something about that.
.

12 Comments »

  1. Thanks for this topic!
    Once we read this clear and fully documented article, the problem seems so easy to solve .. but God knows how many times we can spend on such a problem.

    Comment by Mourad — February 8, 2012 @ 6:53 am | Reply

  2. I’ve discovered your blog and I’ve been browsing through your old postings. Good stuff…

    The fundamental question is, why didn’t the first index rebuild fix the corruption? I believe the answer is that an ordinary rebuild recreates the new index segment based on the data existing in the old index segment. So when you did the rebuild, you also copied the corruption along with it.

    If you had completely dropped the index and then recreated it from DDL, it would have avoided the error, and without having to move the table also.

    Another way to do it is to do the rebuild ONLINE, which goes directly to the underlying table for its data. (I don’t know why; you’d think Oracle could do an online rebuild from the old index, but that’s not how it’s coded.)

    What the move did was signal to Oracle that the old index segment wasn’t usable anymore, since the rowids had all changed, so the subsequent rebuild read from the table.

    Another possibility, that I’ve never tried, is to mark the index UNUSABLE first, then rebuild. Marking it UNUSABLE might have had the same effect as the move, where the index is concerned. (As a guess, if Oracle were clever about tracking SCNs, it might still see that the index data is valid because nothing has changed in the table since then, so you might have to make a dummy update to the table to keep it from reusing the old index segment. That’s just speculation, though.)

    Comment by Jason Bucata — September 16, 2012 @ 3:53 pm | Reply

  3. Hi Jason,
    What a wonderful suggestions you have made here. You gave me several points to work on in order to figure out what Oracle is doing behind the scene in such kind of situations. Thanks a lot for that.

    Writing this article was 100% motivated by the idea for me to easily find a pointer to a simple test case of corrupted indexes and how I managed to solve it, if one day I will have to tackle similar issues.

    I have never thought that new investigations ideas (like yours) will come out thanks to this published article. This is a perfect proof of the mantra I took from Tom Kyte: “If you want to progress then write and let others read you”
    Best regards

    Comment by Houri — September 17, 2012 @ 6:52 am | Reply

  4. Hi,

    I just solve this problem today by executing this :
    DELETE FROM xxxxx WHERE condition;

    ORA-08102: index key not found, obj# 592275, file 360, block 243815 (2)

    select ‘ALTER INDEX ‘||INDEX_NAME||’ rebuild online tablespace ‘||tablespace_name||’;’ from user_indexes where TABLE_NAME = ‘xxxxx’;

    May this help you
    Best Regards

    Comment by macalga — September 20, 2012 @ 2:11 pm | Reply

  5. Macalga,

    Yes, thanks. Of course it helps. You know why? simply because it confirms what Jason stated above in his comment when he wrote “Another way to do it is to do the rebuild ONLINE”

    Comment by hourim — September 20, 2012 @ 2:14 pm | Reply

  6. We keep getting this error. And yes, recreating the index fixes it… for awhile. How do we fix the root cause? We’re getting kind of tired of recreating indexes.🙂

    Comment by Bob Wagner — February 21, 2013 @ 12:54 am | Reply

  7. Bob,

    You see that when I have been confronted to this issue a drop/recreate of the index has not been very helpful. However, when I altered the table to move its data around this was instead a drastic medicine for the corrupted index. Jason Bucata (see comment 2) suggested an ONLINE rebuild which has been confirmed by macalga (see comment 4). So you can try one of those two methods (alter table move or rebuild index online)
    Best regards

    Comment by hourim — February 24, 2013 @ 8:01 am | Reply

    • Thank you. I guess I overlooked the point that the move of the data might be the magic bullet that permanently fixes this. So we will try that. Thanks again!
      -Bob

      Comment by Bob Wagner — February 25, 2013 @ 5:58 pm | Reply

  8. […] year I wrote a note about how I succeeded to overcome an index key not found error by moving the underlying table not […]

    Pingback by ORA-08102: index key not found: Part II | Mohamed Houri’s Oracle Notes — June 14, 2013 @ 6:52 pm | Reply

  9. Nice one Mohamed; thanks. I am doing the rebuild online this time. Can you tell me; if I move the table, will this cause all objects dependant to become invalid like if I delete and recreate index?

    Comment by Uthra Venkatraman — February 16, 2015 @ 6:49 pm | Reply

    • Uthra

      According to what I have encoutered at that time then yes if you move table attached indexes will be invalidated. Though that since then I haven’t tested it again. You can give it a try and confirm here in this blog article that it really invalidates the indexes

      Best regards

      Comment by hourim — February 21, 2015 @ 7:33 am | Reply

  10. Thanks for this post! Was very informative for this error.

    Comment by Paul Schroeder — October 23, 2015 @ 4:47 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: