在生产库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
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秒