How to find purgeable instances in SOA/BPM 12c by Derek Kam

 

clip_image002If you are familiar with SOA/BPM 11g purging, after you have upgraded/implemented SOA/BPM 12c, you will not be able to use most of the SQL for 11g to determine the purgeable instances.  This is because SOA/BPM 12c is no longer using composite_instance table for composite instance tracking.

In SOA/BPM 12c, a common component is used to track the state associated with a business flow and report audit information.  This design will reduce the instance tracking data generated and stored in the database, and improve purge performance by minimizing the number of tables that need to be accessed.  Component instance state will no longer be stored in individual table for instance tracking purpose, the overall flow state will be stored in SCA_FLOW_INSTANCE table.

In SCA_FLOW_INSTANCE table, the “active_component_instances” column keeps track of how many component instances are still in a running/active state. These are the instances in one of the following states:

  • RUNNING
  • SUSPENDED
  • MIGRATING
  • WAITING_ON_HUMAN_INTERVENTION

When the “active_component_instances” value reaches 0, this indicates that the Flow is no longer executing. There is another column called “recoverable_faults”, this column keeps track of how many faults can be recovered. This information together with the “active_component_instances” is used to determine whether the Flow can be purged or not.

The SCA_FLOW_ASSOC table is used to record the association between the original Flow that creates the BPEL component instance and the correlated Flow. The SCA_FLOW_ASSOC table is used by the purge logic to ensure that all correlated Flows are purged together when none of the flow is in an active state.

Another important thing to take note: if you create a SOAINFRA schema with LARGE database profile, all transactional tables will be created with range-partition. If you decide to run the SOA purging with the purge script either manually by running the stored procedure or by using auto purge function which can be configured in Oracle Enterprise Manager Fusion Middleware Control, you will need to set the purge_partitioned_component => true (default is false), otherwise the purge logic will skip all partitioned tables when the purge script run and no flow instance will be purged.  You will be able to find all the partition tables in your SOAINFRA schema by using the following SQL: ?select table_name from user_tables where partitioned = ‘YES’;

You can use the following sample PL/SQL to determine whether the SCA_FLOW_INSTANCE has been partitioned and the number of purgeable flow instances in your SOAINFRA schema. Please read the complete article here.

SOA & BPM Partner Community

For regular information on Oracle SOA Suite become a member in the SOA & BPM Partner Community for registration please visit www.oracle.com/goto/emea/soa (OPN account required) If you need support with your account please contact the Oracle Partner Business Center.

Blog Twitter LinkedIn image[7][2][2][2] Facebook clip_image002[8][4][2][2][2] Wiki

Purging and partitioned schemas by Christian

SOA Suite 11g and 12c both require regular database maintenance for optimal performance. A key task in managing your SOA Suite database is a regular purging strategy. You should be doing this, so read the Oracle SOA Suite database growth management strategy if you haven’t already: http://www.oracle.com/technetwork/middleware/bpm/learnmore/soa11gstrategy-1508335.pdf

One of the best practices for managing large SOA Suite applications is to use Oracle Database partitioning. In 11g this is usually a fairly ad-hoc setup, though the whitepaper has everything you need to know about setting it up; in 12c, the “LARGE” RCU profile is partitioned (with monthly partitions).

Purging a partitioned schema usually involves running the check and move scripts, to ensure your partitions don’t contain “LIVE” data (based on your retention policy), followed by dropping the “OLD” partitions and rebuilding the indexes.

However, there are times where you may want to run a purge to clean up data that doesn’t neatly align with the partitions, for example in a load testing environment. The purge scripts, by default, won’t touch any table that is partitioned. If your favourite table isn’t mentioned in the purge debug log output (example below), then it is probably because it is partitioned.

To force the purge scripts to consider partitioned tables, you need to enable the “purge_partitioned_component” flag to the “delete instances” purge function (see below). The purge script will then purge partitioned tables.

Obviously, this is not intended for regular production use and it should never be used there.

An example invocation with the flag set:
soa.delete_instances(max_runtime => 300, min_creation_date => to_timestamp('2000-01-01','YYYY-MM-DD'), max_creation_date => to_timestamp('2000-12-31','YYYY-MM-DD'), purge_partitioned_component=TRUE);

The example output below is from a soa.delete_instances run that has a partition on composite_instance. Note that there is no mention of composite_instance in the output. Read the complete article here.

SOA & BPM Partner Community

For regular information on Oracle SOA Suite become a member in the SOA & BPM Partner Community for registration please visit www.oracle.com/goto/emea/soa (OPN account required) If you need support with your account please contact the Oracle Partner Business Center.

Blog Twitter LinkedIn image[7][2][2][2] Facebook clip_image002[8][4][2][2][2] Wiki

Purging data from Oracle SOA Suite 11g – Part 1 by C2B2

clip_image002The following questions will be answered:

    • How does Oracle SOA Suite 11g (PS6 11.1.1.7) store data?
    • What data does Oracle SOA Suite 11g (PS6 11.1.1.7) store?
    • Why do you need to purge Oracle SOA Suite 11g (PS6 11.1.1.7) data?
    • What are the purging options available for Oracle SOA Suite 11g (PS6 11.1.1.7)?
    • Which data will be purged by the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script?
    • List of composite instance states that will be considered for purging by the purge script
    • How to install the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script?
    • How to execute the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script?
    • What is Looped purging (Oracle SOA Suite 11g (PS6 11.1.1.7) purge script)?
    • What is Parallel purging (Oracle SOA Suite 11g (PS6 11.1.1.7) purge script)?
    • Description of parameters used by the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script
    • Example 1: Executing the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script for all composites
    • Example 2: Executing the Oracle SOA Suite 11g (PS6 11.1.1.7) purge script for a specific composite

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

SOA Suite uses a database schema called SOAINFRA (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? Read the complete article here.

SOA & BPM Partner Community

For regular information on Oracle SOA Suite become a member in the SOA & BPM Partner Community for registration please visit www.oracle.com/goto/emea/soa (OPN account required) If you need support with your account please contact the Oracle Partner Business Center.

Blog Twitter LinkedIn image[7][2][2][2] Facebook clip_image002[8][4][2][2][2] Wiki

Purging Data From the BPEL Store by Matt Brasier

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.
Getting ready
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 Read the complete article here.

SOA & BPM Partner Community

For regular information on Oracle SOA Suite become a member in the SOA & BPM Partner Community for registration please visit www.oracle.com/goto/emea/soa (OPN account required) If you need support with your account please contact the Oracle Partner Business Center.

Blog Twitter LinkedIn image[7][2][2][2] Facebook clip_image002[8][4][2][2][2] Wiki