SMON的作用还包括清理IND$字典基表(cleanup ind$):
触发场景
当我们在线创建或重建索引时(create or rebuild index online)，服务进程会到IND$字典基表中将该索引对应的记录的FLAGS字段修改为十进制的256或者512(见上图0x100=256,0×200=512)，如:
SQL> create index macleans_index on larges(owner,object_name) online;
SQL> select obj# from obj$ where name='MACLEANS_INDEX';
OBJ#
----------
1343842
SQL> select FLAGS from ind$ where obj#=1343842;
FLAGS
----------
256
ind_online$字典基表记录了索引在线创建/重建的历史
SQL> select * from ind_online$;
OBJ# TYPE# FLAGS
---------- ---------- ----------
1343839 1 256
1343842 1 256
create 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 rebuilt
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
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 rebuilt
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;
/
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_INDEX
old 6: where object_name = '&INDEX_NAME')
new 6: where object_name = 'MACLEANS_INDEX')
OBJECT_NAME
--------------------------------------------------------------------------------
SYS_JOURNAL_1343845
SQL> drop table SYS_JOURNAL_1343845;
Table dropped.
2.第二步要手动修改IND$字典基表
!!!!!! 注意！手动修改数据字典要足够小心！！
select flags from ind$ where obj#=&INDEX_OBJECT_ID;
Enter value for index_object_id: 1343845
old 1: select flags from ind$ where obj#=&INDEX_OBJECT_ID
new 1: select flags from ind$ where obj#=1343845
FLAGS
----------
256
a) 针对online create index，手动删除对应的记录
delete from IND$ where obj#=&INDEX_OBJECT_ID
b) 针对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#
----------
1343854
SQL> select FLAGS from ind$ where obj#=1343854;
FLAGS
----------
256
SQL> 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" purge
delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)
delete from ind$ where bo#=:1
delete 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)
alter system set events '8105 trace name context forever';
Leave a Reply