In this recipe, taken from the book Oracle SOA Suite 11g Performance Tuning Cookbook (ISBN: 9781849688840, Packt Publishing) we will remove old BPEL dehydration data and state from the SOA infrastructure database.
You will need to have access to the database on which the SOA_INFRA schema is hosted. In this recipe we’ll be using a command line local to the host on which we installed the database.
You’ll also need access to the SQL scripts bundled with SOA Suite, if you have SOA Suite installed on the host running the database you can find them under: MW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge
If the database is running on a separate host then you can simple copy the soa_purge directory from the WebLogic Admin server to a directory on the database host, we’ll be using e:\soa_purge for this purpose.
How to do it...
Follow the steps below to run the soa_purge scripts
1. First log into sqlplus as a user with sysdba privileges, and grant the following permissions to the dev_soainfra user then exit the shell
sqlplus / as sysdba SQL> GRANT EXECUTE ON DBMS_LOCK TO DEV_SOAINFRA; SQL> GRANT CREATE ANY JOB TO DEV_SOAINFRA; SQL> exit
2. In the command line it’s easiest to cd to the location of the SOA Suite purge_sql folder, so do that now and then log in as the dev_soainfra user
cd e:\soa_purge SQL> sqlplus DEV_SOAINFRA/your_soainfra_password
3. Now execute the SQL script, this will load generate lots of output. If successful this will end with ‘procedure completed successfully’.
SQL> @soa_purge_scripts.sql ... Procedure created. Type created. Type body created. PL/SQL procedure successfully completed.
4. Enter the following, substituting the creation dates as appropriate
SQL> DECLARE 2 3 max_creation_date timestamp; 4 min_creation_date timestamp; 5 retention_period timestamp; 6 BEGIN 7 8 min_creation_date := to_timestamp('2012-10-01','YYYY-MM-DD'); 9 max_creation_date := to_timestamp('2013-01-30','YYYY-MM-DD'); 10 retention_period := to_timestamp('2010-02-01','YYYY-MM-DD'); 11 12 soa.delete_instances_in_parallel( 13 min_creation_date => min_creation_date, 14 max_creation_date => max_creation_date, 15 batch_size => 10000, 16 max_runtime => 60, 17 retention_period => retention_period, 18 DOP => 3, 19 max_count => 1000000, 20 purge_partitioned_component => false); 21 22 END; 23 / PL/SQL procedure successfully completed.
How it works...
SOA Suite comes bundled with maintenance scripts that allow us to perform housekeeping on the underlying database tables. As these tables are used for each invocation, dehydration and audit task over time they can fill up to sizes which hamper database performance.
In this recipe we copied the SOA Suite SQL procedures within the WebLogic Admin server referenced by MW_HOME/SOA_ORACLE_HOME (on our host this was E:\Oracle\Oracle_SOA1) to the database host containing the SOA schema. We granted suitable privileges to the dev_soainfra user to allow the user to run the task, and then set some parameters for the scripts. We set a start and end date for composite creation min_creation_date, max_creation_date and set a cutoff date for the last modification time of a composite (retention_period). Any composite that was created in the min/max date bounds, and was last modified before the retention_period will be deleted by this script invocation.
We instantiated the delete instances procedure (soa.delete_instances_in_parallel) that runs in parallel, this will speed up the deletion of composite data. The DOPS parameter determines how many parallel invocations will be started. A looping script is also available.
A number of parameters exist, we recommend setting the max_runtime to prevent the script hanging when running, or from users thinking a long running process has hung. The full list of parameters is available here: http://docs.oracle.com/cd/E29505_01/admin.1111/e10226/soaadmin_partition.htm
Out of the box, there are limitations on the purge scripts such as not purging the LOB segments in the SOA DEV_SOAINFRA schema. As such, these scripts can be used as a starting point to build out purge scripts targeting your environment.
The purge script can be changed to target only certain composites, or alternatively other SOA Suite repositories, such as BAM and Mediator.
Separately the SOA Suite database tables support partitioning to allow for the DEV_SOAINFRA schemas to be hosted across multiple databases, splitting the storage costs and reducing processing times. Partitioning databases is beyond the scope of this book and the expected skills of the reader, a database administrator can consult the Oracle Fusion Middleware documentation for more information