Clean up Submit Tasks failure

Products

CA Identity Manager CA Identity Governance CA Identity Portal CA Identity Suite

Issue/Introduction

The following error is seen in the logs for the CleanupSubmittedTasksEvent.

Cleanup Submitted Tasks task: Primary Event. Event ID [e5da8224-0a3b0489-273f8703-891dca9b] Task "Cleanup Submitted Tasks": Failed to execute CleanupSubmittedTasksEvent.
ERROR MESSAGE:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "IAPP_IMSTORE_MGR.ARCHIVECOMPLETEDEVENTSBYID", line 15
ORA-06512: at "IAPP_IMSTORE_MGR.ARCHIVECOMPLETEDTASKSBYID", line 33
ORA-06512: at "IAPP_IMSTORE_MGR.ARCHIVECOMPLETEDTASKS", line 28
ORA-06512: at "IAPP_IMSTORE_MGR.ARCHIVETASKPERSISTENCE", line 15
ORA-06512: at line 1

23:58:31,355 ERROR [ims.tmt.persistence.cleanup] (Thread-12114) cleanupTaskPersistence >> ORA-00001: unique constraint (IAPS_IMSTORE_MGR.PK_ARCH_TASKSESSION12_5) violated
ORA-06512: at "IAPS_IMSTORE_MGR.ARCHIVEBYTASKID", line 34
ORA-06512: at "IAPS_IMSTORE_MGR.ARCHIVETASKS", line 44
ORA-06512: at "IAPS_IMSTORE_MGR.ARCHIVETASKPERSISTENCE", line 19
ORA-06512: at line 1

: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (IAPS_IMSTORE_MGR.PK_ARCH_TASKSESSION12_5) violated
ORA-06512: at "IAPS_IMSTORE_MGR.ARCHIVEBYTASKID", line 34
ORA-06512: at "IAPS_IMSTORE_MGR.ARCHIVETASKS", line 44
ORA-06512: at "IAPS_IMSTORE_MGR.ARCHIVETASKPERSISTENCE", line 19
ORA-06512: at line 1

Environment

Release : 14.1

Component : CA IDENTITY SUITE (VIRTUAL APPLIANCE)

Resolution

The following error is in the logs:  

ORA-00001: unique constraint 

The below steps will help identify and verify the tasksessionid that are not unique.

 
--------------------------------------- 
1. Run the command below to determine the duplicated tasksessionid. 

select tasksessionid from tasksession12_5 where tasksessionid in (select tasksessionid from archive_tasksession12_5); 

2. It will retrieve some data, For example:  
TASKSESSIONID ---EXAMPLE
-------------------------------------------------- 
46a0d232-61ea1eac-32bcd92d-10ae0e 
1386cef2-251dbf07-67255e78-6e7422 
11dbf7cb-e6b5298b-7c5b3412-73ca6b0 
1bd00ef8-9cc45a75-b2a6cdb7-4c5ff5 

3. Run the store procedure below for EACH value retrieved in the step #2

(you will need to change the tasksessionid with you own value)

execute garbageCollectByTaskId ('46a0d232-61ea1eac-32bcd92d-10ae0e'); 

4. You can run the first command again to check if it was deleted

select tasksessionid from tasksession12_5 where tasksessionid in (select tasksessionid from archive_tasksession12_5); 

TASKSESSIONID --EXAMPLE(1st dublicate was removed)
-------------------------------------------------- 
1386cef2-251dbf07-67255e78-6e7422 
11dbf7cb-e6b5298b-7c5b3412-73ca6b0 
1bd00ef8-9cc45a75-b2a6cdb7-4c5ff5 

5. After running the store procedure #3 for  each tasksessionid, run a commit 

6. After these steps you can run the Cleanup Submitted Task to archive your data.