ORA-08104 该索引对象68100正在被联机建立或重建 如何清除创建失败的索引？
Tags： Oracle
在创建一张大表的索引时，因为没有加并行而导致创建时间很长，这时候如果终止操作，比如直接关闭终端，然后再次登录创建时就会报错ORA-00095提示对象名字已被用，而去删除对象时又报错ORA-08104索引正在被创建。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SQL> drop index xxx.BM_IX; drop index xxx.BM_IX * ERROR at line 1: ORA-08104: this index object 1443829 is being online built or rebuilt SQL> select object_id from dba_objects where object_name='BM_IX'; OBJECT_ID ---------- 1443829 $ oerr ora 08104 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 |
从oerr工具看到的ORA-08104的解释是索引并没有创建失败，而是在终端关闭之后，创建的操作还在继续进行。创建或者重建索引时，系统会创建一个临时日志表，这张表被用于存放创建或者重建索引期间产生的日志信息，同时在基表IND$中这个索引的FLAG字段上会被设置为BUILD或者REBUILD标识，当索引信息变更时会把变更信息存入日志表。如果索引创建或者重建失败，这个日志表和数据字典中的状态位都需要后台进程smon进行清理。
因此这里的索引不能被删除是因为后台进程smon还没来得及清理相应的临时段和标志位，认为online rebuild操作还在进行。
那么现在如何终止rebuild index这一操作？查找metalink得到一篇文档：ORA-600 [12813] When Dropping A Table Partition After a Failed IndexRebuild (文档 ID 803008.1)。它给出了两种方法：
1）使用包dbms_repair包来清理
如果在出现问题的对象的数据库活动能停下来，则直接简单地执行如下语句即可：
1 2 3 | connect / as sysdba select dbms_repair.online_index_clean(<problem index object_id>) from dual; exit |
2）使用PL/SQL block调用dbms_repair包来清理
如果在出现问题的对象的数据库活动不能停下来，则如下的PL/SQL block来处理
注：加上dbms_repair.lock_wait表示不是立刻清理，需要不断的寻找资源锁，直到抢到为止
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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(2); end loop; exception when others then RAISE; end; / |
测试人员报告某个sql查询操作比较慢，希望协助查找一下原因。
检查发现IDX_LOG_BUSINON 碎片较为严重，决定重建索引。
为了不影响大家使用，决定用rebuild online的方式重建该索引。
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter index IDX_LOG_BUSINON rebuild online ;
一会有人来叫去会议室讨论影像迁移的问题，与是拔掉网线拿起笔记本去了
会议室。到了会议室发现这个会话已经断开了。找根网线插上继续rebuild索引。
SQL> alter index IDX_LOG_BUSINON rebuild online ;
alter index IDX_LOG_BUSINON rebuild online
*
第 1 行出现错误:
ORA-08104: 该索引对象 68100 正在被联机建立或重建
检查了一下68100对象，发现就是要rebuild的那个索引。
SQL> select OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE
2 from dba_objects o
3 where o.object_id = '68100';
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE
REPORT IDX_LOG_BUSINON 68100 INDEX
由于之前在ORACLE 10g 上遇到过这个问题，所以觉得没啥。直接用
DBMS_REPAIR.ONLINE_INDEX_CLEAN 清理掉，在重建就好了。
SQL> desc dbms_repair
。。。省略部分描述
FUNCTION ONLINE_INDEX_CLEAN RETURNS BOOLEAN
参数名称 类型 输入/输出默认值?
OBJECT_ID BINARY_INTEGER IN DEFAULT
WAIT_FOR_LOCK BINARY_INTEGER IN DEFAULT
说明：DBMS_REPAIR.ONLINE_INDEX_CLEAN ()要求有返回值。
SQL> DECLARE
2 RetVal BOOLEAN;
3 OBJECT_ID BINARY_INTEGER;
4 WAIT_FOR_LOCK BINARY_INTEGER;
5
6 BEGIN
7 OBJECT_ID := 68100;
8 WAIT_FOR_LOCK := NULL;
9 RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
10 COMMIT;
11 END;
12 /
继续开会。。。大约20分钟会议结束。感觉索引应该rebuild结束。
但还没执行完，这时候突然紧张起来了。赶紧去看alert*.log没发现
有异常。什么原因呢，测试环境中这张表的数据并不多，应该很快就能
搞定的。为什么这么长时间还没完呢。是不是有人锁表了呢。
SQL> SELECT /+ rule /
2 s.username,
3 decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
4 o.owner,
5 o.object_name,
6 o.object_type,
7 s.sid,
8 s.serial#,
9 FROM gv$session s, gv$lock l, dba_objects o
10 WHERE l.sid = s.sid
11 AND l.id1 = o.object_id(+)
12 AND s.username is NOT NULL ;
USERNAME LOCK_LEVEL OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#
REPORT REPORT WFLOG TABLE 154 159
REPORT SYS TAB$ TABLE 154 159
REPORT REPORT WFLOG TABLE 154 159
REPORT TABLE LOCK REPORT SYS_JOURNAL_68100 TABLE 154 159
REPORT ROW LOCK 154 159
REPORT TABLE LOCK REPORT WFLOG TABLE 154 159
REPORT ROW LOCK 138 10
REPORT TABLE LOCK REPORT WFLOG TABLE 138 10
果然有人锁表了，找到那个哥们，发现她刚才也来开会了。她commit后，果然很快清理完了。
再次rebuild 这个索引，也很快搞定。
SQL> alter index IDX_LOG_BUSINON rebuild online ;
总结： 幸亏是赶在快吃饭时间用测试库的人比较少，影响比较小。
要是在生产库上就是一次严重的事故了。不过在生产库上
有严格的审批流程，没人敢去轻易操作。
结论：做事情要一心一意，不能分心。尤其是操作数据库。
ONLINE方式在线重建索引异常中断后遇到ORA-08104错误的处理思路
最近在处理ORA-08102错误时，使用ONLINE方式在线重建索引异常中断后遇到ORA-08104错误；
ORA-08104错误网上有许多相关案例和解决方法，这里我也汇总一下解决方法，记录一下本次解决的心得。
当在线重建索引"ALTER INDEX ... REBUILD ONLINE"异常中断后（异常的定义是没有正常完成吧）；再次重建时可能会遇到如下错误 ：
ORA-08104: this index object 114615 is being online built or rebuilt
删除（包括force选项）时均无法删除。
#########################################
![img]() 故障原因是：
create /rebuild index online时会修改数据库字典表obj$,并在该索引用户下创建表sys_journal_obj#（具体的对象号）和在ind$、ind_online$表里(ind_online$字典基表记录了索引在线创建/重建的历史)标记256或512,（11g里rebuild online是514？？）。
如果服务器进程在语句执行过程中意外终止的话，可能会导致相关在ind$标记位信息及在线日志中间表不能及时处理及清除（清除动作一般有smon进程处理,如果重建过程异常中断,smon会清理重建痕迹,但是如果系统非常繁忙导致smon无法清除,或dml操作频繁,导致smon无法获取相关表上的锁,从而造成无法清理痕迹,当再次重建索引或对表进行dml操作会报本篇提示错误），这将导致对该索引的后续操作因ora-08104错误而无法继续，如果是分区表，索引是global，在添加分区也无法继续。
此时对此进行验证：
view plain
- 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(2);
- end loop;
- exception
- when others then
- RAISE;
- end ;
- /
-----------------
也可以指定具体的OBJECT_ID,如：dbms_repair.online_index_clean(114615);
--------------------
关于此函数，11gR2官方文档介绍如下：
This function performs a manual cleanup of failed or interrupted online index builds
or rebuilds. This action is also performed periodically by SMON, regardless of
user-initiated cleanup.
This function returns TRUE if all indexes specified were cleaned up and FALSE if one
or more indexes could not be cleaned up.
如果一直尝试清理且不成功，对应的alert日志中会有如下提示（隔几分钟一次）：
Mon Dec 07 19:33:51 2015
online index (re)build cleanup: objn=114615 maxretry=2000 forever=0
如果一直不成功，一个可选方法是将此索引对应表的TM锁的进程KILL；使用如下语句查询：
view plain
- select status,instance_name from v$instance;
- select pid,spid from v$process p,v$bgprocess b where b.paddr=p.addr and name = 'SMON' ;
- PID SPID
- 22 1741
- oradebug wakeup 22
- select status,instance_name from v$instance;
此步骤可以多次尝试。关于为什么SMON进程未清理掉，可能是未到达SMON进程清理的阀值，或者与当时数据库负载等多种因素有关。
网上解释有：
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进程的清理工作每小时才执行一次，而且在工作负载很高的情况下可能实际很久都不会得到清理，
OBJ$基表是一张低级数据字典表，该表几乎对库中的每个对象(表、索引、包、视图等)都包含有一行记录。很多情况下，这些条目所代表的对象是不存在的对象(non-existent)，引起这种现象的一种可能的原因是对象本身已经被从数据库中删除了，但是对象条目仍被保留下来以满足消极依赖机制(negative dependency)。因为这些条目的存在会导致OBJ$表不断膨胀，这时就需要由SMON进程来删除这些不再需要的行。SMON会在实例启动(after startup of DB is started cleanup function again)时以及启动后的每12个小时执行一次清理任务(the cleanup is scheduled to run after startup and then every 12 hours)。
如果方法2唤醒SMON进程进行清理也不成功，建议是安排停机时间，重启数据库实例了。
如果实在不方便重启数据库实例，对此索引又可以暂时不执行DDL操作，那么可以暂时忽略（此时原索引状态是VALID，不影响使用），等待停机窗口对数据库实例进行重启。
如果实在不方便重启数据库实例又需要重建索引（如索引遇到ORA-08102错误），那么还有一招是修改数据库字典基表，这个方法就不介绍了，生产环境是不会用的；并且底层基表多数存在互相关联，容易出错，慎用！！！
在线重建索引 (alter index index_name rebuild online)虽然延长了索引重建的时间，却也赋予了我们在线重建索引，提高数据可用性的能力。如果在联机重建索引的过程中出现错误，如用户终止，网络中断等，那么当我们再次重建索引时，有可能会产生ORA-08104错误。这是由于先前的操作痕迹没有清除而造成的。
在线重建索引的过程中，oracle数据库会修改数据字典表，并生成中间表（IOT）来记录索引重建期间发生的dml操作。如果重建过程异常中断，smon进程会清理重建痕迹，但是如果系统非常繁忙导致smon应接不暇或者dml操作过多导致smon无法获取相关表上的锁，从而无法清理重建痕迹，当我们再次重建索引时，就会产生ora-08104错误。
下面我们构造一个ora-08104错误
[sql] view plain copy print ?
- 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;
- 10 end ;
- 11 /
- PL/SQL 过程已成功完成。
注意事项：在执行过程中，需要在索引所在表上获取锁，因此应尽可能的保证索引表不被其他事务锁定，以尽快清理临时数据,如果长时间不能清除数据，查看后台日志，我们会发现
[sql] view plain copy print ?
- sql> update ind$ set flags=flags-512 where obj#=
- sql> drop table
.sysjournal ; /这个步骤可能会报资源忙，因为有大量的日志正在插入，可以反复重试一下 /
注意顺序操作顺序，不到万不得已，不要修改数据字典
- 唤醒SMON
我们可以尝试使用ORADEBUG WAKEUP 来唤醒smon，可以多试几次
ORA-600 [12813] When Dropping A Table Partition After a Failed Index Rebuild (文档 ID 803008.1)
In this Document
| Symptoms | |
|---|---|
| Changes | |
|---|---|
| Cause | |
|---|---|
| Solution | |
|---|---|
| References | |
|---|---|
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.
SYMPTOMS
When trying to drop a partition of a table which has an index which failed an online index rebuild operation, an ORA-600 [12813] error is signaled, e.g.:
ORA-20000: this index object "
ORA-00600: internal error code, arguments: [12813], [1], [268453], [], [], [], [], []
CHANGES
An online index rebuild was canceled or interrupted.
CAUSE
This is due to the failed online index rebuild not having been cleaned up successfully.
SOLUTION
SMON should cleanup the failed online index rebuild operation and so correct this. However, if the table is highly active with transactions, SMON may not be able to get the required lock and so the index will not get cleaned up. In such situations, you can manually cleanup the failed index rebuild using the DBMS_REPAIR.ONLINE_INDEX_CLEAN procedure.
To do this, if activity on the problem table can be stopped, then simply execute:
connect / as sysdba
select dbms_repair.online_index_clean(
exit
If activity on the table cannot be stopped, then it may be possible to resolve the problem using the following PL/SQL block:
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(2);
end loop;
exception
when others then
RAISE;
end;
/
NOTE:
This may need to run for many hours however before it can finally get the required access to the table and index.
REFERENCES
NOTE:3805539.8 - Bug 3805539 - Add DBMS_REPAIR.ONLINE_INDEX_CLEAN to manually clean up failed ONLINE builds
