在线创建索引的问题案例

nanatsg 2018-09-21

展开全文

预计阅读时间：16分钟

昨天发了一篇《一张频繁DML的大表，如何正确地创建索引？》，碰巧和恩墨公众号“数据和云”，发的文章《程序媛记一次在线创建索引被kill案例及应对措施》，主题一致，可以作为补充。

《程序媛记一次在线创建索引被kill案例及应对措施》介绍的是，执行create index ... online的进程被kill，和相关可引起问题的场景，以及相应的解决方案，

对于这类问题，解决的主要方法有：

针对上图几种方法，

方法一使用存储过程dbms_repair.online_index_clean进行清理，这种方式不仅可以清理所有处于online built的索引，还可以指定具体的object_id。清理所有在线索引的语法：
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：
declare a boolean; begin a := dbms_repair.online_index_clean(1760,dbms_repair.lock_wait); end; /

但该方法具有版本限制，根据官方mos资料，在10.2及以上版本中均可以使用该存储过程，而针对10.2之前的版本，9.2.0.7和10.1.0.4可通过打上bug3805539的patch后方可使用dbms_repair包。(具体参见mos文档：Bug 3805539 - Add DBMS_REPAIR.ONLINE_INDEX_CLEAN to manually clean up failed ONLINE builds (Doc ID 3805539.8))。

方法二使用ORADEBUG唤醒SMON进程进行清理主要步骤为：首先使用sys用户登录数据库，根据以下sql查出smon的pid：
select pid,spid from v$process p,v$bgprocess b where b.paddr=p.addr and name='SMON'; 然后进行： ORADEBUG WAKEUP pid -- pid为上条sql查出的pid值

方法三重启数据库实例由SMON进程清理，需要安排停机时间，停应用然后重启数据库，此时相关表上没有了DML操作，SMON进程会完成自动清理。

方法四因需要手工修改数据库的内部数据字典，风险较大，不推荐在生产系统中使用，容易造成其他不可预见的问题。

除了上面的分享，一位好友，也分享了关于这问题，他所碰见的案例，

现象：

oracle 10.2.0.5中create index online的session被强行结束掉。

解决：

找到那个表上active的事务的session，kill之，再重新跑如下匿名块：

DECLARE
isClean BOOLEAN;
BEGIN
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN();
END;
/

之后，dba_objects和dba_indexes没有该索引，dba_objects中where object_name like 'SYS_JOURNAL%'也返回零行。这一点从《一张频繁DML的大表，如何正确地创建索引？》，10046的trace中可以看见，有对这些对象delete、drop的操作。

注意：

1. Sys_journal_xxxxx 中的xxxxx是该index的object_id。

2. 如上匿名块需要在那个表没有活动事务的情况下才能真正的运行成功，注意说的是真正的运行成功。真正的运行成功的标记是：

dba_objects和dba_indexes没有该索引。

dba_objects中where object_name like 'SYS_JOURNAL%'也返回零行。

但实际中，可能碰见到的情况是：

当该表上有活动事务时，执行如上匿名块，执行了5分钟之后，报'PL/SQL procedure successfully completed.'，但是 dba_objects和dba_indexes依然有该索引，dba_objects中where object_name like 'SYS_JOURNAL%'也返回有这一行。而且，该索引 drop 也drop不掉，rebuild也rebuild不掉。

MOS上，也有两篇文章，和这个主题相关，

1. How to Cleanup and Rebuild an Interrupted Online Index Rebuild - ORA-8104 , ORA-8106 (文档 ID 272735.1)。
2. How To Find Out Temporary Table Sys_journal_xxxxx Is Created By Rebuilding Which Index (文档 ID 1324941.1)。

我将这两篇文章，copy如下，方便朋友们，结合着来看，充分理解这些的问题，注意：文章仅限于学习！

1. How to Cleanup and Rebuild an Interrupted Online Index Rebuild - ORA-8104 , ORA-8106 (Doc ID 272735.1)

Goal

Sometimes an online rebuild of an index reports an ORA-8104. There can be many reasons for that.

For instance in a highly active database with many transactions the likelyhood of uncommitted transactions on a table is high. So if such a table needs to rebuild an index we may see this error
due to uncommitted transactions. In other cases we may see a dead process with an uncommitted transaction holding a lock on the table. This also prevents us from rebuilding the index. This is
all by design of the online rebuild functionality. However, we can't leave a database in this state forever so SMON has been designed to cleanup these cases.

A process could end up dead if it has been killed from the OS with the command kill -9.

If the process was killed while doing an:
ALTER INDEX REBUILD ONLINE;
then the index could end up in a state where it needs clearing up.

One would think a drop of the index would solve the problem. However,
that will only return an error:ORA-8104 'This index object %s is being online built or rebuilt.'

Solution

The solution is to use the ONLINE_INDEX_CLEAN procedure in the
DBMS_REPAIR package (see: PL/SQL Packages and Types Reference, 10.2)

If the index is IOT then go to : Doc ID 427626.1: ALTER TABLE MOVE ONLINE Cancelled - How to Cope if the Table is an IOT

To fix the problem:
==============

SMON will eventually cleanup the locked index so no actions are actually needed. However, letting SMON do the cleanup can be a bit of 'hit and miss' as SMON will try to cleanup every 60 minutes and if it cannot get a lock on the object with NOWAIT it will just try again later. In a highly active database with many transactions this can cause the rebuild to take a long time as SMON won't get the lock with NOWAIT. Other cases like uncommitted transactions
against the table will also result in SMON not rebuilding the index.

As long as the index is not rebuild all access to the index will result in ORA-8104 or ORA-8106.

So to solve this situation a manual cleanup can be done using the new function DBMS_REPAIR.ONLINE_INDEX_CLEAN()

SQL> conn / as sysdba
SQL> DECLARE
isClean BOOLEAN;
BEGIN
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN();
END;
/

2. How To Find Out Temporary Table Sys_journal_xxxxx Is Created By Rebuilding Which Index (Doc ID 1324941.1)

Goal

How to find out the temporary table SYS_JOURNAL_XXXXX which is created when rebuilding an index.

Solution

Create an index to be rebuilt online.

Note:
The index rebuild should take enough time for the monitoring queries to return actual results.

CREATE TABLE 'ORACLE'.'ORDERS'
( 'ORDER_ID' NUMBER(12,0),
  'ORDER_DATE' DATE
);

CREATE INDEX 'ORACLE'.'ORDERS_IND1' ON 'ORACLE'.'ORDERS' ('ORDER_ID');

begin
for idx in 1 .. 1000000 loop
insert into oracle.orders values(idx,sysdate);
commit;
end loop;
end;
/

alter index oracle.orders_ind1 rebuild online;

Monitor the index rebuilt from a different session as SYSDBA:

NOTE:
If required for this example, you can just re-execute the 'alter index rebuild online;'
for any of the below queries that need to be executed during the index rebuild.

--  For formatting the output
column owner format a10
column object_name format a20
column object_id format 9999999999
column temp_table_name format a20

--  Find all the indexes that are in the REBUILD ONLINE mode:
select obj# from sys.ind$ where bitand(flags, 512) = 512;
      OBJ#
----------
     90003

select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects
where object_id =
(select obj# from sys.ind$ where bitand(flags, 512) = 512);
OWNER      OBJECT_NAME            OBJECT_ID
---------- -------------------- -----------
ORACLE     ORDERS_IND1                90003

select OWNER,OBJECT_NAME,OBJECT_ID from dba_objects where object_name like
'SYS_JOURNAL%';
OWNER      OBJECT_NAME            OBJECT_ID
---------- -------------------- -----------
ORACLE     SYS_JOURNAL_90003          90005

In this example you see:

INDEX 'ORACLE'.'ORDERS_IND1' object id is 90003.
The temp table being used to rebuild this index is named SYS_JOURNAL_90003.
So the naming rule is 'SYS_JOURNAL_'+OBJECT_ID.
You can find the relationship of objects and temp tables directly using the following query:

select a.object_name, b.table_name temp_table_name
from   dba_objects a,
       ( select substr(object_name,13) as obj_id, 
                object_name as table_name
         from dba_objects
         where object_name like 'SYS_JOURNAL_%') b
where a.OBJECT_ID = b.obj_id;
OBJECT_NAME          TEMP_TABLE_NAME
-------------------- --------------------
ORDERS_IND1          SYS_JOURNAL_90003