索引：Rebuild Online 被坑的知识点-ORA-08106

原创

布衣530

发布于 2025-01-10 09:56:05

1590

文章被收录于专栏：Oracle DBA

背景

最近一直通过rebuild online 做索引碎片整理，因表均为分区表，大部分为本地索引及分区索引，有的表涉及上千个索引分区，于是我就用脚本放在后台执行了。这种情况正常运行了一个月。

突然今天巡检执行日志的时候，索引IDX_T1_ID 的其它分区重建只需要10min左右，但 IDX_T1_ID rebuild partition SYS_P3592 分区从凌晨4：29开始到中午12点都还没有结果，意识到执行不正常，查看了下索引涉及到的数据有数据正常入表，数据也可查询，目前看不影响此表的dml操作。

然后咨询开发有个定时任务4：30发起，差不多有80w左右的数据入表，查看程序日志此任务也一直没有执行结束。庆幸的是此任务仅是备份表数据功能。

排查及处理

1、先对rebuild操作进行kill，观察程序任务是否能正常执行完成
2、1小时后程序日志依然没输出，然后把程序里的SQL拿到备库查一下20min无法反回结果，按历史执行记录30min已完成入表操作，此时判断此任务已异常，于是停掉任务在数据库层kill 掉会话。
3、至此分析：任务里的【注1】：插入表任务影响到了rebuild online操作，不知什么原因导致任务里的SQL执行缓慢一直没有结果。
4、rebuild被kill导致无法重新rebuild 报错如下：

SQL> alter index IDX_T1_ID rebuild partition SYS_P3592 online tablespace two_ind_dat;
alter index IDX_T1_ID rebuild partition SYS_P3592 online tablespace two_ind_dat
*
ERROR at line 1:
ORA-08106: cannot create journal table TWO.SYS_JOURNAL_407292

5、查看SYS_JOURNAL_407292对象

select object_name,created,status From dba_objects where object_name like 'SYS_JOURNAL_%'

6、kill rebuild alert 日志输出

Wed Dec 04 12:33:39 2024
online index (re)build cleanup: objn=407292 maxretry=2000 forever=0
Wed Dec 04 12:37:19 2024
opiodr aborting process unknown ospid (125308) as a result of ORA-28
Wed Dec 04 12:48:26 2024

7、查看索引状态为：“user_objects.status=VALID”【注2】：查user_objects判断索引是否有效不准，但查询使用到此索引，感觉像是索引无效了导致SQL查询6-7小时无结果，于是计划对索引删除重建（此表仅为后台查询无交易业务需求，因此可删除重建）
8、drop index 索引报错
原因分析：在执行rebuild index online前长事务，并且并发量比较大，则一旦执行alter index rebuild online，可能因为长事务阻塞，可能导致系统瞬间出现大量的锁，对于压力比较大的系统，这是一个不小的风险。这是需要迅速找出导致阻塞的会话kill掉，rebuild index online一旦执行，不可轻易中断，否则可能遇到ORA-08104。清除动作一般有smon进程处理,如果重建过程异常中断,smon会清理重建痕迹,但是如果系统非常繁忙导致smon无法清除,或dml操作频繁,导致smon无法获取相关表上的锁,从而造成无法清理痕迹,当再次重建索引或对表进行dml操作会报本篇提示错误，这将导致对该索引的后续操作因ora-08104错误而无法继续，如果是分区表，索引是global，在添加分区也无法继续。

SQL> drop index IDX_T1_ID ;
drop index IDX_T1_ID 
           *
ERROR at line 1:
ORA-08104: this index object 407292 is being online built or rebuilt

9、调用dbms_repair.online_index_clean清理
因为还有其它rebuild 操作，因此选用指定OBJECT_ID ，【注3】：执行7次依然报错。
注：加上dbms_repair.lock_wait表示不是立刻清理，需要不断的寻找资源锁，直到抢到为止

-- 清理全部异常数据
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 清理
SQL> DECLARE
  2  RetVal BOOLEAN;
  3  OBJECT_ID BINARY_INTEGER;
  4  WAIT_FOR_LOCK BINARY_INTEGER;
  5  BEGIN
  6  OBJECT_ID := 407292;
  7  WAIT_FOR_LOCK := NULL;
  8  RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
  9  COMMIT;
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SYS.DBMS_REPAIR", line 475
ORA-06512: at line 8

10、查看alert 日志数据库一直在清理objn=369888，为另一个任务对像

Wed Dec 04 16:56:09 2024
online index (re)build cleanup: objn=369888 maxretry=2000 forever=0
online index (re)build cleanup: objn=369888 maxretry=2000 forever=0
online index (re)build cleanup: objn=369888 maxretry=2000 forever=0
online index (re)build cleanup: objn=369888 maxretry=2000 forever=0
Wed Dec 04 16:56:28 2024
online index (re)build cleanup: objn=369888 maxretry=2000 forever=0
Wed Dec 04 16:57:16 2024
online index (re)build cleanup: objn=369888 maxretry=2000 forever=0
online index (re)build cleanup: objn=369888 maxretry=2000 forever=0

11、再次查看SYS_JOURNAL%对像未发现SYS_JOURNAL_407292，然后成功删除索引、重建索引、对表进行信息收集。
12、最后再次执行SQL，10s出结果，任务重新执行30分钟数据补入完成。

【注 1】小测

表准备

create table t1 (
  id          VARCHAR2(11) not null,
  create_date TIMESTAMP(6) not null
);
create index IDX_T1_ID on t1 (id);

session1：插入数据不提交，session2：rebuild online索引
session1 一直不提交，session2就一直无法完成，符合上面的事故现像

session1 :提交，session2：完成

测试小结：索引涉及字段的修改未提交，rebuild online就无法结束。

【注 2】小测

表准备

create table t1 (
  id          VARCHAR2(11) not null,
  create_date TIMESTAMP(6) not null
)
PARTITION BY RANGE(create_date)INTERVAL(numtoyminterval(1,'month'))
    (PARTITION part202401 VALUES LESS THAN(TO_DATE('20240201','yyyymmdd')),
     PARTITION part202402 VALUES LESS THAN(TO_DATE('20240301','yyyymmdd'))
    );

数据准备

11:47:21 SQL> select count(*) from t1 partition(SYS_P1741);
  COUNT(*)
----------
  44551512

创建索引

11:48:05 SQL> create index IDX_T1_ID on t1 (id);
Index created.

查看执行计划：索引扫描

12:36:05 SQL> set  autotrace trace 
12:36:13 SQL> select * from t1 where id ='1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2209018382
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |           |     1 |    20 |     3   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T1        |     1 |    20 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | IDX_T1_ID |     1 |       |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------

Truncate 分区,查看索引状态为：VALID

12:37:20 SQL> alter table t1 truncate partition (SYS_P1741);
Table truncated.
12:37:30 SQL>  col OBJECT_NAME for a50
12:37:38 SQL> set line 800 
12:37:43 SQL> select object_name,status from user_objects where object_name='IDX_T1_ID';
OBJECT_NAME                                        STATUS
-------------------------------------------------- -------
IDX_T1_ID                                          VALID

查看执行计划:全表扫描

12:38:25 SQL> set autotrace trace 
12:38:34 SQL> select * from t1 where id ='1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 589593414
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    20 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|      |     1 |    20 |     2   (0)| 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | T1   |     1 |    20 |     2   (0)| 00:00:01 |     1 |1048575|
--------------------------------------------------------------------------------------------

通过user_indexes.status 显示索引为不可用

13:06:40 SQL>  select status from user_indexes where index_name='IDX_T1_ID';
STATUS
--------
UNUSABLE

测试小结：判断索引是否可用通过user_indexes.status 状态为准。

【注 3】dbms_repair.online_index_clean

dbms_repair.online_index_clean 的指定OBJECT_ID 的方法，好像也是清理所有rebuild online操作，因生产还有另一个rebuild任务，因此报：ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 。

生产问题复现

T1数据量：95188524,执行计划为：INDEX RANGE SCAN

Session1:进行插入，然后Session2:rebuild online索引

叉掉Session2 ssh界面，模拟当时kill会话，查看索引状态：VALID

再次rebuild online索引报错：ORA-08104

查看SYS_JOURNAL%对象

叉掉Session2 的插入界面，模拟当时杀任务

日志输出

注：以下结果是被验证N次后，Rebuild Online 和 Insert 执行1小时以上再Kill会话，才会偶尔出现。

执行计划：全表扫描

15:20:04 SQL> select * from t1 where id='1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 589593414
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    20 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|      |     1 |    20 |     2   (0)| 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | T1   |     1 |    20 |     2   (0)| 00:00:01 |     1 |1048575|
--------------------------------------------------------------------------------------------

rebuild 索引后，查询依然：全表扫描

15:26:06 SQL> alter index IDX_T1_ID rebuild;
Index altered.
15:33:49 SQL> select * from t1 where id='1';
no rows selected
Elapsed: 00:00:24.82
Execution Plan
----------------------------------------------------------
Plan hash value: 589593414
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    20 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|      |     1 |    20 |     2   (0)| 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | T1   |     1 |    20 |     2   (0)| 00:00:01 |     1 |1048575|
--------------------------------------------------------------------------------------------

收集统计信息后，查询执行计划：索引扫描

15:34:19 SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=> 'T1');
PL/SQL procedure successfully completed.
Elapsed: 00:01:51.92
16:08:04 SQL> select * from t1 where id='1';
no rows selected
Elapsed: 00:00:22.27
Execution Plan
----------------------------------------------------------
Plan hash value: 2209018382
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |           |   183 |  4209 |    27   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T1        |   183 |  4209 |    27   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | IDX_T1_ID |   183 |       |     4   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------

总结：rebuild online 注意事项

此次事件的原因分析，应该是表信息过期导致Insert异常缓慢，从而使rebuild online一直未结束。
避开数据库维护任务，示例：《记录一起索引rebuild与收集统计信息的事故》
避开大事务操作，可参考此事件;
关注索引状态：user_indexes.status；
异常中断后残留信息一般是smon进程清理，如清理不了可用dbms_repair.online_index_clean清理；
随时关注临时表，查看临时表情况：

SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

根据如下SQL查看rebuild online 进度

col opname format a32
col target format a32
col perwork format a12
set linesize 1200
select sid
      ,opname
      ,target
      ,sofar
      ,totalwork
      ,trunc(sofar/totalwork*100,2)||'%' as perwork
from v$session_longops where sofar!=totalwork and sid=132;

       SID OPNAME                        TARGET        SOFAR  TOTALWORK PERWORK
---------- --------------------- ------------------ ---------- ---------- --------
       827 Index Fast Full Scan      IDX_T1_ID         27914     157907 17.67%

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库