X

An Oracle SOA Suite - Team Blog

Purging Strategies in Oracle SOA Suite 11gR1 PS3

Guest Author

UPDATE: the detailed whitepaper on "SOA 11g Database Growth Management Strategy" is now available.

Just a quick post before taking off for the long Memorial Day weekend in the US (we are off on Monday).

Since shipping Oracle SOA Suite 11g we’ve seen an explosion of the amount of data being processed by the product across the world. The net result of this is a corresponding growth in audit data and increasingly more complex needs on the purging side: often times purges need to happen on running system and that means with the least possible impact on operations. We have added several improvements in PS3 to the purge scripts. Here is a set of slides describing the various purging strategies now available in Oracle SOA Suite: from the graphical tools in Enterprise Manager, to PL/SQL scripts (looped purge and parallel purge) and database partitioning.

image This slide deck is a work in progress so please leave your questions on purging in comments and I will try to address them in the next version (and if I don’t, Deepak will in the more substantial whitepaper that he is preparing on this very topic).

Join the discussion

Comments ( 5 )
  • Luis Tuesday, June 28, 2011

    Hello,

    I have a customer that is working with SOA11g 11.1.3 and we are experiencing problems with the soa_infra tablespace and the purgescript provided by Oracle. Basically we are not releasing data after using the script for removing all instances completed, faulted or in stale status.

    we have seen how the EM (enterprise Manager SOA ) is having performance issues (very slow) when the tables grow. i was wondering if we need to drop some indexes or truncate other tables so I would be pleased if someone can give some advice with this issue.

    Regards

    Luis


  • Emiel Paasschens Wednesday, February 1, 2012

    At my customer we have migrated from Oracle SOA Suite 11g PS1 to PS4. The problem now is that with the new purge scripts we can not purge on composite name any more!

    The situation is that we have a composite (called the JMS_ROUTER) that reads messages from a jms queue and it filters the messages on type and customer so only the needed message are routed to other composites. The result is that 99% of thousands of messages are filter out.

    With the purge scripts of PS1 all the messages of this JMS_ROUTER composite were purge every night, while for the other composites the success message older than 3 working days were purge and the failures even after a month old.

    How are we gonna do this now?


  • Demed Thursday, February 2, 2012

    Luis,

    Sorry I missed your comments when you posted it.

    Please have a look at this whitepaper: http://bit.ly/soa-db-mgmt and also make sure to request the latest versions of the purge scripts from Oracle Support.

    Emiel,

    Thanks for the detailed use case - it helps and really makes sense.

    The ability to purge on composite names is available in the latest version of the scripts. You can get them from Oracle Support. If you are on PS3 it should be Patch 14512575 (and please also have a look at the above whitepaper)


  • Demed Friday, February 3, 2012

    Correction: the patch number is 11832797

    Go to http://support.oracle.com and click on "patches & updates" to download it.


  • guest Monday, February 6, 2012

    Thanx for the quick response. I've already downloaded the patch and a quick look learned me that it will solve my problem for the most important part.

    The remaining part is the posibility to purge depending on instance state.

    Currently in production (PS1) the succesfull instances are purged after three working days, where faulted instances are purged after a month. Is there another patch available or in development which solves this?


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.