deltalounge

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.

Oracle BPEL

• FMW version 11.1.1.4 and up, there is a new version of the purge_soainfra_oracle.sql. Ensure that you run the purge setup scripts from the 11.1.1.4 RCU or 11.1.1.5 RCU location, respectively, as this contains the latest purge details. Details are in the SOA Admin Guide
• FMW version 11.1.1.1 and up, there is a purge_soainfra_oracle.sql script in the RCU_HOME/rcu/integration/soainfra/sql/purge directory. In the release before 11.1.1.4 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

Oracle ESB

• 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:

$ORACLE_HOME/integration/esb/sql/other/purge_by_date.sql
$ORACLE_HOME/integration/esb/sql/other/purge_by_id.sql
$ORACLE_HOME/integration/esb/sql/other/purge_by_instance_id.sql

Update 2011-06 SOA Suite 11gR1 PS3

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.

Related documentation:

• Oracle BPEL 10g purging strategies
• SOA 11g Infrastructure Database: Installation, Maintenance and Administration Guide [ID 1384379.1]
• Managing Space in the SOA Infrastructure Database

Updated purging strategies on Oracle Support

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.

The Wall Street Journal posted a video on their Video Center in which it is very clearly explained how internet cookies are used to track you while browsing the web.

In the real world it is very hard to keep track of your preferences, especially between a variety of shops. In the virtual world it is too easy.

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

JDBC Settings

Change the settings of the following JDBC Data Sources that were created during the install of AIA of the AIA Demo:

• AIACentralDS
• AIADataSource
• FODDataSource
• FODDS
• LifeCycleDataSource
• mds-soa
• XrefDataSource

Data Source Settings

Login Delay

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.

Now you can start the tools:

• WebLogic Server: C:\Devel\SOAMiddleware\user_projects\domains\soa_domain\bin\startWebLogic.cmd
• JDeveloper: C:\Devel\SOAMiddleware\jdeveloper\jdeveloper.exe

Previous I mentioned a preview of the 11g R1 PS2 new features. Here are a few links for this new release – 11.1.1.3.0:

• SOA Suite 11g R1 PS2 new features.
• Download page
• The documentation index will probably be updated soon.

Existing PS1 users simply apply the patch to upgrade to 11.1.1.3.0.

Other releases

• Oracle Service Bus OSB 11g R1
• Business Process Management BPM 11g R1

On January 27th Larry Ellison and other Oracle executives outlined the Oracle Sun strategy in a live event. The webcast and sheets are available online. There is also a FAQ overview available.

Besides that there is the Oracle + Sun Product Strategy Webcast Series. If you are into Java or Middleware developement, I think the message boils down to these few sheets:

Development Tools

Development Tools Strategy

Application Server

Glassfish and WebLogic will coexist and share logic/components.

AS strategy

SOA products

Oh, and WebCenter will be the strategic portal offering.

SOA product strategy

• Integration perspective
• Oracle gets MySQL for free

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):

1
2
3
4
5
6
7
8
9

SELECT aq.name
,      aq.queue_table
,      aq.queue_type
,      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.

Queue compatibility

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

1
2
3
4
5
6

dbms_aqadm.create_queue_table
    ( Queue_table => qtablename
    , Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE'
    , multiple_consumers => TRUE
    , compatible => '8.1'
    );

and change this to:

1
2
3
4
5
6

dbms_aqadm.create_queue_table
    ( Queue_table => qtablename
    , Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE'
    , multiple_consumers => TRUE
    , compatible => '10.2'
    );

if you already created the queues, use this statement:

1
2
3
4

dbms_aqadm.migrate_queue_table
    ( queue_table => 'ESB_JAVA_DEFERRED'
    , compatible => '10.2'
    );

If you’re not sure check the compatibility with this query:

1
2
3
4
5
6

SELECT queue_table
,      compatible
,      recipients
FROM   dba_QUEUE_tables
WHERE  owner = 'ORAESB'
;

Streams pool size

Verify the current stream_pool_size using the following query:

1
2
3
4
5
6
7

SELECT component
,      current_size/1024/1024 "CURRENT_SIZE"
,      min_size/1024/1024 "MIN_SIZE"
,      user_specified_size/1024/1024 "USER_SPECIFIED_SIZE"
,      last_oper_type "TYPE" 
FROM   v$sga_dynamic_components
;

look for the streams pool. There are several Metalink notes on this setting (including 316889.1, 102926.1 and 335516.1). The latter has a general recommandetion per RDBMS version:

• 11g: set STREAMS_POOL_SIZE to be greater or equal to 100 MB;
• 10gR2: set SGA_TARGET > 0 and STREAMS_POOL_SIZE=0 to enable autotuning of the Streams pool;
• 10gR1: use the STREAMS_POOL_SIZE init.ora parameter to configure the Streams memory allocation;

And of course you could use V$STREAMS_POOL_ADVICE to get advice for your specific situation.

Upgrade the JDK

A described here upgrading the JDK can also give a performance boost.

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.

1. Stop all running Application Server processess.
2. Rename the current JDK directory:

1 2	cd $ORACLE_HOME mv jdk jdk.old

3. Install or copy the JDK version you need into $ORACLE_HOME/jdk
4. Start the Application Server processess.

You can check the JDK version:

1	$ /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

1 2 3 4 5

security.provider.1=com.ibm.jsse2.IBMJSSEProvider2 security.provider.2=com.ibm.crypto.provider.IBMJCE security.provider.3=com.ibm.security.jgss.IBMJGSSProvider security.provider.4=com.ibm.security.cert.IBMCertPath security.provider.5=com.ibm.security.sasl.IBMSASL

and create a symbolic link (or copy the jar) from the directory $ORACLE_HOME/jre/lib/ext/ibmjsseprovider2.jar to $ORACLE_HOME/jdk/jre/lib/ibmjsseprovider2.jar as described in Metalink note 746423.1.

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 described earlier 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.

• Download SOA Suite 10.1.3.5
• Dive into the certification matrix 10.1.3
• Expect the documentation pages to be updated shortly.
• 10.1.3 Support status and alerts on Metalink

Update – Installation instructions

Installation instructions for SOA Suite 10.1.3.5.1 on WebLogic Server can be found in these blogs:

• Oracle ACE Edwin Biemond
• Solution Architect Kavin Mehta