C2B2 logo icon

Purging data from Oracle SOA Suite 11g

In this post, we describe the steps involved in using the Oracle purge script to remove your unwanted data

Working with Oracle SOA Suite requires a great understanding of data storage, the purge options available to you - and how to implement the correct purging process for your specific needs. In this post, I'm going to take you through the specifics of SOA Suite 11g (PS6 11.1.1.7) data storage in manageable sections, and hopefully give you all the tools you need to undertake successful purges!

So, let's start with the fundamentals...

How does Oracle SOA Suite 11g (PS6 11.1.1.7) store data?

SOA Suite uses a database schema called SOAINFRA (a collection of database objects such as tables, views, procedures, functions etc.) to store data required for the running of SOA Suite applications. The SOAINFRA (SOA Infrastructure) schema is also referred to as the ‘dehydration store’ acting as the persistence layer for capturing SOA Suite data.

What data does Oracle SOA Suite 11g (PS6 11.1.1.7) store?

Composite instances utilising the SOA Suite Service Engines (BPEL, mediator, human task, rules, BPM, OSB, EDN etc.) will write data to tables residing within the SOAINFRA schema. Each of the engines will either write data to specific engine tables (e.g. the CUBE_INSTANCE table is used solely by the BPEL engine) or common tables that are shared by the SOA Suite engines such as the AUDIT_TRAIL table.

A few examples of the type of data that is stored within the SOAINFRA schema are listed below:

  • Message payload (e.g. input, output)
  • Scope (e.g. variables)
  • Auditing (e.g. data flow timestamps)
  • Faults
  • Deferred (messages that can be recovered)
  • Metrics
Why do you need to purge Oracle SOA Suite 11g (PS6 11.1.1.7) data?

Data within the Oracle SOA Suite database can grow to substantial levels in a short space of time. Payload sizes and volume of data will have an impact on available disk space which in turn will affect the performance of SOA Suite. For example, EM console can often become slow to navigate, increasing number of messages becoming stuck or requiring recovery, JTA transaction problems etc.

Purging itself, can become challenging if the data has not been maintained due to the large number of composite instances. Therefore, establishing a purge strategy and implementing it on a regular basis will help maintain the health of SOA Suite keeping the environment running efficiently.

What are the purging options available for Oracle SOA Suite 11g (PS6 11.1.1.7)?

Oracle provides three options for purging Oracle SOA Suite 11g data:

  1. EM Console: Within the Enterprise Manager console the ‘Delete with Options’ can be used to manually delete many instances at once however, this may lead to transaction timeouts and is not recommended for large volumes.
     
  2. Purge Script: This is the process of deleting instances that are no longer required using stored procedures that are provided with Oracle SOA Suite 11g out of the box.
     
  3. Partitioning: Instances are segregated based on user defined criteria within the database, when a partition is not required it will be dropped freeing the disk space.
Oracle SOA Suite 11g (PS6 11.1.1.7) purge script

So, the key question is...what data will be purged by the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script?

The purge script will delete composite instances that are in the following states:

  • Completed
  • Faulted
  • Terminated by user
  • Stale
  • Unknown

The purge script will NOT delete composite instances that are in the following states:

  • Running (in-flight)
  • Suspended
  • Pending Recovery

To complete this picture, here is a list of composite instance states that will be considered for purging by the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script:

How do I install the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script?

The following details will be required:

  • Database host details:
    - hostname (IP address)
    - username
    - password
  • SOA Database schema details:
    - prefix
    - password
  • Full path of the SOA Suite home folder
  • Full path of the directory where the Oracle purge script will write log information to (a folder on the database host) 

‘DEV' was the soainfra schema prefix used for the examples below.

a. Log into the Database host server.

b. Connect to the database as administrator using SQL*Plus:
sqlplus / as sysdba

Grant privileges to the soainfra (database) user that will be executing the scripts:

GRANT EXECUTE ON DBMS_LOCK TO _SOAINFRA;
GRANT CREATE JOB TO _SOAINFRA;
GRANT CREATE EXTERNAL JOB TO _SOAINFRA;

d. Exit SQL*Plus and go to the location of the Oracle purge script:

exit
$cd /rcu/integration/soainfra/sql/soa_purge/

e. Connect to the database as the soainfra user using SQL*Plus:

sqlplus _SOAINFRA/

@soa_purge_scripts.sql

Procedure created.
Function created.
Type created.
Type body created.
PL/SQL procedure successfully completed.
Package created.
Package body created.

f. Exit SQL*Plus and create a directory where the log files (generated by the Oracle purge script) should be written to:

exit
$mkdir -p /PurgeLogs

g. Connect to the database with SQL*Plus as SYSDBA and declare the directory:

sqlplus / as sysdba

CREATE OR REPLACE DIRECTORY SOA_PURGE_DIR AS '/PurgeLogs';

GRANT READ, WRITE ON DIRECTORY SOA_PURGE_DIR TO _SOAINFRA;

All the database objects required for purging data using the Oracle purge script are now loaded into the SOAINFRA schema ready for use.

How do I execute the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script?

There are two options for running the purge script:

  1. Looped
    Looped purge is a single threaded PL/SQL script that will iterate through the SOAINFRA tables and delete instances matching the parameters specified.
     
  2. Parallel
    Parallel purge is essentially the same as the looped purge. It is meant to be more efficient as it uses the dbms_scheduler package to spawn multiple purge jobs all working on a distinct subset of data. There are 2 more parameters that can be specified in addition to the ones used by the looped purge. This is designed to purge large data volumes hosted on high-end database nodes with multiple CPUs and a good IO sub-system. A maintenance window should be used as it requires a lot of resources.
 
Example 1: Executing the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script to purge data for all composites

We are required to delete all composite instances which were created between 1st June 2010 and 30th June 2010. In addition, there is a requirement not to delete instances that have been modified after 30th June 2010. The script must finish running after an hour due to business hours resuming shortly afterwards.

  • min_creation_date = 1st June 2010
  • max_creation_date = 30 June 2010
  • retention_period = 1st July 2010
The above will in effect delete all "composite instances" where the created time of the instance is between 1st June 2010 and 30 June 2010 and the modified date of the BPEL instances is less than 1st July 2010.

Looped Execution:
DECLARE

max_creation_date timestamp;
min_creation_date timestamp;
batch_size integer;
max_runtime integer;
retention_period timestamp;

BEGIN
min_creation_date := to_timestamp('2010-06-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-06-30','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2010-07-01','YYYY-MM-DD');
batch_size := 10000;

soa.delete_instances(
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period);

END;
/

Parallel Execution

DECLARE

max_creation_date timestamp;
min_creation_date timestamp;
batch_size integer;
max_runtime integer;
retention_period timestamp;
DOP integer;
max_count integer;
purge_partitioned_component boolean;

BEGIN
min_creation_date := to_timestamp('2010-06-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-06-30','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2010-07-01','YYYY-MM-DD');
batch_size := 10000;
DOP := 3;
max_count := 1000000;
purge_partitioned_component := false);


soa.delete_instances_in_parallel (
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
DOP => DOP,
max_count => max_count,
purge_partitioned_component => purge_partitioned_component);

END;
/

 

Example 2: Executing the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script to purge data from a specific composite

This is essentially the same conditions as the Example 1 scenario, but with an additional requirement of only purging data from the composite named OrderBookingComposite. No other composite data should be purged.

Composite details can be gathered by querying the COMPOSITE_INSTANCE table within the SOAINFRA schema. The column named COMPOSITE_DN (distinguished name) holds the details required by the purge script:

Format: <soa_partition name>/<composite name>!<composite_revision>
Example: default/OrderBookingComposite!1.0

Looped Execution:

DECLARE
min_creation_date timestamp;
max_creation_date timestamp;
batch_size number;
max_runtime number;
retention_period timestamp;
purge_partitioned_component boolean;
composite_name varchar2(200);
composite_revision varchar2(200);
soa_partition_name varchar2(200);

BEGIN
min_creation_date := to_timestamp('2010-06-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-06-30','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2010-07-01','YYYY-MM-DD');
batch_size := 10000;
purge_partitioned_component := true;
composite_name := 'OrderBookingComposite';
composite_revision := '1.0';
soa_partition_name := 'default';


soa.delete_instances(
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
purge_partitioned_component => purge_partitioned_component,
composite_name => composite_name,
composite_revision => composite_revision,
soa_partition_name => soa_partition_name);

END;
/

Parallel Execution

DECLARE
min_creation_date timestamp;
max_creation_date timestamp;
batch_size number;
max_runtime number;
retention_period timestamp;
DOP integer;
max_count integer;
purge_partitioned_component boolean;
composite_name varchar2(200);
composite_revision varchar2(200);
soa_partition_name varchar2(200);

BEGIN
min_creation_date := to_timestamp('2010-06-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-06-30','YYYY-MM-DD');
max_runtime := 60;
retention_period := to_timestamp('2010-07-01','YYYY-MM-DD');
batch_size := 10000;
DOP := 3
max_count := 1000000;
purge_partitioned_component := true;
composite_name := 'OrderBookingComposite';
composite_revision := '1.0';
soa_partition_name := 'default';


soa.delete_instances(
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => batch_size,
max_runtime => max_runtime,
retention_period => retention_period,
DOP => DOP,
max_count => max_count,
purge_partitioned_component => purge_partitioned_component,
composite_name => composite_name,
soa_partition_name => soa_partition_name);

END;
/

Conclusion

This post has provided a basic understanding of the purge script contained within Oracle SOA Suite 11g (PS6 11.1.1.7). A long-term purging strategy needs to be implemented, and in order to do so, a good understanding of the workings of the purge script is required - along with an awareness of the issues related to the script - and I hope I've helped you on your way to bringing that strategy closer to fruition.