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'.)
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:
[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:
- Make a DB backup. (Always a good practice before updating any entry in the APM database)
- Stop the EM running the TCS and Stats Aggregation Service
- 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;
- 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.