Remained Privileged Accounts after expire the requests with deadlock (ORACLE)

Products

• CA Virtual Privilege Manager
• CA Privileged Identity Management Endpoint (PIM)
• CA Privileged Access Manager (PAM)

Issue/Introduction

Customer met remained Privileged accounts problem after expired Privileged Accounts Requests. 

It may occur when many Privileged Account expires at same time.

 

Following error message In server.log and failed at DeletePrivilegedAccountExceptionEvent  

ERROR org.quartz.core.ErrorLogger] An error occurred while marking executed job complete. job= 'PPMSchedule.xxx_xx' 

org.quartz.JobPersistenceException: Couldn't remove job: ORA-00060: deadlock detected while waiting for resource 

[See nested exception: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource 

...

ERROR [com.netegrity.ims.exception.EventExecuteStateException] Execution of event: DeletePrivilegedAccountExceptionEvent failed.  Exception encountered: JobPersistenceException:Couldn't remove job: ORA-00060: deadlock detected while waiting for resource

 

Environment

OS: Windows 2012 Prod:CA Privileged Identity Manager r12.9 SP2 for SAM CDB: ORACLE 11g R2 User Store: ActiveDirectory 2012 R2

Cause

This problem is caused by SQL lock statement is for SQLServer in quartz.properties.

It should be changed for  ORACLE.

Resolution

1. Stop JBoss service.

2. Change directory to "C:\jboss-4.2.3.GA\server\default\deploy\IdentityMinder.ear\config"

3. Edit quartz.properties file as following :

Before: 

org.quartz.jobStore.SelectWithLockSQL = SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ? 

 

After: 

org.quartz.jobStore.SelectWithLockSQL = SELECT LOCK_NAME FROM {0}LOCKS WHERE LOCK_NAME = ? FOR UPDATE

 

4. Save file and close editior.

5. Backup server.log and boot.log if you need.

6. Delete all files under  following directory:

 C:\jboss-4.2.3.GA\server\default\deploy\tmp

 C:\jboss-4.2.3.GA\server\default\deploy\work

7. Restart JBoss service