Crowd does not synchronise with AD because of Corrupt/Unusable Indexes
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Crowd Synchronization fails with AD due to unusable Indexes in ORACLE DB. This issue impacts both Server and DC and resolution is for both.
Diagnosis
When Crowd is connected with Oracle DB, synchronisation with AD can fail due to corrupt or Indexes in Unusable state in DB. We will get logs similar to the ones below when the sync will fail.
1
2
3
4
5
6
2020-06-18 11:58:13,724 Caesium-2-2 ERROR [jdbc.batch.internal.BatchingBatch] HHH000315: Exception executing batch [java.sql.BatchUpdateException: ORA-01502: index 'ATL_CROWD2.SYS_C0017360' or partition of such index is in unusable state
], SQL: insert into cwd_synchronisation_status (directory_id, node_id, sync_start, sync_end, sync_status, status_parameters, id) values (?, ?, ?, ?, ?, ?, ?)
2020-06-18 11:58:13,725 Caesium-2-2 WARN [engine.jdbc.spi.SqlExceptionHelper] SQL Error: 1502, SQLState: 72000
2020-06-18 11:58:13,725 Caesium-2-2 ERROR [engine.jdbc.spi.SqlExceptionHelper] ORA-01502: index 'ATL_CROWD2.SYS_C0017360' or partition of such index is in unusable state2020-06-18 11:58:13,725 Caesium-2-2 ERROR [org.hibernate.internal.ExceptionMapperStandardImpl] HHH000346: Error during managed flush [org.hibernate.exception.GenericJDBCException: could not execute batch]
2020-06-18 11:58:13,725 Caesium-2-2 ERROR [atlassian.crowd.directory.DbCachingDirectoryPoller] Error occurred while refreshing the cache for directory [ 458754 ].
org.springframework.orm.hibernate5.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [insert into cwd_synchronisation_status (directory_id, node_id, sync_start, sync_end, sync_status, status_parameters, id) values (?, ?, ?, ?, ?, ?, ?)]; SQL state [72000]; error code [1502]; could not execute batch; nested exception is org.hibernate.exception.GenericJDBCException: could not execute batchCause
Checking more on this error, it seems to be related to the index being in an unusable state as per ORA-01502: index or partition of such index is in usable state tips
Solution
Please follow below steps to resolve this issue.
You will find something like below in the crowd application logs.
1ORA-01502: index '<DIRECTORY-INDEX>' or partition of such index is in unusable state
Connect to your database, e.g. with Oracle SQL Developer and search for the index <DIRECTORY-INDEX>
You will see, that STATUS has the value UNUSABLE. Rebuild your index.
STATUS should now be switched to VALID and synchronization of your directory should work again.You have to rebuild the index in your database for each directory that is affected.
Simulating the above steps for the indexes in the logs shared above, we will follow below steps.
In the logs, as we found the error as ORA-01502: index 'ATL_CROWD2.SYS_C0017360' or partition of such index is in unusable state, we have to rebuild this.
Connect to your database, e.g. with Oracle SQL Developer and search for the index ATL_CROWD2.SYS_C0017360
You will see, that STATUS has the value UNUSABLE. Rebuild your index.
STATUS should now be switched to VALID and synchronisation of your directory should work again.
Was this helpful?