In the previous post the preferred settings of heavily used SOA composites was demonstrated. It was shown that with the correct settings applied only failed component instances are persisted in the database. This post discusses the purging possibilities of such component instances.

As stated in the earlier post, we are now facing a situation where the composite instance itself is not persisted, only its failed component instances. Since the composite is heavily used, even a short outage of a system used by the composite can lead to a huge amount of failures. Having resolved these faults, the need often arises to clear these faults from the system so that fault monitoring is not disturbed by them.

Instances without faults are not persisted in this case because no information from the composite is needed, so a straightforward approach would be to delete the failed instances with Enterprise Manager. In this case, however, this is not possible because the composite instance itself is not persisted and instance purging in Enterprise Manager is based on the composite instance.

Another possibility is to use the database purging scripts supplied by Oracle. These scripts are automatically present in the SOAINFRA schema once the schema is created with the RCU utility. If purging is done with the help of these scripts, the failed orphaned component instances are deleted with the purging run. However, purging generally has fixed time slots and it might be difficult to bring that slot forward if the failed component instances are to be deleted sooner.

The functions used by the purging scripts, however, are also available on their own. A sample script to delete the failed orphaned components could therefore be constructed by using the functions present in the SOAINFRA schema. The same functions can also be found in the scripts which are automatically supplied with every SOA Suite and JDeveloper installation and can be found in the subdirectory rcu/integration/soainfra/oracle/soa_purge under the installation directory.


min_creation_date TIMESTAMP;
max_creation_date TIMESTAMP;
batch_size INTEGER;
retention_period TIMESTAMP;
composite_dn VARCHAR2(125);
soa_partition_name VARCHAR2(10);
composite_name VARCHAR2(100);
composite_revision VARCHAR2(10);
mediator_deleted BOOLEAN;
decision_deleted BOOLEAN;
bpel_deleted BOOLEAN;
fabric_deleted BOOLEAN;


min_creation_date := to_timestamp(‚2013-01-01′,’YYYY-MM-DD‘);
max_creation_date := to_timestamp(‚2013-03-31′,’YYYY-MM-DD‘);
batch_size := 20000;
retention_period := max_creation_date;
soa_partition_name := ‚PARTITION‘;
composite_name := ‚TestProcess‘;
composite_revision := ‚1.0‘;

IF soa_partition_name IS NOT NULL THEN
composite_dn := soa_partition_name;
IF composite_name IS NOT NULL THEN
composite_dn := composite_dn || ‚/‘ || composite_name;
IF composite_revision IS NOT NULL THEN
composite_dn := composite_dn || ‚!‘ || composite_revision;

bpel_deleted := soa_orabpel.deleteNoCompositeIdInstances(
p_min_creation_date => min_creation_date,
p_max_creation_date => max_creation_date,
p_older_than => retention_period,
p_rownum => batch_size,
soa_partition_name => soa_partition_name,
composite_name => composite_name,
composite_revision => composite_revision);

mediator_deleted := soa_mediator.deleteNoCompositeIdInstances(
p_min_creation_date => min_creation_date,
p_max_creation_date => max_creation_date,
p_older_than => retention_period,
p_rownum => batch_size,
composite_dn => composite_dn);

decision_deleted := soa_decision.deleteNoCompositeIdInstances(
p_min_creation_date => min_creation_date,
p_max_creation_date => max_creation_date,
p_older_than => retention_period,
p_rownum => batch_size,
composite_dn => composite_dn);

fabric_deleted := soa_fabric.deleteNoCompositeIdInstances(
min_created_date => min_creation_date,
max_created_date => max_creation_date,
retention_period => retention_period,
batch_size => batch_size,
composite_dn => composite_dn);

IF (bpel_deleted OR mediator_deleted OR decision_deleted OR fabric_deleted) THEN
dbms_output.put_line(‚Further instances may be present in the system, please rerun the script to delete these‘);
dbms_output.put_line(‚All orphaned instances deleted‘);


This script can be executed in the SOAINFRA schema on demand „“ even without starting the purging run „“ thereby assuring fault monitoring efficiency. Keep in mind that while the script is running in the database, performance of the application might be slightly lower because of increased database load. Therefore it is advisable to limit the run time of the script by narrowing the number of processed component instances „“ either by selecting the composite itself or if that is not possible selecting an appropriate batch size, thereby limiting the number of deleted component instances in one run.

Alle Beiträge von Attila Nemeth

Schreibe einen Kommentar