1、检查alert日志发现错误
Wed Nov 30 13:36:47 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:48 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:48 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:49 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:50 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:51 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:52 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:52 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:53 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:54 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:55 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:56 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:56 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:57 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:57 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:36:58 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 13:37:12 2011 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable Wed Nov 30 22:00:09 2011 ………… Wed Nov 30 22:00:15 2011 GATHER_STATS_JOB encountered errors. Check the trace file. Wed Nov 30 22:00:15 2011 Errors in file /opt/oracle/admin/ora9i/bdump/ora9i_j001_21372.trc: ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state
2、查看trace文件
/opt/oracle/admin/ora9i/bdump/ora9i_j001_21372.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine: x86_64
Instance name: ora9i
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 21372, image: oracle@localhost.localdomain (J001)
*** 2011-11-30 22:00:15.782
*** ACTION NAME:(GATHER_STATS_JOB) 2011-11-30 22:00:15.763
*** MODULE NAME:(DBMS_SCHEDULER) 2011-11-30 22:00:15.763
*** SERVICE NAME:(SYS$USERS) 2011-11-30 22:00:15.763
*** SESSION ID:(532.60095) 2011-11-30 22:00:15.763
ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state
*** 2011-11-30 22:00:15.782
GATHER_STATS_JOB: GATHER_TABLE_STATS('"VAS"','"TAB_XN_CALLLOG_ANALYSIS"','""', ...)
ORA-20000: index "VAS"."XN_CALLLOG_ANALYSIS_PK" or partition of such index is in unusable state
3、日志初步结论
通过alert日志，感觉应该是对分区表操作，导致”VAS”.”XN_CALLLOG_ANALYSIS_PK”索引变成了unusable state，然后在数据库自动收集统计信息的时候报错(最大可能是全局index导致)
4、验证猜测是否正确
SQL> SELECT owner,index_name,table_name,status FROM DBA_indexes 2 WHERE index_name='XN_CALLLOG_ANALYSIS_PK' AND owner='VAS'; OWNER INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- VAS XN_CALLLOG_ANALYSIS_PK TAB_XN_CALLLOG_ANALYSIS UNUSABLE
5、解决相关问题问题
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' || INDEX_NAME ||
'REBUILD PARTITION ' || PARTITION_NAME || ' NOLOGGING online;'
FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
AND STATUS = 'UNUSABLE'
UNION ALL
SELECT 'alter index ' ||OWNER || '.' || A.INDEX_NAME || ' REBUILD online nologging;'
FROM DBA_INDEXES A
WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'PUBLIC')
AND STATUS = 'UNUSABLE';
执行生成sql，解决相关index unusable问题
