How to monitor Oracle SOA BPEL purge?

Implementing a working Purge script to get rid of old Oracle SOA BPEL instances is one of the most important tasks to maintain a working Oracle SOA Suite environment in production. Forgetting to do so will have the whole environment slow down as Oracle SOA Bpel database get bigger.

Depending on your business requirement, I usually advise purging instances data older than 2 months. Instances data are useful to troubleshoot issues with BPEL processes and shouldn’t be kept once those are successfully completed.

You may also need to update failed instances status to force them in a purgeable state. Especially for unrecoverable instances.(See at the bottom of the gist)

SQL queries to troubleshoot and monitor your Oracle SOA Suite BPEL purge scripts:

--Change this variable for your purge date value. In this case the Bpel purge purge all instance older than &purgeDays days
DECLARE purgeDays number := 72;
--TroubleShooting BPEL Purge
--Not purged instances
SELECT
ci.ecid AS ecid,
decode(ci.state,0, 'STATE_INITIATED(NOK)',1,'STATE_OPEN_RUNNING(OK)',2,'STATE_OPEN_SUSPENDED(OK)',3,'STATE_OPEN_FAULTED(OK)',4,'STATE_CLOSED_PENDING_CANCEL(OK)',5,'STATE_CLOSED_COMPLETED(NOK)',6,'STATE_CLOSED_FAULTED(NOK)', 7, 'STATE_CLOSED_CANCELLED(NOK)', 8, 'STATE_CLOSED_ABORTED(NOK)', 9, 'STATE_CLOSED_STALE(NOK)', 10, 'STATE_CLOSED_ROLLED_BACK(NOK)') AS state,
ci.composite_name AS composite_name,
ci.composite_revision AS composite_revision,
TO_CHAR(ci.creation_date at TIME ZONE(tz_offset('Europe/Paris')), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS creation_time,
TO_CHAR(ci.modify_date at TIME ZONE(tz_offset('Europe/Paris')), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS modify_date
FROM
YOUR_SOA_USER.cube_instance ci
where
ci.modify_date <= (sysdate-&purgeDays)
order by ci.modify_date desc;
--not purged DLVMessage by types
SELECT
dv.ecid AS ecid,
dv.composite_name AS composite_name,
dv.cikey,
decode(dv.state,0,'STATE_UNRESOLVED',1,'STATE_RESOLVED',2,'STATE_HANDLED',3,'STATE_CANCELLED',4,'STATE_MAX_RECOVERED') AS dlv_state,
decode(dv.dlv_type,1,'Invoke',2,'Callback') AS dlv_type
from
YOUR_SOA_USER.dlv_message dv
where dv.receive_date <= (sysdate-&purgeDays)
group by dv.ecid, dv.composite_name, dv.cikey, dv.state, dv.dlv_type;
-- count not purged DLVMessages
select
count(dv.ecid)
from
YOUR_SOA_USER.dlv_message dv
where dv.receive_date <= (sysdate-&purgeDays);
--
select count (task.ecid)
from YOUR_SOA_USER.wftask task
where task.state IS NOT NULL AND
task.state NOT IN ('DELETED','ERRORED','EXPIRED','STALE','WITHDRAWN');
SELECT unique mi.ECID from YOUR_SOA_USER.MEDIATOR_INSTANCE mi
where mi.component_state between 4 and 15;
SELECT dlv.ECID from YOUR_SOA_USER.DLV_MESSAGE dlv
WHERE dlv.dlv_type=1 and dlv.state in (0,1);
select unique ecid, state from YOUR_SOA_USER.composite_instance
where (bitand(state,127)=1 or bitand(state,6)=2 or bitand(state,16)=16 or
bitand(state,64)=64 or bitand(state,127)=32);
--Recoverable message not purgeable (6)
select *
from YOUR_SOA_USER.dlv_message dlv
inner join YOUR_SOA_USER.cube_instance ci on ci.ecid = dlv.ecid
where ci.state < 5
and ci.creation_date <= (sysdate-&purgeDays)
and ci.ecid=dlv.ecid
and dlv.dlv_type=1
AND dlv.STATE in (0,1);
--purgeable instance 394 059
select count(ci.ecid) from YOUR_SOA_USER.cube_instance ci
where ci.state >= 5
and ci.MODIFY_DATE <= (sysdate-&purgeDays);
-- Running cube instance records, NOT eligible for purging: 266 828
select count(ci.ecid)
from YOUR_SOA_USER.cube_instance ci
where ci.state < 5
and ci.MODIFY_DATE <= (sysdate-&purgeDays);
--not purged DLVMessage by types
SELECT
dv.composite_name AS composite_name,
decode(dv.state,0,'STATE_UNRESOLVED',1,'STATE_RESOLVED',2,'STATE_HANDLED',3,'STATE_CANCELLED',4,'STATE_MAX_RECOVERED') AS dlv_state,
decode(dv.dlv_type,1,'Invoke',2,'Callback') AS dlv_type
from
YOUR_SOA_USER.dlv_message dv
where dv.receive_date <= (sysdate-&purgeDays)
group by dv.composite_name, dv.state, dv.dlv_type;
select
count(dv.ecid)
from
YOUR_SOA_USER.dlv_message dv
where dv.receive_date <= (trunc(sysdate)-&purgeDays);
--All Older instances
select count(ci.ecid) from YOUR_SOA_USER.cube_instance ci
where ci.modify_date <= (trunc(sysdate)-&purgeDays);
--purgeable instance
select count(ci.ecid) from YOUR_SOA_USER.cube_instance ci
where ci.state >= 5
and ci.MODIFY_DATE <= (trunc(sysdate)-&purgeDays);
-- Running cube instance records, NOT eligible for purging:
select count(ci.ecid)
from YOUR_SOA_USER.cube_instance ci
where ci.state < 5
and ci.MODIFY_DATE <= (trunc(sysdate)-&purgeDays);
--If you need to force instance to a purgeable state here is how
--Force all old instance(>&purgeDays) to a purgeable state
update YOUR_SOA_USER.composite_instance set state=16 where created_time < trunc(sysdate)-&purgeDays;
update YOUR_SOA_USER.work_item set state = 10, modify_date = sysdate where modify_date < trunc(sysdate)-&purgeDays;
update YOUR_SOA_USER.cube_instance ci set ci.state = 8 ,
ci.modify_date = sysdate - 70 where ci.creation_date < trunc(sysdate)-&purgeDays;
update YOUR_SOA_USER.dlv_message set state = 3 where receive_date < trunc(sysdate)-&purgeDays;
update YOUR_SOA_USER.dlv_subscription set state = -1 where subscription_date < trunc(sysdate)-&purgeDays;
COMMIT;
--Delete unpurgeable element
delete from YOUR_SOA_USER.xml_document xd where doc_partition_date < trunc(sysdate)-&purgeDays;
delete from YOUR_SOA_USER.instance_payload ip where created_time < trunc(sysdate)-&purgeDays;
delete from YOUR_SOA_USER.headers_properties hp where modify_date < trunc(sysdate)-&purgeDays;
delete from YOUR_SOA_USER.document_dlv_msg_ref ddmr where dlv_partition_date < trunc(sysdate)-&purgeDays;
delete from YOUR_SOA_USER.dlv_message dm where receive_date < trunc(sysdate)-&purgeDays;
COMMIT;

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.