Elaborate on ORA-08104 errors

Although online index reconstruction (alter index index_name rebuild online) has extended the index reconstruction time, it has also given us the ability to recreate indexes online to improve data availability. If errors such as user termination or network interruptions occur during online index rebuilding, ORA-08104 errors may occur when we rebuild the index again. This is because the traces of previous operations are not cleared.

During online index reconstruction, the Oracle database modifies the data dictionary table and generates an intermediate table (IOT) to record dml operations during index reconstruction. If the reconstruction process is interrupted abnormally, the smon process will clear the reconstruction traces. However, if the system is busy, smon may be overwhelmed or too many dml operations will lead to smon being unable to obtain the lock on the relevant table, and thus the reconstruction traces cannot be cleared, when we rebuild the index again, a ora-08104 error occurs.

Below we construct a ora-08104 Error

-- Session 1
SQL> alter index ind1 rebuild online;
-- Session 2
SQL> update tab1 set rn = rn + 1;
-- Session 1 network fault, disconnection
-- Session 2
SQL> update tab1 set rn = rn + 1;

499999 rows have been updated.

SQL> COMMIT;
-- Session 3
SQL> alter index ind1 rebuild online;
Alter index ind1 rebuild online
*
Row 3 has an error:
ORA-08104: The index object 87859 is being created or rebuilt online

To solve the ora-08104 error, we need to clear the reconstruction trace, the method is summarized as follows:

 

 

• ONLINE_INDEX_CLEAN Function

 

 

This function performs a manual cleanup of failed or interrupted online index builds or rebuilds. This action is also specified med periodically by SMON, regardless of user-initiated cleanup.

This function returnsTRUEIf all indexes specified were cleaned up andFALSEIf one or more indexes cocould not be cleaned up.

Syntax

DBMS_REPAIR.ONLINE_INDEX_CLEAN (   object_id      IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,   wait_for_lock  IN BINARY_INTEGER DEFAULT LOCK_WAIT) RETURN BOOLEAN;

Parameters

Table 79-8 ONLINE_INDEX_CLEAN Function Parameters

Parameter	Description
object_id	Object id of index to be cleaned up. The default cleans up all object ids that qualify.
wait_for_lock	This parameter specifies whether to try getting DML locks on underlying table [[sub] partition] object. The default retries up to an internal retry limit, after which the lock get will give up. IfLOCK_NOWAITIs specified, then the lock get does not retry.

Declare
Isclean boolean;
Begin
Isclean: = false;
While isclean = false
Loop
Isclean: = DBMS_REPAIR.ONLINE_INDEX_CLEAN (dbms_repair.all_index_id, dbms_repair.lock_wait );
Dbms_lock.sleep (10 );
End loop;
10 end;
11/

The PL/SQL process is successfully completed.

For more details, please continue to read the highlights on the next page:

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store

1 2 Next Page