暂无图片
暂无图片
9
暂无图片
暂无图片
4
暂无图片
首页 / 索引：Rebuild Online 被坑的知识点-ORA-08106

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

原创 布衣 2024-12-09
571

背景

  最近一直通过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_%'

复制

image.png

  • 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就一直无法完成，符合上面的事故现像
    image.png
  • session1 :提交，session2：完成
    image.png
  • 测试小结：索引涉及字段的修改未提交，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
    image.png

  • Session1:进行插入，然后Session2:rebuild online索引
    image.png

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

  • 再次rebuild online索引报错：ORA-08104
    image.png

  • 查看SYS_JOURNAL%对象
    image.png

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

  • 日志输出
    image.png

注：以下结果是被验证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%


复制

欢迎赞赏支持或留言指正
image.png

墨力计划ora-08106ora-08104
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
Z
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

布衣-DBA案例（共105篇）
ORACLE 运维操作记录

评论

千山暮雨
暂无图片
2月前
评论
暂无图片 0
为什么不先查一下重建索引会话在等待什么？这样不是更容易确认问题原因吗？
2月前
暂无图片 点赞
评论
杜伟
暂无图片
3月前
评论
暂无图片 0
深度好文
3月前
暂无图片 点赞
评论
11 0
暂无图片
3月前
评论
暂无图片 0
👌
3月前
暂无图片 点赞
评论
星星之火
暂无图片
3月前
评论
暂无图片 0
再次查看SYS_JOURNAL%对像未发现SYS_JOURNAL_407292，然后成功删除索引、重建索引、对表进行信息收集。
3月前
暂无图片 点赞
评论