oracle数据库some,【案例】Oracle报错Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG...

天萃荷净

运维DBA在巡检数据库时发现alert日志文件中报错Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG have been marked unusable

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、Ora错误初步结论

通过alert日志，感觉应该是对分区表操作，导致”VAS”.”XN_CALLLOG_ANALYSIS_PK”索引变成了unusable state，然后在数据库自动收集统计信息的时候报错(最大可能是全局index导致)

4、分析验证Ora错误

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、解决Ora错误

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问题

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错Some indexes or index [sub]partitions of table VAS.TAB_PUB_CALLLOG