The question on the ability to purge instances for both Oracle BPEL and Oracle ESB keeps recurring so I decided to come up with a small overview.
FMW version 188.8.131.52 and up, there is a new version of the purge_soainfra_oracle.sql. Ensure that you run the purge setup scripts from the 184.108.40.206 RCU or 220.127.116.11 RCU location, respectively, as this contains the latest purge details. Details are in the SOA Admin Guide
FMW version 18.104.22.168 and up, there is a purge_soainfra_oracle.sql script in the RCU_HOME/rcu/integration/soainfra/sql/purge directory. In the release before 22.214.171.124 you need to apply patch 10297850 to obtain the updated purge scripts. More details are in the FMW Admin Guide on Managing Database Growth. Mark Kelderman posted on how to improve the performance of this script by adding a specific index.
From BPEL in SOA Suite version 10.1.3.3.1 and up, there is a purge_instances_oracle.sql script in the SOA_HOME/bpel/system/database/scripts directory.
For BPEL in SOA Suite version 10.1.3.1 you need patch 7162289. After applying this patch purge_instances_oracle.sql will be located at SOA_HOME/bpel/system/database/scripts.
In case you’re still running BPEL 10.1.2.0.2 check Oracle support document 398578.1 and document 341102.1
For Mediator in FMW 11g refer to the scripts and links mentioned in the BPEL paragraph.
To purge ESB instances in 10.1.3.4 you need to apply at least MLR#4 to have purge scripts available. There are 3 scripts available:
Recently the SOA Suite Engineering Team published a presentation on Purging strategies in Oracle SOA Suite 11gR1 PS3. Besides the information presented in this post, the presentation goes into targeted deletion from the EM and looks into looped and parallel purges. It also mentions the use of database partitioning. That can be very useful when you have long running instances and a large installation (the presentation gives more than 500GB total OR more than 10GB daily depending on environment specifics).
Update 2011-07 Purging the Extreme Edition
Mark Kelderman posted a blog on Purging SOA Suite 11g Extreme Edition. He give a SQL script that deletes data from Mediator, BPEL, BPM, Workflow and Composite, and after that reclaims disk space.
Update Purge Oracle Service Bus
Marc Kelderman posted on his blog on Purging OSB Report Data. Besides the default way to delete the data using the console. He also gives a SQL script to purge directly in the database.
SOA 11g database growth strategies
In January of 2012 the Oracle Fusion Middleware whitepaper on database growth strategies was published. The advice presented should facilitate better dialog between SOA and Database administrators when planning database and host requirements. An important aspect of the whitepaper is purging of the SOA database.
As part of the Oracle Fusion Middleware (FMW) SOA 11g Infrastructure Database: Installation, Maintenance and Administration Guide [ID 1384379.1] A purging guide has been published to offer strategies and procedures for managing database growth in a production SOA 11g implementation.
Purging is not supported for MS SQL Server out of the box or through a patch in any version of 11g. The provided scripts can, however, be used as a solid base from which to create usable purge scripts for SQL Server.
It is possible you noticed that after the installation of Oracle AIA 11g R1 starting the Managed Server of WLS takes more time than before the installation. This is not only due to the new Composites. There are also some settings you could change to reduce the time the Managed server to which you deployed AIA needs to start up.
Change JDBC settings
To alter some of the JDBC settings that are configured during the AIA 11g installation go to the WLS Adminnistartion console – http://yourserver:port/console. In the Domain Structure choose: > Services > JDBC > Data Sources.
Change the settings of the following JDBC Data Sources that were created during the install of AIA of the AIA Demo:
Data Source Settings
In the Settings for "Data Source Name" select the Configuration and the Connection Pool tab. Expand the the Connection Pool tab by clicking the Advanced link. The alter the Login Delay from 30 to 0 seconds.
This will reduce the time that is needed to start the Managed Server by several minutes.
There are several advantages when working in a project with Oracle SOA Suite to have a local installation of the SOA Suite on your workstation. While developing and trying bits of code you can’t break other peoples work as you can on a central server. Running FMW 11g on a workstation with say memory on the low end of the spectrum can be challenging. In this blog I’ll show you some additional (to this previous post) measure that my blogless colleague Rob Heikoop came up with.
Rob came up with two things:
Create an All in One AdminServer as described on the Oracle wiki. We skipped BAM in the installation Use only one Domain in WLS. You’ll have to combine the admin en the SOA_domain. Running just one domain saves memory.
Keep the database centralized. In the central database each developer has it’s own SOA schema that is especially created for him. To do this run the RCU for every developer and use the prefix to reflect for example the developers initials (example of using RCU on slideshare). Not running the database on your workstation saves memory and CPU.
In order to have new developers being able to use this quickly we copy the reference installation (with plug-ins, patches and the works) and alter the configuration:
Alter the scripts in the C:\Devel\SOAMiddleware\user_projects\domains\soa_domain\bin directory to reflect your workstation name instead of that of the reference machine. You can do this easily by using an editor like Notepad++ and use Search > Find in files > Replace in files. Otherwise you’ll be stopping your teammates server…. (again sorry Rob)
Change the configuration files in C:\Devel\SOAMiddleware\user_projects\domains\soa_domain\config\jdbc to reflect the prefix created especially for you while using the RCU. Again Notepad++ can help you here.
Although there were already several posts on querying AQ, like “in the queue” and “enqueue“, the next one is great when monitoring queues. We are using this to monitor AQ, especially for Oracle ESB (hence the commented addition):
, v$aq.*FROM v$aq
, all_queues aq
WHERE aq.qid = v$aq.qid
-- use if looking for ESB AQ: and aq.queue_table = 'ESB_JAVA_DEFERRED';
The query will result in something like:
It contains the following metrics:
Waiting – Number of waiting message;
Ready – Number of messages with status ready;
Expired – Number of expired messages;
Total Wait – Total waiting time per queue;
Average Wait – Average waiting time per queue.
A describtion of the AQ and Streams views can be found here.
If you are using AQ within Oracle ESB there might be a point in time you want to tune AQ performance. In this post you’ll find the results of Metalink research, and our experience on a production system.
When creating the ORAESB schema (in version 10.1.3.3) using the script $ORACLE_HOME/integration/esb/sql/oracle/create_esb_topics.sql, the queues are created with 8.1 compatibility. This is solved in 10.1.3.4. To alter this find the statement
This post describes how to change the JDK for an Oracle Application Server 10.1.3.x installation. To check which JDK versions are supported with Application Server releases, check Metalink note 258833.1.
Stop all running Application Server processess.
Rename the current JDK directory:
mv jdk jdk.old
Install or copy the JDK version you need into $ORACLE_HOME/jdk
Start the Application Server processess.
You can check the JDK version:
$ /jdk/bin>./java -version
AIX 5L specials
If your systems are running AIX 5L there is some patching to be done. Assuming you’re using JDK 1.5 you have to apply patch 5261515.
After upgrading to a IBM JDK it is very well possible to run into the JAVAX.NET.SSL.SSLKEYEXCEPTION:RSA PREMASTER SECRET ERROR. In that case you have to modify the $ORACLE_HOM/jdk/jre/lib/security/java.security to
From today there is a SOA Suite 10.1.3 familiy member available for WebLogic Server 10.3. Oracle SOA Suite 10.1.3.5.1 has been released. As describedearlier we were looking for a recent SOA Suite version that is certified for a recent WebLogic server version. And here it is: Oracle BPEL Manager, Oracle ESB, Oracle Rules, and OWSM (basically all versioned 10.1.3.5.1) are certified with WLS 10.3.1.