Friday, December 14, 2012

Soa purge 11g

Since the start on fusion we have been plagued with purge related issues, 11.1.1.3 the purge job barely worked.So we applied patches on top of it to fix it.On 11.1.1.5 its far better.So i will talk W.R.T 11.1.1.5 or higher version of SOA,

There are 2 options for purging SOA Tables :
 1)  Looped Purge - The stored procedure soa.delete_instances provides the looped purge implementation. Debug mode can be enabled by executing  soa_purge/common/debug_on.sql and setting serverout on.
Sample client to execute looped purge is listed below.


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-01-01','YYYY-MM-DD');
   MAX_CREATION_DATE := to_timestamp('2010-01-31','YYYY-MM-DD');
    max_runtime := 60;
    retention_period := to_timestamp('2010-01-31','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,
     purge_partitioned_component => false);
  END;
  /

 2) Parallel Purge: The stored  procedure soa.delete_instances_in_parallel provides the parallel purge implementation  To execute the parallel purge, the following additional privileges are required for the soainfra user.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)
    i)  Create Job
    ii) execute on dbms_lock.


DECLARE
   max_creation_date timestamp;
   min_creation_date timestamp;
   retention_period timestamp;
  BEGIN
   min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
   max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
   retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
    soa.delete_instances_in_parallel(
     min_creation_date => min_creation_date,
     max_creation_date => max_creation_date,
     batch_size => 10000,
     max_runtime => 60,
     retention_period => retention_period,
     DOP => 3,
     max_count => 1000000,
     purge_partitioned_component => false);
 END;

 Debug mode can be enabled by executing the script 'soa_purge/common/debug_on.sql' and setting serverout on .  The debug logs from the  jobs  spawned by parallel purge will  be logged into files created in the directory 'SOA_PURGE_DIR' . This directory needs to be created(e.g: create directory SOA_PURGE_DIR as '\tmp\purge') and the soainfra user needs to be provided write access on this directory. Also ensure that the location of the  directory is  accessible to the oracle server.

There is another option ignore_state=> true ,which purges instances regardless of its state.Normally only completed instances are purged.



1 comment:

  1. ALso for 11.1.1.3 the script used is different.

    Its as below,(run as soainfra user)

    BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'PURGE_COMPOSITES',
    job_type => 'PLSQL_BLOCK',
    job_action => 'DECLARE
    FILTER INSTANCE_FILTER := INSTANCE_FILTER();
    DELETED_INSTANCES NUMBER;
    BEGIN
    FILTER.MAX_CREATED_DATE := SYSDATE - 7; /* Keep 7 days dehydration data */
    DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
    FILTER => FILTER,
    MAX_INSTANCES => 100000,
    PURGE_PARTITIONED_DATA => TRUE );
    EXCEPTION
    WHEN OTHERS THEN
    NULL; /* TODO Exception Handling */
    END;',
    repeat_interval => 'FREQ=DAILY;',
    start_date => to_timestamp('03/25/2011 23:00:00','MM/DD/YYYY HH24:MI:SS'),
    job_class => 'DEFAULT_JOB_CLASS',
    auto_drop => FALSE,
    enabled => TRUE);
    COMMIT;
    END;
    /

    ReplyDelete