ETL Execution Failure: Resolving 'ORA-20001' and 'ORA-02437' Primary Key Violations

Products

• CA Identity Governance
• CA Identity Suite

Issue/Introduction

When running an ETL (Extract, Transform, Load) process in Identity Governance, the execution may fail with a sequence of nested Oracle exceptions in the logs:

Caused by: java.sql.SQLException: ORA-20001: ERROR WHILE EXECUTING THE I2_SP_DW2 STORED PROCEDURE
-20001 -ERROR- ORA-20001: ERROR WHILE EXECUTING THE I2_SP_CREATEDWCONSTRAINTS STORED PROCEDURE
-2437 -ERROR- ORA-02437: cannot validate (REPORT_DB.XPKI2_PENTITY_PENTITY_REL) - primary key violated

Environment

Identity Governance 14.x

Cause

The failure is typically caused by insufficient space in the Oracle Undo Tablespace.

When the tablespace is exhausted during large ETL transactions, the database cannot maintain read consistency or roll back changes, leading to misleading primary key validation errors during the execution of stored procedures like I2_SP_CREATEDWCONSTRAINTS.

Resolution

To resolve this issue, the Oracle database administrator (DBA) must increase the size or allow auto-extension of the undo tablespace. Follow these implementation steps:

1. Identify the current undo tablespace being used by the Identity Governance reporting schema.

2. Monitor the tablespace usage during the ETL execution to confirm it is reaching its maximum capacity.

3. Increase the data file size for the undo tablespace using the following SQL command (example):

   ALTER DATABASE DATAFILE '[path_to_datafile]' RESIZE [new_size];

4. Alternatively, enable auto-extension for the undo tablespace:

   ALTER DATABASE DATAFILE '[path_to_datafile]' AUTOEXTEND ON NEXT [size] MAXSIZE [max_size];

5. Once the tablespace has been increased, restart the ETL process from the Identity Governance management console.

Note: If the error persists after increasing the undo tablespace, verify that there are no actual duplicate records in the XPKI2_PENTITY_PENTITY_REL table that could be triggering a legitimate primary key violation.