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 [############-##########-#############] 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.

Additional Information

If IM taskpersistance and archive are using separate DB's you will need to switch the operation a bit:

This select will need to be split into two:

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

so:

select tasksessionid from archive_tasksession12_5;

save result to a file, move file to second database.

select tasksessionid from tasksession12_5 where tasksessionid in <file from previous step>;

this will give you list of duplicated tasks you can apply step 3 on to in taskpersistance database.