CleanUp IM submitted task doesn't work with error "ORA-06512: in line 1"

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

An issue has been identified when running the CA Identity Manager cleanup submitted task process.

The process appears to work (scheduled every day) however every couple of weeks the following error is logged:

Cleanup Submitted Tasks task: Primary Event. Event ID [90f13f76-7ffefd47-5ce0f5bf-1dc96b49] Task "Cleanup Submitted Tasks": Failed to execute CleanupSubmittedTasksEvent. ERROR MESSAGE: ORA-00001: restricción única (IMOBJSTORE.PK_ARCH_TASKSESSION12_5) violada ORA-06512: en "IMOBJSTORE.ARCHIVEBYTASKID", línea 34 ORA-06512: en "IMOBJSTORE.ARCHIVETASKS", línea 44 ORA-06512: en "IMOBJSTORE.ARCHIVETASKPERSISTENCE", línea 15 ORA-06512: en línea 1

What is the cause of this issue?

Environment

Identity Manager 14.x

Resolution

This issue can occur in normal operations and may require a manual cleanup of duplicate items.  Please refer to the following Knowledge Article.

 

Clean up Submit Tasks failure, "ORA-00060: deadlock detected while waiting for resource"

 

However, in this case, the issue was caused by old stored procedures ("GarbageCollectTasks" and "archiveTasks" procedures) which needed to be updated as below. 

 

Old Line:

tasksession12_5.state = 16384 -- audit state
     AND last_access_time < 
      LEAST(cutoff_time, SYSDATE - audit_timeout_days)

 

New : 

tasksession12_5.state = 16384 -- audit state
     AND parent_of_wf_id IS NULL
     AND last_access_time < 
      LEAST(cutoff_time, SYSDATE - audit_timeout_days)

 

After making the changes the issue was resolved.