Using Out-of-the-box Purge Scripts In Oracle SOA 11.1.1.4

Purging the SOA Infra tables (aka dehyrdation store) is a very important task for SOA Suite administrators. In case you have production environments where transactional volume is high it can fill up your SOA Infra audit tables fast and if the data growth in tables is not controlled, it can lead to major performance issues or nightmares rather.

SOA 11.1.1.4 has 2 purging techniques available :
1. Either use the database partitioning concept where the SOA Infra tables are partitioned based on date range or other criteria and you can drop the partitions. This is faster way of doing and comes handy when you have to deal with huge volume of data. This however requires some advanced DBA skills to perform.

2. We also have some purge scripts available which can come handy. Oracle SOA Suite installations across versions have come with out-of-the-box purge scripts but most of these had performance issues. The 11.1.1.4(aka PS3) version of SOA Suite purge scripts have many performance improvements and are easy to use as well. This post explains the simple steps required to execute the purge scripts in your environment.

Step1: Connect to the DB with SQL*Plus as SYSDBA to grant privilages to the SOA Infra user (say DEV_SOAINFRA) that executes the scripts:

SQL> GRANT EXECUTE ON DBMS_LOCK TO DEV_SOAINFRA; 
SQL> GRANT CREATE ANY JOB TO DEV_SOAINFRA;

Step2: The Purge Scripts location is $RCU_HOME/rcu/integration/soainfra/sql/soa_purge/ Connect to the DB with SQL*Plus as the DEV_SOAINFRA user and load the scripts:

SQL> @soa_purge_scripts.sql

This should create some procedures, functions, types and packages under DEV_SOAINFRA schema.
Step3: Before running the purge check how many records there are to be purged using below SQL. Please note cube_instance is not the only table which gets purged, there are lot of child tables which get purged as well.

SQL> select state, count(*) from cube_instance group by state;

Step4: If you want to spool the PLSQL program's output to a log file then set serveroutput on. This would help you understand which are the tables getting purged and also what are the eligible records getting purged.

SQL> SET SERVEROUTPUT ON;
SQL> spool '/tmp/spool.log'

Then run the script mentioned in next step and once finished turn off the spooling.

SQL> spool off 

Step5: Please note there are 2 modes of running purge either loop purge or parallel purge. In loop purge it iterates through the set of eligible records and purges it. Parallel purge is similar to loop purge with additional flexibility of spawning parallel threads to do the purging (faster, multi threaded approach if dealing with huge number of records). Below is a sample of loop purge, for parallel purge the procedure name is delete_instance_in_parallel

SQL> DECLARE
max_creation_date timestamp;
min_creation_date timestamp;
retention_period timestamp;

BEGIN
min_creation_date := to_timestamp('2011-10-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2011-10-05','YYYY-MM-DD');
retention_period := to_timestamp('2011-10-05','YYYY-MM-DD');

soa.delete_instances(
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => 10000,
max_runtime => 60,
retention_period => retention_period,
purge_partitioned_component => false);

END;
/

You can then use the SQL in Step3 to check how many records were purged once the script completes and also open the spool.log to see the data purged from child tables.