Cleanup Submitted Tasks fails to move records to archive with ORA-00001 constraint violation

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

During the execution of the "Cleanup Submitted Tasks" task in Identity Manager, the process may stop or fail to archive specific records. This failure is often accompanied by an error in the View Submitted Tasks (VST) or system logs:

Error during CleanupSubmittedTasksEvent.longRunningEventExecute: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (IAMARCHIVE.PK_ARCH_RUNTIMESTATUSDETAIL12) violated

Symptoms include the cleanup task processing only a single record (or a very small number) and then aborting or appearing to succeed without actually reducing the size of the runtime tables.

 

Environment

• Product: Identity Manager, Identity Suite
• Releases: 14.5 (Standalone and Virtual Appliance)
• Database: Oracle (Primary), Microsoft SQL Server

Cause

The issue is caused by a data inconsistency where an ID in the runtime table (runtimestatusdetail12) already exists in the archive table (archive_runtimestatusdetail12). This typically occurs after:

1. A database migration or partial restore.
2. Database replicas falling out of synchronization.
3. Manual data manipulation in the archive database.

Resolution

1) DELETE FROM archive_runtimestatusdetail12 WHERE id = DuplicatedIDFound;

2) Rerun Cleanup Submitted Tasks