ONLINE方式在线重建索引异常中断后遇到ORA-08104错误的处理思路

版权声明：本文为CSDN博主「还不算晕」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/haibusuanyun/article/details/50236057

最近在处理ORA-08102错误时，使用ONLINE方式在线重建索引异常中断后遇到ORA-08104错误；
ORA-08104错误网上有许多相关案例和解决方法，这里我也汇总一下解决方法，记录一下本次解决的心得。

当在线重建索引”ALTER INDEX … REBUILD ONLINE”异常中断后（异常的定义是没有正常完成吧）；再次重建时可能会遇到如下错误 ：
ORA-08104: this index object 114615 is being online built or rebuilt
删除（包括force选项）时均无法删除。

故障原因是：
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，在添加分区也无法继续。

此时对此进行验证：
set lines 200 pages 50000
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#;
可以查到一行数据，对应索引的OBJECT_ID：114615 ；
（如果有多行可能是同时有其它管理员也在创建索引或者是不止一个创建索引的问题）

此时的解决办法有如下：
1.使用存储过程dbms_repair.online_index_clean来清理
2.使用ORADEBUG唤醒SMON进程进行清理
3.重启数据库实例时由SMON进程进行清理

4.修改数据库基表INX$ obj$等来解决

分别介绍一下这些方法：

建议的方法是使用方法1的存储过程来清理，但是实际使用过程中，可能出现很久都无法清理的情况或者一直无法清理；使用的语句是：

--Doc ID 1378173.1
SET SERVEROUTPUT ON
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;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/

如下loop清理所有：
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);

conn / as sysdba
DECLARE
isClean BOOLEAN;
BEGIN
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(114615);     --ORA-08104(114615)
END;
/

关于此函数，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；使用如下语句查询：
select object_name,s.sid,s.serial#,p.spid
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr and object_name='ARRAY_CMN_GLASS_T';

–注意RAC时使用GV$表；
幸运的是，我遇到的问题在存储过程执行两小时左右时清理掉了，后面再次通过删除、重建索引的方法完成索引重建；此过程中也有遇到ORA-00054的锁表问题，此问题好处理，要么等，要么KILL相应进程；本次是协调后KILL了相应进程即可。

如果方法1不能成功时，可以尝试ORADEBUG唤醒SMON进程进行清理；
SYS用户登陆：
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错误），那么还有一招是修改数据库字典基表，这个方法就不介绍了，生产环境是不会用的；并且底层基表多数存在互相关联，容易出错，慎用！！！
————————————————
版权声明：本文为CSDN博主「还不算晕」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/haibusuanyun/article/details/50236057