本文共 10205 字，大约阅读时间需要 34 分钟。

rebuild online 创建时，会话被Kill修复索引

1.0实验目的：日常运维经常create index online,但是期间被kill会导致索引再次创建失败，测试解决该问题

2.0测试流程：

创建一个测试表，创建一个测试Local索引，记录创建时间(有足够Kill session time)查询会话ID创建测试分区索引kill 会话查询视图，该索引状态drop 索引尝试再次创建索引进行测试使用dbms包进行清理

3.0创建测试表，测试索引创建时间

#drop table scott.t purge;SCOTT > create table scott.t(id int,name varchar2(20)) partition by hash(id) partitions 4;set timing ondeclarev_id int;beginfor v_id in 1 .. 5000000loopinsert into scott.t values(v_id,'test'||v_id);end loop;commit;end;/Elapsed: 00:10:27.90create index scott.t_p_ind on scott.t(id) local;Elapsed: 00:00:20.25

drop index scott.t_p_ind;

3.1 kill session script

SYS > select inst_id,sid,serial#,paddr from gv$session where sid =(select sid from v$mystat where rownum=1); INST_ID SID SERIAL# PADDR---------- ---------- ---------- ---------------- 1 41 2799 0000000073A1C4A8select '!ps -ef|grep '||pro.spid ||' '|| '!kill -9 '||pro.spid from v$session ses,v$process pro where ses.sid in( 41 ) and ses.paddr=pro.addr

'!PS-EF|GREP'||PRO.SPID||''||'!KILL-9'||PRO.SPID

--------------------------------------------------------------------------------!ps -ef|grep 3868 !kill -9 3868 3.2 create index SQL executecreate index scott.t_p_ind on scott.t(id) online; 3.3 kill create index session!kill -9 3868

create index scott.t_p_ind on scott.t(id) online

ERROR at line 1:ORA-03113: end-of-file on communication channelProcess ID: 3868Session ID: 41 Serial number: 2799

3.4查询索引被kill后的状态SYS > select owner,index_name,status from dba_indexes where owner='SCOTT' and index_name='T_P_IND';OWNER INDEX_NAME STATUS--------- ---------------- --------SCOTT T_P_IND VALID

####创建一个其它列索引，使用hint 是否强制走索引？ [failed] 不重要，可忽略SYS > create index scott.test_ind on scott.t(name);set autotrace onselect count(name) from scott.t; COUNT(*)---------- 5000000-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4389 (1)| 00:00:53 | | || 1 | RESULT CACHE | cnrwbkdx2qdpa9s91jr2ka3jzb | | | | | || 2 | SORT AGGREGATE | | 1 | | | | || 3 | PARTITION HASH ALL| | 4759K| 4389 (1)| 00:00:53 | 1 | 4 || 4 | TABLE ACCESS FULL| T | 4759K| 4389 (1)| 00:00:53 | 1 | 4 |-----------------------------------------------------------------------------------------------------------

select /*+ INDEX(TEST_IND) */ count(*) from scott.t;

-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 4389 (1)| 00:00:53 | | || 1 | RESULT CACHE | cnrwbkdx2qdpa9s91jr2ka3jzb | | | | | || 2 | SORT AGGREGATE | | 1 | | | | || 3 | PARTITION HASH ALL| | 4759K| 4389 (1)| 00:00:53 | 1 | 4 || 4 | TABLE ACCESS FULL| T | 4759K| 4389 (1)| 00:00:53 | 1 | 4 |-----------------------------------------------------------------------------------------------------------

SYS > desc scott.t Name Type -------------------------------------------- ID NUMBER(38) NAME VARCHAR2(20) alter table scott.t modify name not null;再次重复上述操作，还是全表扫描收集统计信息analyze table SCOTT.T compute statistics*ERROR at line 1:ORA-08104: this index object 91965 is being online built or rebuilt。。。。。。出现之前online创建索引报错的信息，无法收集该索引统计信息

单独收集索引信息

Analyze index scott.TEST_IND estimate statisticsIndex analyzed.

再次重复上述查询

count(*)还是未走索引，11g Oracle优化器使用的是基于成本的考量，因此我们无法强制改变，hint只是一定范围内的提示，而并非100%指定

修改上述思路，查询一条记录

SYS > select count(*) from scott.t t where name LIKE 'test999999';

COUNT(*)

---------- 1------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 12 | | ||* 2 | INDEX RANGE SCAN| TEST_IND | 1 | 12 | 3 (0)| 00:00:01 |------------------------------------------------------------------------------

drop index scott.TEST_IND;

SYS > select count(*) from scott.t t where name LIKE 'test999999';

COUNT(*)

---------- 1-------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 12 | 4398 (1)| 00:00:53 | | || 1 | RESULT CACHE | 0n8qmskbrjq8sbj8x2d00ugtz4 | | | | | | || 2 | SORT AGGREGATE | | 1 | 12 | | | | || 3 | PARTITION HASH ALL| | 186 | 2232 | 4398 (1)| 00:00:53 | 1 | 4 ||* 4 | TABLE ACCESS FULL| T | 186 | 2232 | 4398 (1)| 00:00:53 | 1 | 4 |-------------------------------------------------------------------------------------------------------------------#####

回归本次实验目的，测试的是online create index kill 之后，对索引问题的修复本次索引虽然状态是好的，但实际索引是有问题的SYS > select owner,index_name,status from dba_indexes where owner='SCOTT' and index_name='T_P_IND';OWNER INDEX_NAME STATUS--------- ---------------- --------SCOTT T_P_IND VALID

SYS > select count(*) from scott.t where id=1;

COUNT(*)---------- 1----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1389 (22)| 00:00:17 | | || 1 | RESULT CACHE | d74bgy2vpgmj18rsc3c4w4dygh | | | | | | || 2 | SORT AGGREGATE | | 1 | 13 | | | | || 3 | PARTITION HASH SINGLE| | 44 | 572 | 1389 (22)| 00:00:17 | 4 | 4 ||* 4 | TABLE ACCESS FULL | T | 44 | 572 | 1389 (22)| 00:00:17 | 4 | 4 |----------------------------------------------------------------------------------------------------------------------

如果索引状态正常，执行计划将走索引，而非table access full 全表扫描，如何解决索引问题？

3.5drop 索引尝试drop index soctt.T_P_IND;drop index soctt.T_P_IND *ERROR at line 1:ORA-01418: specified index does not exist

再次创建索引进行测试

SYS > create index scott.t_t on scott.t(id);

create index scott.t_t on scott.t(id) *ERROR at line 1:ORA-01408: such column list already indexed

对该索引rebuild重建

alter index scott.t_p_ind rebuild online;

ERROR at line 1:ORA-08104: this index object 91970 is being online built or rebuilt

查询报错解释说明，该索引正在重建SYS > !oerr ora 0810408104, 00000, "this index object %s is being online built or rebuilt"// *Cause: the index is being created or rebuild or waited for recovering // from the online (re)build // *Action: wait the online index build or recovery to complete

MOS （ID 375856.1)

在运行在线索引重建时，会话被终止或以其他方式异常终止。您现在正在尝试再次运行索引重建并抛出错误：已创建dbms_repair.online_index_clean函数以清除联机索引重建。使用dbms_repair.online_index_clean函数来解决此问题。

参考LOB

https://blog.csdn.net/haibusuanyun/article/details/50236057

create /rebuild index online时会修改数据库字典表obj$,并在该索引用户下创建表sys_journal_obj#和ind$、ind_online$表里如果服务器进程在语句执行过程中意外终止的话，可能会导致相关在ind$标记位信息及在线日志中间表不能及时处理及清除（清除动作一般有smon进程处理,如果重建过程异常中断,smon会清理重建痕迹,但是如果系统非常繁忙导致smon无法清除,或dml操作频繁,导致smon无法获取相关表上的锁,从而造成无法清理痕迹,当再次重建索引或对表进行dml操作会报本篇提示错误），这将导致对该索引的后续操作因ora-08104错误而无法继续，如果是分区表，索引是global，在添加分区也无法继续。

 

4.0解决问题

4.1表没有tm锁，调用存储过程进行清理4.2表存在事务，需等待事务结束4.3存在大量的rebuild online被结束，批量操作

4.1表没有tm锁，调用存储过程进行清理

--查询字典表中的记录 select i.obj#, i.flags, u.name, o.name, o.type# from sys.obj$ o, sys.user$ u, sys.ind_online$ i where (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512) and (not ((i.type# = 9) and bitand(i.flags, 8) = 8)) and o.obj# = i.obj# and o.owner# = u.user#; OBJ# FLAGS NAME NAME TYPE#---------- ---------- ---------------- ------------------- 91970 256 SCOTT T_P_IND 1

 

--调用dbms_repair.online_index_clean

declarea boolean;begina := dbms_repair.online_index_clean(91970,dbms_repair.lock_wait);end;/

SYS > select owner,index_name,status from dba_indexes where owner='SCOTT' and index_name='T_T';

no rows selected

4.2表存在事务，需等待事务结束--如果此时，表存在TM 3 dml事务？create index scott.t_t on scott.t(id) online;*ERROR at line 1:ORA-03113: end-of-file on communication channelProcess ID: 4245Session ID: 65 Serial number: 115--再次查询视图 OBJ# FLAGS NAME NAME TYPE#---------- ---------- ------------------------------ ------------------------------ ---------- 91974 256 SCOTT T_T 1--dml操作

SYS > delete scott.t where rownum<2;

1 row deleted.--调用dbms_repair.online_index_cleandeclarea boolean;begina := dbms_repair.online_index_clean(91974,dbms_repair.lock_wait);end;/ --会话hang住

Alert logSat May 12 10:03:52 2018online index (re)build cleanup: objn=91974 maxretry=2000 forever=0

--

select object_name,s.sid,s.serial#,s.username,' '||'!kill -9 '||p.spid as"kill"from v$locked_object l , dba_objects o , v$session s , v$process pwhere l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr and o.owner='SCOTT' and o.object_name='T';OBJECT_NAM SID SERIAL# USERNAME kill---------- ---------- ---------- ---------- --------------------T 40 2437 SYS !kill -9 4114T 40 2437 SYS !kill -9 4114--kill session可释放资源

--4.3存在大量的rebuild online被结束，批量操作

declare

isClean boolean;beginisClean := FALSE;while isClean=FALSE loopisClean := dbms_repair.online_index_clean(dbms_repair.all_index_id,dbms_repair.lock_wait);dbms_lock.sleep(2);end loop;exceptionwhen others thenRAISE;end;

 

--测试创建两个不同用户下的online失效

create index scott.t_t on scott.t(id) online;

kill sessionHR > create table hr.t1 as select * from scott.t;select distinct sid from v$mystat;create index hr.c1 on hr.t1(id) online;kill session

--查询视图记录

OBJ# FLAGS NAME NAME TYPE#---------- ---------- ----------------- -------------------- 91977 256 SCOTT T_T 1 91987 256 HR C1 1--执行存储过程 PL/SQL procedure successfully completed.--Alert日志记录Sat May 12 10:19:29 2018online index (re)build cleanup: objn=91977 maxretry=2000 forever=0online index (re)build cleanup: objn=91987 maxretry=2000 forever=0

--重建完毕，如果存在锁资源，相信也无法申请，kill或等待即可