How to fix corrupt APM database indexes in an Oracle database. ORA-01502: index <index name> or partition of such index is in unusable state

Products

• APP PERF MANAGEMENT
• CA Application Performance Management Agent (APM / Wily / Introscope)
• CUSTOMER EXPERIENCE MANAGER
• INTROSCOPE

Issue/Introduction

Description:

Summary: This article explains how to address a corrupt APM database index issue in Oracle (with the error message 'index <index name>' or partitionof such index is in unusable state'.)

Solution:

Enterprise Manager logs for the Tim Collection or Stats Aggregation Service show that update or insert statements are failing with the error message:

• 'index <index name>' or partition of such index is in unusable state'.

The error occurs in an Oracle database and appears in the logs as:

[ERROR] [Thread-36][Manager.com.timestock.tess.services.processors.StatsAggregator]
[DailyStatsPool.Thread5] [com.timestock.tess.util.TessLogger] Insert/updates failed for table
com.timestock.tess.data.objects.StatsTranSetUserGroupDaily java.sql.SQLException: ORA-01502: index '<index name>' or partition of such index is in unusable state

Take the following steps to correct this error:

1. Make a DB backup. (Always a good practice before updating any entry in the APM database)
   
   
2. Stop the EM running the TCS and Stats Aggregation Service
   
   
3. On the database side, rebuild the affected index(es) using the command below as an example:
   
   
   • alter index <index name> rebuild;
     
     
   • For example, the index APM.SYS_C0098790 would be rebuilt using:
     
     
   • alter index APM.SYS_C0098790 rebuild;
     
     
4. Restart the EM running the TCS and Stats Aggregation Service.

Enterprise Manager logs should no longer contain this error. If errors persist, contact CA APM Support for further assistance.

Environment

Release:
Component: APMCEM