ORA-01502: index 'SYS.WRH$_ROWCACHE_SUMMARY

本文详细记录了在Oracle数据库中遇到的WRH$_ROWCACHE_SUMMARY_PK索引不可用问题的排查与解决过程，包括重建索引、设置分区属性等步骤。

最近数据库的日志中，总是有大量的trc文件，规律是每隔1个小时产生。
之前以为是BUG，于是打了p6972843 无果。

点击(此处)折叠或打开

-rw-r----- 1 oracle dba 1221 Mar 10 05:00 qs0000z1_m000_884766.trc
-rw-r----- 1 oracle dba 1219 Mar 10 06:00 qs0000z1_m000_950356.trc
-rw-r----- 1 oracle dba 1220 Mar 10 07:00 qs0000z1_m000_880664.trc
-rw-r----- 1 oracle dba 1220 Mar 10 08:00 qs0000z1_m000_938154.trc
-rw-r----- 1 oracle dba 1220 Mar 10 09:00 qs0000z1_m000_938222.trc
-rw-r----- 1 oracle dba 1220 Mar 10 10:00 qs0000z1_m000_979256.trc
-rw-r----- 1 oracle dba 1220 Mar 10 11:00 qs0000z1_m000_983306.trc
-rw-r----- 1 oracle dba 1220 Mar 10 12:00 qs0000z1_m000_823354.trc
-rw-r----- 1 oracle dba 1222 Mar 10 13:00 qs0000z1_m000_1048844.trc
-rw-r----- 1 oracle dba 1220 Mar 10 14:00 qs0000z1_m000_975096.trc
-rw-r----- 1 oracle dba 1220 Mar 10 15:00 qs0000z1_m000_811240.trc
-rw-r----- 1 oracle dba 1220 Mar 10 16:00 qs0000z1_m000_884944.trc
-rw-r----- 1 oracle dba 1222 Mar 10 17:00 qs0000z1_m000_1020024.trc

重日志产生的过程中，可以看到，应该是oracle的自动任务。AWR的统计信息。
打开其中一个trc文件，查看

*** ACTION NAME:(Auto-Flush Slave Action) 2014-03-11 13:00:39.814
*** MODULE NAME:(MMON_SLAVE) 2014-03-11 13:00:39.814
*** SERVICE NAME:(SYS$BACKGROUND) 2014-03-11 13:00:39.814
*** SESSION ID:(899.17958) 2014-03-11 13:00:39.814
*** KEWROCISTMTEXEC - encountered error: (ORA-01502: index 'SYS.WRH$_ROWCACHE_SUMMARY_PK' or partition of such index is in unusable state
)
*** SQLSTR: total-len=548, dump-len=240,
STR={insert into wrh$_rowcache_summary (snap_id, dbid, instance_number, parameter, total_usage, usage, gets, getmisses, scans, scanmisses, scancompletes, modifications, flushes, dlm_requests, dlm_conflicts, dlm_releases) select :snap}
*** KEWRAFM1: Error=13509 encountered by kewrfteh

可以看到 SYS.WRH$_ROWCACHE_SUMMARY_PK 索引不可用。
分析一下主键索引

SQL> ANALYZE INDEX SYS.WRH$_ROWCACHE_SUMMARY_PK VALIDATE STRUCTURE;

ANALYZE INDEX SYS.WRH$_ROWCACHE_SUMMARY_PK VALIDATE STRUCTURE

ERROR at line 1:

ORA-01502: index 'SYS.WRH$_ROWCACHE_SUMMARY_PK' or partition of such index is

in unusable state

select * from dba_indexes where index_name='WRH$_ROWCACHE_SUMMARY_PK'

查询主键索引状态为

STATUS UNUSABLE

第一次解决问题的时候。手工把主键索引重新rebuild了一下。

alter index SYS.WRH$_ROWCACHE_SUMMARY_PK rebuild online;

有一段时间问题没有错误日志出现了，期间重启过一次服务器，重启之后发现又有上次的报错信息。

查询主键索引的对应表是 WRH$_ROWCACHE_SUMMARY
SQL> select TABLE_NAME,INDEX_NAME, from dba_indexes where INDEX_NAME='WRH$_ROWCACHE_SUMMARY_PK';

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
WRH$_ROWCACHE_SUMMARY WRH$_ROWCACHE_SUMMARY_PK
查询得到这张表是分区表
SQL> select * from dba_tables where table_name='WRH$_ROWCACHE_SUMMARY';
PARTITIONED
-----------
YES

查询分区索引，结果为空

SQL> SELECT index_name, partition_name, status

2 FROM dba_ind_partitions

3 WHERE index_name = 'WRH$_ROWCACHE_SUMMARY_PK';

因为这张表是系统表，于是查询了一下其他正常数据库服务器，
'WRH$_ROWCACHE_SUMMARY_PK 是分区本地索引。
正常数据库中，查询结果应该是：

SQL> SELECT index_name, partition_name, status

2 FROM dba_ind_partitions

3 WHERE index_name = 'WRH$_ROWCACHE_SUMMARY_PK';

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCACHE_SU_MXDB_MXSN USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30022 USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30046 USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30094 USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30070 USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30118 USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30166 USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30142 USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30190 USABLE

WRH$_ROWCACHE_SUMMARY_PK WRH$_ROWCAC_3109660417_30214 USABLE

于是再次查询有问题的数据库，索引信息。
SQL> select * from dba_indexes where INDEX_NAME='WRH$_ROWCACHE_SUMMARY_PK';
分区属性为NO，正常数据库为YES。

PARTITIONED NO

于是手工改为分区本地索引。
1、删除原有索引，在删除之前，先找到WRH$_ROWCACHE_SUMMARY 的DDL语句中的创建主键语句。
2、创建本地索引
查找DDL，找到创建主键语句

alter table WRH$_ROWCACHE_SUMMARY

add constraint WRH$_ROWCACHE_SUMMARY_PK primary key (DBID, SNAP_ID, INSTANCE_NUMBER, PARAMETER)

using index local

tablespace SYSTEM

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);
原本是没有红色local语句的，不加LOCAL 创建的索引是全局索引。
于是手工添加local语句，手工执行。
再次查询分区索引信息，一切都正常。

select * from dba_indexes where INDEX_NAME='WRH$_ROWCACHE_SUMMARY_PK';
PARTITIONED YES
STATUS N/A