---

---

测试人员报告某个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选项）时均无法删除。
#########################################

故障原因是：

 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，在添加分区也无法继续。

此时对此进行验证：
可以查到一行数据，对应索引的OBJECT_ID：114615 ；
（如果有多行可能是同时有其它管理员也在创建索引或者是不止一个创建索引的问题）

#########################################

此时的解决办法有如下：

1.使用存储过程dbms_repair.online_index_clean来清理
2.使用ORADEBUG唤醒SMON进程进行清理
3.重启数据库实例时由SMON进程进行清理
4.修改数据库基表INX$ obj$等来解决
-------------------
分别介绍一下这些方法：
建议的方法是使用方法1的存储过程来清理，但是实际使用过程中，可能出现很久都无法清理的情况或者一直无法清理；使用的语句是：
---------------------
如下loop清理所有：

-----------------
也可以指定具体的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；使用如下语句查询：

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

如果方法1不能成功时，可以尝试ORADEBUG唤醒SMON进程进行清理；
SYS用户登陆：


此步骤可以多次尝试。关于为什么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错误

要解决ora-08104错误，就要清除重建痕迹，总结方法如下：

• ONLINE_INDEX_CLEAN Function

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.

Syntax

DBMS_REPAIR.ONLINE_INDEX_CLEAN (
   object_id      IN BINARY_INTEGER DEFAULT ALL_INDEX_ID,
   wait_for_lock  IN BINARY_INTEGER DEFAULT LOCK_WAIT)
 RETURN BOOLEAN;

Parameters

注意事项：在执行过程中，需要在索引所在表上获取锁，因此应尽可能的保证索引表不被其他事务锁定，以尽快清理临时数据,如果长时间不能清除数据，查看后台日志，我们会发现

• 手工修改数据字典，清除中间表

• 唤醒SMON   

我们可以尝试使用ORADEBUG WAKEUP 来唤醒smon，可以多试几次