心态崩了，索引失效无法重建，后面领导站了一排！！！

作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验，
Oracle、PostgreSQL ACE
全网粉丝10万+
擅长主流Oracle、MySQL、PG、高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理
微信:jem_db
QQ交流群:587159446
公众号：IT邦德

@

• 1.故障现象

• 2.故障分析

• 3.故障处理

• 3.1 清理索引创建失败信息

• 3.2 重新rebuild索引

• 4.索引失效原因

• 4.1 普通表索引失效的情况

• 4.2 分区表索引失效的情况

• 5.无效索引监控

• 6.总结

前言

一次生产环境索引相关故障的处理过程

1.故障现象

今日业务反馈用户业务语句执行报错，OGG数据同步也中断了，业务系统出现大面积卡顿，影响业务，排查发现核心业务表索引出现了unusable，部分分区重建失败再次重建报错如下

2.故障分析

由于该分区表的本地分区索引状态变为 unusable，
故需要对分区索引进行重建。
在重建时临时表空间不够大，无法扩展导致部分分区重建失败。
扩展临时表空间后再次重建时，因为前面
重建索引分区语句异常终止导致 ORA-08106 错误。
这是由于在线创建时，原来的老索引保留不动，
并且允许用户访问原来的索引。
针对该索引的所有 DML 操作，都将记录在 Journal table 中。
该表是索引创建过程中的临时过渡用的中间表。
由于异常终止，Oracle 认为索引创建还在进行。
所以需要手动执行存储过程 dbms_repair.online_index_clean 
来清理掉这些异常留下的痕迹，删掉重建该分区索引。

3.故障处理

3.1 清理索引创建失败信息

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;
/

3.2 重新rebuild索引

alter index EDS_DEFECT_IDX1 rebuild \
partition  EDS_DEFECT_2024 \
tablespace PROPOSAL_DAT_IDX  online parallel 4;
注意：
保证临时表空间足够，注意归档的空间
极端情况需要删除索引后重建

4.索引失效原因

4.1 普通表索引失效的情况

1、手动置索引无效：
ALTER INDEX IND_OBJECT_ID UNUSABLE;
2、如果对表进行 MOVE 操作(包含移动表空间和压缩操作)或在线重定义表后，
那么该表上所有的索引状态会变为 UNUSABLE。
MOVE 操作的 SQL 语句为：ALTER TABLE TT MOVE;
3、SQL*Loader 加载数据。
在 SQL*Loader 加载过程中会维护索引，
由于数据量比较大，在SQL*Loader 加载过程中出现异常情况，
也会导致 Oracle 来不及维护索引，导致索引处于失效状
态，影响查询和加载。
异常情况主要有：
在加载过程中杀掉 SQL*Loader 进程、重启或表空间不足等。

4.2 分区表索引失效的情况

--不管是全局索引和本地索引，只要出现了数据移动，那么索引或分区索引都会失效:
1)对分区表的某个含有数据的分区执行了 
TRUNCATE、DROP 操作可以导致该分区表的全局索引失效，
而分区索引依然有效，如果操作的分区没有数据，
那么不会影响索引的状态。需要注意的是，
对分区表的 ADD 操作对分区索引和全局索引没有影响。
2)执行 EXCHANGE 操作后，
全局索引和分区索引都无条件地会被置为 UNUSABLE
（无论分区是否含有数据）。
但是，若包含 INCLUDING INDEXES 子句(缺省情况下为 EXCLUDING INDEXES)，
则全局索引会失效，而分区索引依然有效。
3)如果执行 SPLIT 的目标分区含有数据，
那么在执行 SPLIT 操作后，全局索引和分区索引都会
被被置为 UNUSABLE。
如果执行 SPLIT 的目标分区没有数据，
那么不会影响索引的状态。
4)对分区表执行 MOVE 操作后，
全局索引和分区索引都会被置于无效状态。
5)手动置其无效：ALTER INDEX IND_OBJECT_ID UNUSABLE;。
对于分区表而言，除了 ADD 操作之外，
TRUNCATE、DROP、EXCHANGE 和 SPLIT 
操作均会导致全局索引失效，
但是可以加上 UPDATE GLOBAL INDEXES 子句让全局索引不失效。

5.无效索引监控

查询数据库中所有无效索引：
select index_name name,'No Partition' partition,'No Subpartition' Subpartition,status
from all_indexes where status not in('VALID','USABLE','N/A')
union
select index_name name,partition_name partition,'No Subpartition' Subpartition,status
from all_ind_partitions where status not in('VALID','USABLE','N/A')
union
select index_name name,partition_name partition,subpartition_name Subpartition,status
from all_ind_subpartitions where status not in('VALID','USABLE','N/A');

6.总结

Oracle索引是一种供服务器在表中快速查找一个行的数据库结构,合理使用索引能够大大提高数据库的运行效率。那当然日常的运维也非常的重要。