Database error 8102 during client copy
Hi All,
I am from Basis team and this is my first blog to SCN. 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.
Thats it the issue is solved 🙂 . Hope this post will help you.
Thanks Pradeep !
hi Pradeep,
doc is really gud but easy for only some missing Indexes,
in my case, I am getting 200+ missing indexes during SCCL. how can i build these missing indexes mean I have to alter one-by-one or I can rebuild to all missing indexes in once.
Regards,
Hi Jamil,
When we usually have more indexes to be built we can run the index building as a script.
So that we need not run the commands manually.
Regards,
Pradeep
Dear Pradeep,
would you like to guide us how do i write required script?Please send me template for this script.
regards,
Dear Pradeep,
would you like to guide us how do i write required script?Please send me template for this script.
regards,
Hi Jamil,
If you have the list of indexes which needs to be rebuild... Just login to SAP .. goto SE38 .. report RSANAORA .. there you got multiple selection and you can rebuild the index online for any number of indexes.. run the report in background..
You also find the solution on Note 365481 - Block corruptions
Procedure to rebuild the index can be found in Note 682926 - Composite SAP note: Problems with "create/rebuild index"
Regards, Juan
Dears
Looks like Juan beat me to it but this blog post isn't exactly what I would call a good example of what to do in case you run into an Oracle error during a client copy.
A couple of reasons for that:
1) You don't need an Oracle expert to rebuild an index. Rebuilding an index is basic knowledge that a SAP system administrator should have. There are several SAP courses for Oracle database administration for SAP. ADM505, ADM510.
There is also a very good SAPPress book available: SAP Database Administration with Oracle which can serve as learning and/or reference material.
2) You could easily have looked up the Oracle error on SAP Service Marketplace. Using the keyword ORA-08102 is sufficient to narrow down the search result to only retrieve 6 SAP notes. You should read those SAP notes to understand the situation and to check if rebuilding the index is sufficient.
In that regard please read SAP Note 1413928 - Index corruption/wrong results after rebuild index ONLINE
You might even have index corruption / wrong results after rebuilding those indexes online.
Best regards
Tom