ANBOB™

Last night ,The Developers ask me they have creating an index do not use parallel,Unacceptably slow. then kill the session to cancel, but now to face ORA-08104 during trying to online index rebuild . that is a busy product database(7×24). DB version 10205 2nodes rac on hpux.

SQL> ALTER INDEX ANBOB.BB_BUS_INFO_IR REBUILD online parallel 32;
ALTER INDEX CRM_OWNER_USER.BB_BUS_INFO_IR REBUILD online parallel 32
*
ORA-08104: this index object 3878859 is being online built or rebuilt

SQL> @oid 3878858

owner                     object_name                    object_type       
------------------------- ------------------------------ ------------------
ANBOB                     BB_BUS_INFO_IR                 INDEX             

# oerr ora 8104
08104, 00000, "this index object %s is being online built or rebuilt"
// *Cause:  the index is being created or rebuild or waited for recovering 
//          from the online (re)build 
// *Action: wait the online index build or recovery to complete

SQL> select STATUS from dba_indexes where index_name='BB_BUS_INFO_IR';

 STATUS
 --------
 UNUSABLE
 
SQL> select obj#,flags from ind$ where obj#=3878858;
      OBJ#      FLAGS
---------- ----------
   3878858        517

SQL> select to_char(517,'xxxxxxx') from dual;
TO_CHAR(
--------
     205

ind$.flags         number not null,
                /* mutable flags: anything permanent should go into property */
                                                    /* unusable (dls) : 0x01 */
                                                    /* analyzed       : 0x02 */
                                                    /* no logging     : 0x04 */
                                    /* index is currently being built : 0x08 */
                                     /* index creation was incomplete : 0x10 */
                                           /* key compression enabled : 0x20 */
                                              /* user-specified stats : 0x40 */
                                            /* secondary index on IOT : 0x80 */
                                      /* index is being online built : 0x100 */
                                    /* index is being online rebuilt : 0x200 */
                                                /* index is disabled : 0x400 */
                                                     /* global stats : 0x800 */
                                            /* fake index(internal) : 0x1000 */
                                       /* index on UROWID column(s) : 0x2000 */
                                            /* index with large key : 0x4000 */
                             /* move partitioned rows in base table : 0x8000 */
                                 /* index usage monitoring enabled : 0x10000 */
                      /* 4 bits reserved for bitmap index version : 0x1E0000 */

Tip:
Online index rebuilds which fail (for any reason) leave the dictionary marked that the rebuild was in progress and SMON should
clean up the dictionary (kdicclean). This cleanup function is only executed every hour by SMON so you have to wait for SMON to clean IND$.

Of course, we can also manually clean up, To resolve this issue you should refer to the following method run the rebuild using DBMS_REPAIR.ONLINE_INDEX_CLEAN function:

SQL> DECLARE
     isClean BOOLEAN;
  BEGIN
       isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(3878859, DBMS_REPAIR.LOCK_WAIT);
  EXCEPTION
  WHEN OTHERS THEN
  RAISE;
  END;
  /

PL/SQL 过程已成功完成。

if error ora-24120 , try following SQL

 
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;
end;
/

Note:
If rebuilding the index without online option, Oracle will grab the index in X-mode and rebuild a new index segment by selecting the data from the old index. So here we are
– not allowing any DML on the table hence there is no journal table involved
– and it is doing an index scan
So if you do lots of DML on the same table,while rebuilding index online,it should take longer time.

打赏

微信扫一扫，打赏作者吧～