rebuild index online prompted ora-08106

 在生产库rebuild online时cancel后,重新build后提示:ORA-08106: cannot create journal table TEST.SYS_JOURNAL_68422,查询相关文档后,可以用dbms_repair.online_index_clean方式修复。    

  为了保险起见,找了一个LINUX 11.2.0.3 RAC环境测试,脚本如下:

 create table temp

(owner varchar2(20), 
 object_name  varchar2(50), 
 SUBOBJECT_NAME  varchar2(20), 
 OBJECT_TYPE varchar2(20) , 
 status varchar2(20),
 create_time timestamp
 )
 partition by range(create_time)
 (
 partition PAR_2013_12_24 values less than (TIMESTAMP' 2013-12-25 00:00:00'),
 partition PAR_2013_12_25 values less than (TIMESTAMP' 2013-12-26 00:00:00'),
 partition PAR_2013_12_26 values less than (TIMESTAMP' 2013-12-27 00:00:00'),
 partition PAR_2013_12_27 values less than (TIMESTAMP' 2013-12-28 00:00:00'),
 partition MAX_PART values less than (maxvalue)
 );

create index t_idx_temp on temp(owner,object_name,OBJECT_TYPE) local;

重复插入,直到几十万数据:
insert into temp
select a.owner, a.OBJECT_NAME, substr(a.SUBOBJECT_NAME,1,20), a.OBJECT_TYPE, a.status, sysdate 
From dba_objects a;

另开一个SQLPLUS插入一批数据不提交,接着REBUILD索引,等待10几20秒后Cancel,再REBUILD后出现错误:

SQL> set time on           
10:01:52 SQL> SET SERVEROUTPUT ON

10:07:29 SQL> alter index t_idx_temp rebuild partition PAR_2013_12_25 online;


alter index t_idx_temp rebuild partition PAR_2013_12_25 online
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation



10:11:30 SQL> alter index t_idx_temp rebuild partition PAR_2013_12_25 online;
alter index t_idx_temp rebuild partition PAR_2013_12_25 online
*
ERROR at line 1:
ORA-08106: cannot create journal table TEST.SYS_JOURNAL_68422


切换到SYS用户
10:17:25 SQL> conn / as sysdba
Connected.
10:17:38 SQL>  DECLARE
10:17:45   2      isClean BOOLEAN;
10:17:45   3   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;
 /10:17:45   4  10:17:45   5  10:17:45   6  10:17:45   7  10:17:45   8  10:17:45   9  10:17:45  10  10:17:45  11  10:17:45  12  10:17:45  13  10:17:45  14  



PL/SQL procedure successfully completed.


10:18:34 SQL> 10:18:34 SQL> conn test/test

Connected.

重新rebuild online

10:18:49 SQL> alter index t_idx_temp rebuild partition PAR_2013_12_25 online;


Index altered.

到这为止,测试成功!

记录:生产库下使用dbms_repair.online_index_clean情况:

*******************************
10:14:43 SQL> SET SERVEROUTPUT ON
10:14:46 SQL>  DECLARE
10:14:50   2      isClean BOOLEAN;
10:14:50   3   BEGIN
10:14:50   4     isClean := FALSE;
10:14:50   5     WHILE isClean=FALSE
10:14:50   6       LOOP
10:14:50   7       isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(DBMS_REPAIR.ALL_INDEX_ID, DBMS_REPAIR.LOCK_WAIT);
10:14:50   8       DBMS_LOCK.SLEEP(10);
10:14:50   9     END LOOP;
10:14:50  10   EXCEPTION
10:14:50  11     WHEN OTHERS THEN
10:14:50  12       RAISE;
10:14:50  13   END;
10:14:50  14   /


PL/SQL procedure successfully completed.


10:20:41 SQL> 

耗时5分51秒


发布了36 篇原创文章 · 获赞 12 · 访问量 16万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览