在线reduild索引的故障
背景
在导入数据后，需要给数据创建索引，但有一些索引会提示报错。于是就手工重建了已有的索引。
由于第一次在线重创建索引时，没有使用到平行度，于是Ctril+C了，但它并立刻结束，而是处于后台运行。于是乎使用了droptable，与drop index都失败的，没有找到相关的sql_id与对应的OS进程情况下，找到了mos。
过程
模拟表
create table rebuildindex as select * from dba_objects ;
insert into rebuildindex select * from rebuildindex;
insert into rebuildindex select * from rebuildindex;
insert into rebuildindex select * from rebuildindex;
模拟故障
create index ind_rebuildindex_DATA_OBJECT_ID on rebuildindex (DATA_OBJECT_ID);
alter index ind_rebuildindex_DATA_OBJECT_ID rebuild online ;
Ctrl+c
alter index ind_rebuildindex_DATA_OBJECT_ID rebuild online ;
报障（ORA-08104）
两次重建报错
CZQTEST@192.168.6.11:1521/gdsndb 2021-11-17 17:53:26 >alter index ind_rebuildindex_DATA_OBJECT_ID rebuild online ;
alter index ind_rebuildindex_DATA_OBJECT_ID rebuild online
*
ERROR at line 1:
ORA-08104: this index object 75245 is being online built or rebuilt
删除索引报错
CZQTEST@192.168.6.11:1521/gdsndb 2021-11-17 17:54:45 >drop index ind_rebuildindex_DATA_OBJECT_ID;
drop index ind_rebuildindex_DATA_OBJECT_ID
*
ERROR at line 1:
ORA-08104: this index object 75245 is being online built or rebuilt
删除表报错
CZQTEST@192.168.6.11:1521/gdsndb 2021-11-17 17:54:52 >drop table rebuildindex purge;
drop table rebuildindex purge
*
ERROR at line 1:
ORA-08104: this index object 75245 is being online built or rebuilt
导入数据报错
[oracle@czq scripts]$ cat impdp_dulplicate_data.par.log22
Import: Release 12.2.0.1.0 - Production on Wed Nov 17 16:04:18 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@PM AS SYSDBA parfile=impdp_dulplicate_data.par logfile=impdp_dulplicate_data.par.log2
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SUSER"."PRJREQFORM" 60.12 MB 123739 rows
ORA-31693: Table data object "czq"."rebuildindex " failed to load/unload and is being skipped due to error:
ORA-08104: this index object 89254 is being online built or rebuilt
. . imported "SUSER"."INT__PO_HEADER" 48.05 MB 285030 rows
索引状态生效中
select status from dba_indexes where index_name='IDXMFT_PROJECT_NUM';
STATUS
--------
VALID
没找到相关的进程与会话
本来想着杀会话和进程的。等smon整理的。
SQL> select sql_text from v$sql where sql_id in (select sql_id from v$session where sql_id is not null);
SQL_TEXT
--------------------------------------------------------------------------------
select t.schema, t.name, t.flags, q.name, t.timezone, a.owner_instance owner fr
om system.aq$_queue_tables t, system.aq$_queues q, sys.aq$_queue_table_aff
inities a where t.objno = :1 and q.table_objno = t.objno and a.table_objno = t.
objno and q.usage = 0 and NOT ( t.name in ('DEF$_AQCALL' ,
'DEF$_AQERROR') and t.schema = 'SYSTEM')
select sql_text from v$sql where sql_id in (select sql_id from v$session where s
ql_id is not null)
再次重建还是一样错。
SQL> alter index PMISUSER.IDXMFT_PROJECT_NUM rebuild parallel 64;
alter index PMISUSER.IDXMFT_PROJECT_NUM rebuild parallel 64
*
ERROR at line 1:
ORA-08104: this index object 89254 is being online built or rebuilt
解决
- SMON每隔60分钟会处理类似这种情况，但也有运气成分。因为当smon遇到该索引所在的表（基表，分区、子分区）存在未提交的事务时，就不起任何作用了。所以还是手工清理稳妥。
- 只要不重要索引，所有对索引的访问都会导致ora-08104或ora-8106
- DBMS_REPAIR 包中的 ONLINE_INDEX_CLEAN 过程（参见：PL/SQL Packages and Types Reference, 10.2）
- 10.1以前的版本没有这个包，所以只能等待。10.2版本后就可以使用了。
- mos：1384434.1 272735.1
- 使用dbms_repair.ONLINE_INDEX_CLEAN(OBJ_ID)清理后，再重建索引即可解决。
SQL> DECLARE
2 isClean BOOLEAN;
3 BEGIN
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(89254);
4 5 END;
6 /
PL/SQL procedure successfully completed.
总结
- 虽然本次是在测试库，但也感谢这次测试库，生产上的操作会引起客户的意见与生产事故。会给自己，公司和客户一定的负面印象。毕竟圈子小。每个人都有自己的信誉度，当信誉度降到一定程度后，就没有叫你干活了。创业也是一样。
- 谨慎操作生产库，回车前，多一次审核，多一点敬畏，多一点备份，多一些真诚。
- 操作前了解所操作的基本原理。如：rebuild onlie 与rebuild区别。
- 有始有终，如：开平行创建索引后，怎么关平行？
浙公网安备 33010602011771号