ORA-01502 Error Index or partition of such index is in unusable state - Clarity

Products

• Clarity PPM On Premise

Issue/Introduction

We are unable to navigate through Clarity pages or run any jobs and processes. The error on the log files refers to a problem with an index

Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]
ORA-01502: index 'NIKU.CMN_SEC_ASSGND_OBJ_PERM_PK' or partition of such index is in unusable state
ORA-06512: at "NIKU.CMN_SEC_ASSIGN_INST_RIGHT_SP", line 76
ORA-06512: at line 1

This error can also occur during an upgrade such as:

9/09/22 4:38 AM (ExecTask) To URL: jdbc:oracle:thin:@server:1521/servicenameexample
9/09/22 4:41 AM (ExecTask) java.sql.BatchUpdateException: error occurred during batching: ORA-01502: index 'CLARITY.CMN_SCH_JOB_RUN_PARAMS_PK' or partition of such index is in unusable state

Cause

This is an Oracle DB issue:

An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation

Resolution

1. Make a DB backup
2. Stop the Clarity services
3. On the database side, rebuild the affected index(es) using the command below as an example:
   alter index <index name> rebuild
In the above case: alter index CMN_SEC_ASSGND_OBJ_PERM_PK rebuild
4. Restart the Clarity services

Rebuilding the affected index might complain about the following index and so on.

If you find yourself in this scenario, you might need to rebuild all indices.

Additional Information

ORA-01502: index or partition of such index is in usable state problem