ORA-08104 重现

1、查询测试表的当前数据量和索引状态
SQL> select count(*) from ttraycurr;
 
  COUNT(*)
----------
    856598

SQL> select index_name,index_type,status from user_indexes where table_name='TTRAYCURR'

index_name        index_type    status
----------------  -----------  -----------
IX_TTRAYCURR_05    NORMAL      N/A
IX_TTRAYCURR_06    NORMAL      N/A
IX_TTRAYCURR_01    NORMAL      N/A
IX_TTRAYCURR_02    NORMAL      N/A
IX_TTRAYCURR_04    NORMAL      N/A
IX_TTRAYCURR_03    NORMAL      VALID   

2、模拟现场大量的插入操作
SQL> insert into ttraycurr select * from ttraycurr;

3、同时删除分区致使全局索引失效
SQL> alter table ttraycurr drop partition PTTTRAYCURR_200909;
                                                            
Table altered  

SQL> select index_name,index_type,status from user_indexes where table_name='TTRAYCURR'

index_name        index_type    status
----------------  -----------  -----------
IX_TTRAYCURR_05    NORMAL      N/A
IX_TTRAYCURR_06    NORMAL      N/A
IX_TTRAYCURR_01    NORMAL      N/A
IX_TTRAYCURR_02    NORMAL      N/A
IX_TTRAYCURR_04    NORMAL      N/A
IX_TTRAYCURR_03    NORMAL      UNUSABLE

4、执行重建索引，2分钟后KILL SESSION
SQL> alter index IX_TTRAYCURR_03 rebuild online;
 
alter index IX_TTRAYCURR_03 rebuild online

ORA-01013: user requested cancel of current operation

SQL> alter index IX_TTRAYCURR_03 rebuild online;
 
alter index IX_TTRAYCURR_03 rebuild online
 
ORA-08104: this index object 64093 is being online built or rebuilt

5、会话被异常终止后由于SMON无法立即清理相应的临时段和标志位，所以oracle认为重建还在进行，此时如果drop index将会得到资源忙的提示
SQL> drop index IX_TTRAYCURR_03;
 
drop index IX_TTRAYCURR_03
 
ORA-00054: resource busy and acquire with NOWAIT specified
 
6、查询系统表ind$中对应的FLAGS，然后执行DBMS_REPAIR.ONLINE_INDEX_CLEAN进行手工清理，此时insert仍然在进行，查询v$session_wait发现有index (re)build online cleanup等待事件。
SQL> select obj#,flags from sys.ind$ where obj#=64093;
 
      OBJ#      FLAGS
---------- ----------
     64093        519
 
SQL> DECLARE
  2  RetVal BOOLEAN;
  3  OBJECT_ID BINARY_INTEGER;
  4  WAIT_FOR_LOCK BINARY_INTEGER;
  5  BEGIN
  6  OBJECT_ID := 64093;
  7  WAIT_FOR_LOCK := NULL;
  8  RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
  9  COMMIT;
 10  END;
 11  /
 

SQL> select sid,event from v$session_wait;

       SID EVENT
---------- ----------------------------------------------------------------
       139 SQL*Net message from client
       140 SQL*Net message from client
       141 index (re)build online cleanup
       142 SQL*Net message from client
       143 SQL*Net message from client
       144 db file sequential read
       146 SQL*Net message from client
       152 SQL*Net message to client
       155 SQL*Net message from client
       156 rdbms ipc message
       157 rdbms ipc message
       159 rdbms ipc message
       161 rdbms ipc message
       162 rdbms ipc message
       163 rdbms ipc message
       164 smon timer
       165 rdbms ipc message
       166 rdbms ipc message
       167 rdbms ipc message
       168 rdbms ipc message
       169 rdbms ipc message
       170 pmon timer

22 rows selected.

7、DBMS_REPAIR.ONLINE_INDEX_CLEAN执行成功后再查询ind$中的状态，正常情况下FLAGS应该会减去512变成7，此时则可以正常执行rebuild online，但是由于ttraycurr仍然有大量的未提交的DML操作，
所以在DBMS_REPAIR.ONLINE_INDEX_CLEAN执行成功的情况下，OBJECT 64903的FLAGS仍然没有发生改变,也就是说在有大量的改变数据的DML操作时DBMS_REPAIR.ONLINE_INDEX_CLEAN操作无效。
SQL> select obj#,flags from sys.ind$ where obj#=64093;
 
      OBJ#      FLAGS
---------- ----------
     64093        519

8、根据网上资料，只能手动更新ind$表的FLAG字段值
SQL>  update ind$ set flags=flags-512 where obj#=64093;
                                                      
1 row updated.                                        
                                                      
SQL> commit;                                          
                                                      
Commit complete.                                      
                                                      
SQL> select obj#,flags from sys.ind$ where obj#=64093;
                                                      
      OBJ#      FLAGS                                 
---------- ----------                                 
     64093          7          
    
9、删除临时表
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
SYS_JOURNAL_64093
TTRAYDELTAOCV
TTRAYCURR

SQL> drop table SYS_JOURNAL_64093;

Table dropped.

SQL> alter index IX_TTRAYCURR_03 rebuild online;
此时重建不再报错,待DML提交后索引重建成功。而此时再查询ind$表可以发现object 64093的FLAGS为519。

疑问：为什么用DBMS_REPAIR.ONLINE_INDEX_CLEAN清理FLAGS仍然不变，metalink上的解决办法也是如此啊，难道是由于有大量未提交的DML操作导致？ 

METALINK
Session Was Killed During The Rebuild Of Index ORA-08104 [ID 375856.1]

--------------------------------------------------------------------------------
 
  修改时间 29-APR-2010     类型 PROBLEM     状态 MODERATED  

In this Document
  Symptoms
  Cause
  Solution
  References

 

--------------------------------------------------------------------------------

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

 

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.6 to 11.1.0.6 - Release: 9.2 to 11.1
Information in this document applies to any platform.

Symptoms
While running an online index rebuild your session was killed or otherwise terminated abnormally.  You are now attempting to run the index rebuild again and is throwing the error:

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

 

Cause
A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not.

Solution
The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. Use the dbms_repair.online_index_clean function to resolve the issue.

* Please note if you are unable to run the dbms_repair.online_index_clean function it is due to the fact that you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix.  The fix for this bug is a new function in the dbms_repair package called dbms_repair.online_index_clean, which has been created to cleanup online index [[sub]partition] [re]builds. New functionality is not allowed in patchsets; therefore, this is not available in a patchset but is available in 10gR2.

- Check your patch list to verify the database is patched for Bug 3805539 using the following command and patch for the bug if it is not listed:

关于ind$的falgs字段解释如下，16进制值
/* 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 */

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/702413/viewspace-681495/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/702413/viewspace-681495/