了解你所不知道的SMON功能(七):清理IND$字典基表-白红宇

SMON的作用还包括清理IND$字典基表(cleanup ind$):

触发场景

当我们在线创建或重建索引时(create or rebuild index online)，服务进程会到IND$字典基表中将该索引对应的记录的FLAGS字段修改为十进制的256或者512(见上图0x100=256,0x200=512)，如:

SQL> create index macleans_index on larges(owner,object_name) online;SQL> select obj# from obj$ where name='MACLEANS_INDEX';      OBJ#----------   1343842SQL> select FLAGS from ind$ where obj#=1343842;     FLAGS----------       256ind_online$字典基表记录了索引在线创建/重建的历史SQL> select * from ind_online$;      OBJ#      TYPE#      FLAGS---------- ---------- ----------   1343839          1        256   1343842          1        256create table ind_online$( obj#          number not null,  type#         number not null,              /* what kind of index is this? */                                                               /* normal : 1 */                                                               /* bitmap : 2 */                                                              /* cluster : 3 */                                                            /* iot - top : 4 */                                                         /* iot - nested : 5 */                                                            /* secondary : 6 */                                                                 /* ansi : 7 */                                                                  /* lob : 8 */                                             /* cooperative index method : 9 */  flags         number not null                                      /* index is being online built : 0x100 */                                    /* index is being online rebuilt : 0x200 */)

原则上online create/rebuild index的的清理工作由实际操作的服务进程负责完成，这种清理在DDL语句成功的情况下包括一系列数据字典的维护，在该DDL语句失败的情形中包括

和数据字典的维护，无论如何都需要drop在线日志中间表

SYS_JOURNAL_nnnnn(nnnn为该索引的obj#)

。数据字典的维护工作就包含对IND$基表中相应索引记录的FLAGS标志位的恢复，但是如果服务进程在语句执行过程中意外终止的话，那么短时间内FLAGS标志位字段就无法得到恢复，这将导致对该索引的后续操作因ORA-8104错误而无法继续:

SQL> drop index macleans_index;drop index macleans_index           *ERROR at line 1:ORA-08104: this index object 1343842 is being online built or rebuilt08104, 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

SMON负责在启动后(startup)的每小时执行一次对IND$基表中因在线创建/重建索引失败所留下记录的清理，这种清理工作由kdicclean函数驱动(kdicclean is run by smon every 1 hour，called from SMON to find if there is any online builder death and cleanup our ind$ and obj$ and drop the journal table, stop journaling)。这种清理工作典型的调用堆栈stack call如下:

ksbrdp -> ktmSmonMain  ktmmon -> kdicclean -> kdic_cleanup -> ktssdrp_segment

注意因为SMON进程的清理工作每小时才执行一次，而且在工作负载很高的情况下可能实际很久都不会得到清理，在这种情景中我们总是希望能尽快完成对索引的在线创建或重建，在10gr2以后的版本中我们可以直接使用dbms_repair.online_index_clean来手动清理online index rebuild的遗留问题:

SQL> drop index macleans_index;drop index macleans_index           *ERROR at line 1:ORA-08104: this index object 1343842 is being online built or rebuiltDECLARE 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;/SQL>  drop index macleans_index; drop index macleans_index            *ERROR at line 1:ORA-01418: specified index does not exist成功清理

但是如果在9i中的话就比较麻烦，可以尝试用以下方法(不是很推荐，除非你已经等了很久):

1.首先手工删除在线日志表，通过以下手段找出这个中间表的名字select object_name  from dba_objects where object_name like       (select '%' || object_id || '%'          from dba_objects         where object_name = '&INDEX_NAME')/Enter value for index_name: MACLEANS_INDEXold   6:          where object_name = '&INDEX_NAME')new   6:          where object_name = 'MACLEANS_INDEX')OBJECT_NAME--------------------------------------------------------------------------------SYS_JOURNAL_1343845SQL> drop table SYS_JOURNAL_1343845;Table dropped.2.第二步要手动修改IND$字典基表!!!!!! 注意！手动修改数据字典要足够小心！！select flags from ind$ where obj#=&INDEX_OBJECT_ID;Enter value for index_object_id: 1343845old   1: select flags from ind$ where obj#=&INDEX_OBJECT_IDnew   1: select flags from ind$ where obj#=1343845     FLAGS----------       256a) 针对online create index，手动删除对应的记录delete from IND$ where obj#=&INDEX_OBJECT_IDb) 针对online rebuild index，手动恢复对应记录的FLAGS标志位update IND$ set FLAGS=FLAGS-512 where obj#=&INDEX_OBJECT_ID

接下来我们实际观察一下清理工作的细节:

SQL> select obj# from obj$ where name='MACLEANS_INDEX';      OBJ#----------   1343854SQL> select FLAGS from ind$ where obj#=1343854;     FLAGS----------       256SQL> oradebug setmypid;Statement processed.SQL> oradebug event 10046 trace name context forever,level 8;Statement processed.SQL> DECLARE  2   isClean BOOLEAN;  3  BEGIN  4    isClean := FALSE;  5    WHILE isClean=FALSE  6    LOOP  7      isClean := dbms_repair.online_index_clean(  8      dbms_repair.all_index_id, dbms_repair.lock_wait);  9 10      dbms_lock.sleep(10); 11    END LOOP; 12  END; 13  /PL/SQL procedure successfully completed.===============================10046 trace=============================select i.obj#, i.flags, u.name, o.name, o.type#  from sys.obj$ o, sys.user$ u, sys.ind_online$ i where (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512)   and (not ((i.type# = 9) and bitand(i.flags, 8) = 8))   and o.obj# = i.obj#   and o.owner# = u.user#select u.name,       o.name,       o.namespace,       o.type#,       decode(bitand(i.property, 1024), 0, 0, 1)  from ind$ i, obj$ o, user$ u where i.obj# = :1   and o.obj# = i.bo#   and o.owner# = u.user#delete from object_usage where obj# in (select a.obj#                  from object_usage a, ind$ b                 where a.obj# = b.obj#                   and b.bo# = :1)drop table "SYS"."SYS_JOURNAL_1343854" purgedelete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)delete from ind$ where bo#=:1delete from ind$ where obj#=:1

我们可以利用以下语句找出系统中可能需要恢复的IND$记录，注意不要看到查询有结果就认为这是操作失败的征兆，很可能是有人在线创建或重建索引:

select i.obj#, i.flags, u.name, o.name, o.type#  from sys.obj$ o, sys.user$ u, sys.ind_online$ i where (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512)   and (not ((i.type# = 9) and bitand(i.flags, 8) = 8))   and o.obj# = i.obj#   and o.owner# = u.user#/

相关诊断事件可以通过设置诊断事件event=’8105 trace name context forever' 来禁止SMON清理IND$(Oracle event to turn off smon cleanup for online index build)

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277842