ORA-01502: index 'SYS.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