Identity Manager Cleanup Task Fails with ORA-00001 Unique Constraint Violation

Products

• CA Identity Suite

Issue/Introduction

The automatic "Cleanup Submitted Tasks" task in Identity Manager fails repeatedly with a database error. The failure is typically observed when the "Cleanup via Stored Procedure" option is enabled.

Error Message: ORA-00001: unique constraint (TEST.PK_ARCH_RUNTIMESTATUSDETAIL12) violated
ORA-06512: at "TEST.ARCHIVEBYID", line 13
ORA-06512: at "TEST.ARCHIVEBYTASKID", line 67
ORA-06512: at "TEST.ARCHIVETASKS", line 52
ORA-06512: at "TEST.ARCHIVETASKPERSISTENCE", line 13

Environment

Release : 14.5.1
Component : Identity Manager

Cause

This issue is caused by a data consistency problem between the runtime and archive database tables. Specifically, an orphaned record scenario can occur when parent records have already been archived, while related child records remain in the runtime tables.

During execution of the cleanup stored procedure, the system attempts to archive these remaining child records. If records with the same identifiers already exist in the corresponding archive tables, the operation can fail with a primary key constraint violation. This condition is typically caused by overlapping data between the runtime and archive tables, resulting from previous archival activities, data migrations, or manual database operations.

In the reported scenario, many duplicate record IDs were identified between the runtime table (RUNTIMESTATUSDETAIL12) and the archive table (ARCHIVE_RUNTIMESTATUSDETAIL12). The runtime records were recent, while the corresponding archive records originated from historical data.

Resolution

Identify duplicate records that exist in both the runtime and archive tables and determine whether the archive records are historical data that can be safely removed.

The following query can be used to identify duplicate records in the archive table that also exist in the runtime table:

SELECT COUNT(*)
FROM TEST.archive_runtimestatusdetail12 rsd
WHERE rsd.ORIGINATIONTIME < TO_DATE('06-07-2015', 'dd-mm-yyyy')
  AND EXISTS (
      SELECT 1
      FROM TEST.runtimestatusdetail12 e
      WHERE e.id = rsd.id
  );

After validating that the affected archive records are no longer required, remove the duplicate archive entries:

DELETE FROM TEST.archive_runtimestatusdetail12 rsd
WHERE rsd.ORIGINATIONTIME < TO_DATE('06-07-2015', 'dd-mm-yyyy')
  AND EXISTS (
      SELECT 1
      FROM TEST.runtimestatusdetail12 e
      WHERE e.id = rsd.id
  );

After removing the duplicate archive records, rerun the cleanup process. The cleanup task should complete successfully without encountering primary key violations.