转载 Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable

http://www.xifenfei.com/2011/12/some-indexes-or-index-subpartitions-of-table-vas-tab_pub_calllog-have-been-marked-unusable.html

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: [email protected] (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问题