000038393 - Change requests get randomly cancelled and add unrelated activities or continue to provision in RSA Identity Governance & Lifecycle

• High CPU usage
• Workflows stop progressing
• Change requests stop progressing
• There may be database performance issues due to the rapid growth of Workpoint tables such as WP_PROCI.

• Oracle alert log (/u01/app/oracle/diag/rdbms/avdb/AVDB/trace/alert_AVDB.log): (rapid database growth)

ORA-1653: unable to extend table AVUSER.WP_PROCI by 8192 in tablespace DATA_1M

• aveksaServer.log file ($AVEKSA_HOME/wildfly/standalone/log/aveksaServer.log): (heavy workload)

0-01-24 10:09:03,430 [Worker_jobq#Normal#WPDS_30718] 
ERROR com.workpoint.services.impl.GenericServiceImpl - Numeric Overflow

• WorkPoint.log file ($AVEKSA_HOME/wildfly/standalone/log/WorkPoint.log):

2020-01-21 10:29:13,403 [Worker_jobq#Normal#WPDS_8] 
ERROR com.workpoint.server.monitor.JobMonitorHelper  - 
doCreateJobs() failed creating Split Node Job for Split ID 671879 in node 136338560:WPDS 
in parent Job 6251271:WPDS
java.sql.SQLException: BeanFactory not initialized or already closed - 
call 'refresh' before accessing beans via the ApplicationContext

• RSA Identity Governance & Lifecycle 7.1.1 P04
• RSA Identity Governance & Lifecycle 7.2.0

Customers who have encountered this issue should be strongly encouraged to patch to a version where this is resolved. Even if a particular event self resolves the severity of future events cannot be predicted.

NOTE: Until you are able to upgrade, you can use the tools provided in RSA Knowledge Base Article 000038546 -- Queries to detect workflows and change requests that could overload the Workpoint Server in RSA Identity Governance & Lifecycle to monitor the system for change requests or workflows that may potentially cause this issue. 

1. Detect and terminate the aberrant change request and/or workflow.
2. Cleanup the Workpoint monitoring queues. Be careful not to cancel any valid change requests and work that is in the Workpoint queues.

As avuser run the following SQL detection scripts:

• Script 1:  Execute this script three times replacing wp_[JOB or ALERT or SCRIPT] with wp_JOB_monitor, wp_ALERT_monitor, and wp_SCRIPT_monitor for each run.

select * from 
(select * from (Select wpi.cr_id, wpi.name, wpi.proc_state_id, job.* from wp_proci wpi join
(select proci_id, count(proci_id) as NUM_OF_WORK_IN_Q from wp_[JOB or ALERT or SCRIPT]_monitor 
group by proci_id 
having count(proci_id) > 5
order by count(proci_id)) job
on wpi.proci_id = job.proci_id) 
order by NUM_OF_WORK_IN_Q desc) LIVE
JOIN 
(select * from 
(with tbleA as (
select
PS.PROCI_ID,
ji.cri_id
from avuser.WP_PROCI_SPLIT ps
join avuser.T_AV_WFJOB_ITEMS ji 
on PS.PROCI_NODE_ID = JI.NODE_ID and JI.NODE_DB = PS.PROCI_NODE_DB and JI.SPLIT_ID = PS.SPLIT_ID
)
select distinct
cr.id,
cr.name,
cr.request_date,
cr.completed_date
from tbleA tmp
join AVUSER.WP_PROCI pi on tmp.PROCI_ID = pi.PROCI_ID
join AVUSER.PV_CHANGE_REQUEST_DETAIL crd on tmp.cri_id = crd.id
join AVUSER.PV_CHANGE_REQUEST cr on pi.cr_id = cr.id
join AVUSER.PV_CHANGE_REQUEST cr1 on CRD.CHANGE_REQUEST_ID = cr1.id
where PI.CR_ID != CRD.CHANGE_REQUEST_ID)) BAD
on LIVE.CR_ID = BAD.ID

• Script 2:

select cr.* from t_av_change_requests cr
where exists 
(select 1 from t_av_change_request_details crd 
where cr.id = crd.change_requests_id and crd.state = 'CA')
and not exists 
(select 1 from t_av_change_request_details crd 
where cr.id = crd.change_requests_id and crd.operand_type != 'RF' and crd.state != 'CA')
and cr.current_state = 'PV';

• You must immediately identify the change request that is affected and complete the cancellation of that request. Until the change request is fully cancelled the system may continue to degrade. It is important to contact RSA Identity Governance & Lifecycle Support as soon as possible to obtain a script to remediate the problem request(s) and get assistance on cleaning up the Workpoint monitor queues.
• Consider restoring to a known good database backup from a time before the issue occurred.