Solution for Database error ORA-08102 found in SM21

[Former Member]

Hi All,

This document discusses the solution for an error I have encountered while performg a client copy on Oracle database(10.2.0.4.0).

I want to share my experience while performing a local client copy.

I was doing a client copy on Unix- Oracle environment and we ran into error "Database error 8102".  The exact error log from SM21 is below,

Error: (from sm21)

Database error 8102 at DEL access to table ACCTHD

> ORA-08102: index key not found, obj# 31189, file 34, block

> 1200035 (2)

The error clearly shows that index key was not found for an object in the table. Index for the tables mentioned in the error (ACCTHD) in the above case were corrupted. Hence DB action DEL(delete) is failing on these tables. I have checked several forums with the error code ORA-8102 and in most of them suggested to rebuild the index for all tables.

Luckily we have an experienced Oracle DBA in our project and with his help we were able to solve this by rebuilding Index for the table corrupted.

Below are steps to rebuild the Index,

Step  #1 : Logon to your server as ORA<SID>

Step  #2 : Open sqlplus as sysdba

sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn /as sysdba

              Connected.

Step #3 : Run the below query on the set of tables to get list if index to be rebuild for the given table

                select index_name, owner from dba_indexes where table_name='<table_name_uppercase>';

Step #4 : From the list of Index displayed from the above query run the below command to generate the index and wait till you receive the confirmation “Index altered.”

SQL> alter index SAPSR3."ACCTHD~0" rebuild online parallel 2;

Index altered.

SQL> alter index SAPSR3."ACCTHD~1" rebuild online parallel 2;

Index altered.

This solves the issue .

Alternatively we can run the report "RSANAORA" to rebuild the index online/offline. This in turn runs same query in SQL level.

One more best practice is to run the "Update Stats" on the table and index( which were rebuild) so that performance will not be effected due to the rebuild. Hope this page will help to solve your issue when you run into a similar kind of error.