Mohamed Houri’s Oracle Notes

June 14, 2013

ORA-08102: index key not found: Part II

Filed under: Index — hourim @ 6:52 pm

Last year I wrote a note about how I succeeded to overcome an index key not found error by moving the underlying table not without mentioning that an offline rebuild of the corrupted index reveals to be of no help.  Because, in contrast to an online index rebuild which is based on the table data, an offline index rebuild is based on the index data. And, as far as this data is corrupted in the index, rebuilding the same index with the same data will  produce the same error.

What prompted me to write this article is that, yesterday,  we have been confronted to the same error with the same index in the ACCEPTANCE (beta) database.

I was going to play with this case by tracing(10046 events) an offline rebuild first and then set the index in an unusable state before rebuilding  it offline when I received an e-mail from a DBA telling that he has successfully rebuilt the culprit index online. Too late.

Fortunately, yes you read it correctly, the day after, the same error occurred again but this time on another index of the same table.

The occasion was then given to me again to check the suggestion made by Jason Bucata (see comment 2) about putting the index in an unusable state and rebuild it offline. As such, i.e. when index is in an unusable state, even if this index is rebuilt offline, Oracle will use the underlying table to reconstruct the corrupted index in contrast to a “valid” index rebuilt offline where the underlying table is not used during this kind of rebuild.

And by the way, instead of rebuilding the newly corrupted index, I decide to consider all the table indexes (it is not very safe  but I could not take a risk of another day with a new different corrupted index)

select 'alter index ' ||index_name || ' unusable;' from user_indexes where table_name = 'TABLE_XXX';
select 'alter index ' ||index_name || ' rebuild;'  from user_indexes where table_name = 'TABLE_XXX';

The 10046 trace for the rebuild offline of an unusable index identified by its object_id (obj#=245082) belonging to a table identified by its object_id ( obj#=244832) when tkprofed show this:

alter index XXX_IND_NI rebuild

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       75      0.32       0.37          0         96          0           0
Execute      1    182.96     174.86     492687     368598      75682           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       76    183.28     175.23     492687     368694      75682           0

Misses in library cache during parse: 75
Optimizer mode: ALL_ROWS
Parsing user id: 47

Elapsed times include waiting on following events:

Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
SQL*Net break/reset to client                 150        0.00          0.17
SQL*Net message to client                      76        0.00          0.00
SQL*Net message from client                    76      146.07        169.65
db file scattered read                       6588        0.61          7.27
db file sequential read                       821        0.03          0.08
direct path write temp                        320        0.29          5.81
direct path read temp                        4498        0.06          1.17
log file sync                                   2        0.02          0.02
log file switch completion                      8        0.31          0.73
direct path write                               6        0.00          0.00
reliable message                                1        0.00          0.00
enq: RO - fast object reuse                     1        0.00          0.00
rdbms ipc reply                                 1        0.01          0.01
********************************************************************************

The presence of db file scattered read wait event is a clear indication of a full segment read; and their high number (6588) compared to the db file sequential read (821) suggests that this offline rebuild (of an unusable index) has been done using the underlying table.

Note, by the way, the unusual high number (47) of Misses in Library cache during parse and a cpu time (183 seconds) greater than the elapsed time (175 seconds)

The trace file shows also the following interesting information:

 =====================

PARSING IN CURSOR #2 len=43 dep=0 uid=47 oct=9 lid=47 tim=22486851823983 hv=2687996766 ad='920eda48'
alter index XXX_IND_NI rebuild
END OF STMT

PARSE #2:c=10000,e=6576,p=0,cr=9,cu=0,mis=1,r=0,dep=0,og=1,tim=22486851823977
BINDS #2:
WAIT #2: nam='db file scattered read' ela= 2526 file#=37 block#=21 blocks=58 obj#=244832 tim=22486851828916
WAIT #2: nam='db file scattered read' ela= 4839 file#=37 block#=79 blocks=58 obj#=244832 tim=22486851838736
WAIT #2: nam='db file scattered read' ela= 3139 file#=37 block#=137 blocks=58 obj#=244832 tim=22486851847085
WAIT #2: nam='db file scattered read' ela= 2603 file#=37 block#=195 blocks=58 obj#=244832 tim=22486851853977
WAIT #2: nam='db file scattered read' ela= 5218 file#=37 block#=253 blocks=58 obj#=244832 tim=22486851863154
WAIT #2: nam='db file scattered read' ela= 2313 file#=37 block#=311 blocks=58 obj#=244832 tim=22486851868008
WAIT #2: nam='db file scattered read' ela= 2611 file#=37 block#=369 blocks=58 obj#=244832 tim=22486851875983
WAIT #2: nam='db file scattered read' ela= 3098 file#=37 block#=427 blocks=58 obj#=244832 tim=22486851882593
WAIT #2: nam='db file scattered read' ela= 3194 file#=37 block#=485 blocks=58 obj#=244832 tim=22486851892313
WAIT #2: nam='db file scattered read' ela= 2763 file#=37 block#=543 blocks=58 obj#=244832 tim=22486851901798
WAIT #2: nam='db file scattered read' ela= 3374 file#=37 block#=601 blocks=48 obj#=244832 tim=22486851912129
WAIT #2: nam='db file scattered read' ela= 3214 file#=37 block#=1299 blocks=58 obj#=244832 tim=22486851918241
WAIT #2: nam='db file scattered read' ela= 3015 file#=37 block#=1357 blocks=58 obj#=244832 tim=22486851927379
WAIT #2: nam='db file scattered read' ela= 2787 file#=37 block#=1415 blocks=58 obj#=244832 tim=22486851936055
………..

WAIT #2: nam='db file sequential read' ela= 79 file#=45 block#=3152 blocks=1 obj#=244832 tim=22486853368773
WAIT #2: nam='db file scattered read' ela= 365 file#=45 block#=3154 blocks=55 obj#=244832 tim=22486853369742
WAIT #2: nam='db file scattered read' ela= 464 file#=45 block#=3852 blocks=58 obj#=244832 tim=22486853373874
WAIT #2: nam='db file scattered read' ela= 680 file#=45 block#=3910 blocks=58 obj#=244832 tim=22486853378189
WAIT #2: nam='db file scattered read' ela= 1957 file#=45 block#=3968 blocks=58 obj#=244832 tim=22486853385193
WAIT #2: nam='db file scattered read' ela= 846 file#=45 block#=4026 blocks=58 obj#=244832 tim=22486853390273
WAIT #2: nam='db file scattered read' ela= 456 file#=45 block#=4084 blocks=58 obj#=244832 tim=22486853395209
WAIT #2: nam='db file scattered read' ela= 455 file#=45 block#=4142 blocks=58 obj#=244832 tim=22486853399969
etc….

A very high number of db file scattered read on obj#=244832 which represents the object id of the table to which is attached to corrupted index.

Finally, the bottom line from this blog article is to show that when an index is corrupted (ora-08102) it is possible to rebuild it offline but you should first set it into an unusable state.

Advertisements

2 Comments »

  1. Hi ,
    A fast action against such an error is to drop and create index ….

    Comment by Kais — June 14, 2013 @ 9:31 pm | Reply

  2. Kais,

    Thanks for your comment.

    Yes, it’s correct but it is always interesting to know what is happening behind the scene

    Best regards
    Mohamed

    Comment by hourim — June 15, 2013 @ 8:25 am | 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

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: