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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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; |