ORA-08104: this index object 2482175 is being online built or rebuilt ~ Blog for oracle RAC Goldengate Dataguard ASM database

Hello Friends,

In this post, we will see the error related to the rebuild of the index.

so let's get started.

For example in a highly active database, the probability of uncommitted transactions on a table is high. So if such a table needs to rebuild an index we will see this error

due to uncommitted transactions. In other cases, we can see a dead process with an uncommitted transaction holding a lock on the table. This also prevents us from rebuilding the index. This is

all by the design of the online rebuild functionality. However, we can't leave a database in this state forever so SMON has been designed to clean up these cases.

A process will end up dead if it has been killed from the OS with the command kill -9.

If the process was killed after executing the below command:

ALTER INDEX Index_name REBUILD ONLINE;

then the index will end up in a state where it needs clearing up.

Dropping of the index will also return the same error: ORA-8104 "This index object %s is being online built or rebuilt."

Solution :

SMON will cleanup the locked index so no actions are actually needed. However, letting SMON do the cleanup can be a bit of 'hit and miss' as SMON will try to clean up every 60 minutes

and if it cannot get a lock on the object with NOWAIT it will just try again later. In a highly active database with many transactions, this can cause the rebuild to take a long time as SMON won't get

the lock with NOWAIT. Other cases like uncommitted transactions

against the table will also result in SMON not rebuilding the index.

Till the point index is not rebuilt all access to the index will result in ORA-8104 or ORA-8106.

So to solve this situation a manual cleanup can be done using the new function

DBMS_REPAIR.ONLINE_INDEX_CLEAN()

Solution steps :


SQL> ALTER INDEX "RSPSR4"."RQSTL~0" REBUILD PARALLEL 4;
ALTER INDEX "RSPSR4"."RQSTL~0" REBUILD PARALLEL 4
*
ERROR at line 1:
ORA-08104: this index object 2482175 is being online built or rebuilt


SQL> alter system kill session '2222,20303';

System altered.

SQL> DECLARE
isClean BOOLEAN;
BEGIN
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN();
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.


SQL> ALTER INDEX "RSPSR4"."RQSTL~0" REBUILD ONLINE PARALLEL 4;

Index altered.

Hope This Help's.

Regards

Sultan Khan

ORA-08104: this index object 2482175 is being online built or rebuilt

Related Post