Recent Blog Entries

Thursday, 26 July 2012

AIA Tips & Tricks Series: Purging ESB Resequencer Tables in AIA 2.5 (Example: Comms PIPs)

AIA 2.5 Comms PIPs use the ESB 10g Resequencer for the following ESB Services:

  • CommunicationsCustomerPartyEBSV2Resequencer
  • SyncAcctSiebelAggrEventConsumer_RS
  • ProcessSalesOrderFulfillmentSiebelCommsSequencer
  • Consume_UPDSO_RS

While processing extensive volumes, the number of rows can grow quite rapidly in the ESB Resequencer tables (ESB_MESSAGES, ESB_MESSAGE_MAP, ESB_GROUP_STATUS). It is therefore critical to include these tables in the regular purging procedure – the standard ESB purge scripts will not touch them.

For ESB, the ESB Resequencer Purge Script is made available though Patch 9309170 – “Purge script for ESB scripts resequencer table”.
It adds a file  purge_resequencer_by_timestamp_serviceGUID.sql in SOA_HOME>/integration/esb/sql/other
Running this in sqlplus on schema ORAESB creates a stored procedure

res_purge_by_date_sguid(date d, varchar guid)

For purging all services using resequencer older than 10 days you can execute for example:

   CURSOR reseq_services is
   select guid from wf_events where name in ('CommunicationsCustomerPartyEBSV2Resequencer', 'SyncAcctSiebelAggrEventConsumer_RS', 'ProcessSalesOrderFulfillmentSiebelCommsSequencer', 'Consume_UPDSO_RS');

   FOR service IN reseq_services LOOP
        res_purge_by_date_sguid(sysdate-10, service.guid);

If you want to determine all ESB services which use Resequencer dynamically you can use

SELECT GUID from WF_EVENTS where ENDPOINT_PROPERTIES like '%ResequencerType%'

So the sequence for purging all AIA data is:

  1. Purge Resequencer data with  res_purge_by_date_sguid  (Example above)
  2. Purge ESB runtime data with purge_by_date_package.Purge_by_Date(date d)
    Example: CALL purge_by_date_package.Purge_by_Date(sysdate – 10)
  3. Purge BPEL runtime data
    Example: CALL SINGLE_THREADED_LOOPED_PURGE.purge_instances_loop(date d, integer rows, integer seconds);
  4. Check ESB queue tables
  5. Check AIA PIP Queue Tables (in JMSUSER)

Additional resources:

A useful query of the number of messages in ESB_GROUP_STATUS to check the purge is

select error, count(1), name from esb_group_status e, wf_events w where e.service_guid = w.guid  group by error, name;

0    126   Consume_UPDSO_RS
1    53    ProcessSalesOrderFulfillmentSiebelCommsSequencer
0    136   CommunicationsCustomerPartyEBSV2Resequencer
1    1     CommunicationsCustomerPartyEBSV2Resequencer
0    219   ProcessSalesOrderFulfillmentSiebelCommsSequencer
1    1     Consume_UPDSO_RS

Useful script for checking all BPEL and ESB tables:

select 'attachment', count(1) from orabpel.attachment UNION
select 'audit_details', count(1) from orabpel.audit_details UNION
select 'audit_trail', count(1) from orabpel.audit_trail UNION
select 'cube_instance', count(1) from orabpel.cube_instance UNION
select 'cube_scope', count(1) from orabpel.cube_scope UNION
select 'dlv_message', count(1) from orabpel.dlv_message UNION
select 'dlv_subscription', count(1) from orabpel.dlv_subscription UNION
select 'invoke_message', count(1) from orabpel.invoke_message UNION
select 'native_correlation', count(1) from orabpel.native_correlation UNION
select 'work_item', count(1) from orabpel.work_item UNION
select 'xml_document', count(1) from ORABPEL.xml_document UNION
select 'esb_control', count(1) from ORAESB.esb_control UNION
select 'esb_error', count(1) from ORAESB.esb_error UNION
select 'esb_error_retry', count(1) from ORAESB.esb_error_retry UNION
select 'esb_faulted_instance', count(1) from ORAESB.esb_faulted_instance UNION
select 'esb_group_status', count(1) from ORAESB.esb_group_status UNION
select 'esb_java_deferred', count(1) from ORAESB.esb_java_deferred UNION
select 'esb_messages', count(1) from ORAESB.esb_messages UNION
select 'esb_message_map', count(1) from ORAESB.esb_message_map UNION
select 'esb_monitor', count(1) from ORAESB.esb_monitor UNION
select 'esb_relation_xml', count(1) from ORAESB.esb_relation_xml UNION
select 'esb_relation_xml', count(1) from ORAESB.esb_relation_xml UNION
select 'esb_transaction_status', count(1) from ORAESB.esb_transaction_status;

Purging ESB Queue Tables (example for ESB_MONITOR and ESB_CONTROL):

po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(queue_table => 'ESB_MONITOR', purge_condition => NULL, purge_options => po);

po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(queue_table => 'ESB_CONTROL', purge_condition => NULL, purge_options => po);


  • New BPEL 10g Purge Scripts From MLR#2 [ID 1110833.1]
  • ESB Purge Instances scripts in and [ID 788285.1]

Tuesday, 10 July 2012

Verifying connectivity from Siebel EAI JMS to AIA 2.5 / OC4J 10.1.3 using a generic JMS Test Client

The integration of Siebel, BRM and OSM using AIA 2.5 makes use of the JMS client built in Siebel EAI. To verify if you have connectivity and the correct parameters on Siebel side, there is a very helpful JMS Test Client available in My Oracle Support – see MOS Note “Basic Troubleshooting Steps for EAI JMS Transport [ID 850954.1]”.

However this note is very generic. For being able to instantly run this in an AIA 2.5 environment I am including this TestJMSClient here as a JDeveloper project. It is tested using JDBC and OC4J

Setting up and running the project is very easy: just copy the libraries needed from a SOA installation  to a directory where the client runs. See here for a screenshot of the required libs:


Then adapt the jndi properties used in the class to your environment and run
The standard behaviour of the TestJMSClient is to send a new JMS TextMessage every 5 seconds (100 in total).

A successful execution should produce this output:

Lookup Queue Connection Factory : java:comp/resource/COMMS_SUBMITORDER_PRODUCER/XAQueueConnectionFactories/AIA_Queue
Creating Queue Connection...
Creating Queue Session...
Creating Sender...
Creating Text Message...
Sending message...
Sent message: Testing: Tue Jul 10 16:21:02 CEST 2012 - ID:C47B70A81A140F5AE0436538A8C0198C

You might run into several connectivity issues:

Issue 1:

WARNING: Exception returned by remote server: {0}
java.lang.NoClassDefFoundError: oracle/classloader/PolicyClassLoader

Solution: include pcl.jar in the list of project libraries

Issue 2:

Creating Queue Connection...
Exception occurred: oracle.jms.AQjmsException: IO Error: The Network Adapter could not establish the connection

Solution:  One possible cuase may be that the host name of the database is not known on client side (missing entry in file /etc/hosts or \Windows\system32\drivers\etc\hosts)

Issue 3:

Creating Queue Connection...
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/bcel/generic/Instruction

Solution:  bcel.jar is missing in the list of project libraries

Issue 4:

oracle.j2ee.clustering.ClusteringMessages warningInOpmnGetServers
WARNING: Error in obtaining server list from OPMN on host Please verify that OPMN is running.
Lookup Queue Connection Factory : java:comp/resource/COMMS_SUBMITORDER_PRODUCER/XAQueueConnectionFactories/AIA_Queue
JNDI lookup failed: javax.naming.NameNotFoundException: java:comp/resource/COMMS_SUBMITORDER_PRODUCER/XAQueueConnectionFactories/AIA_Queue not found

Solution:  OC4J is not running or the Connection Factory JNDI name is wrong

Issue 5:

If you receive class version / serial id mismatch exceptions, this is likely a difference in the classes used in the aqapi.jar on client and on OC4J server side.

Solution:  Be sure that you are using the same aqapi.jar and ojdbc5.jar on client side and server side.

More on adopting the JMS Test Client for AIA 11.x soon!

Wednesday, 4 July 2012

New BPEL Thread Pool in SOA 11g for Non-Blocking Invoke Activities from (PS5)

Up to release there have been 4 thread pools in Oracle SOA Suite 11g to control parallelism of execution:

  • Invoke Thread Pool (for asynchronous invocations)
  • Engine Thread Pool (i.e. for callback execution)
  • System Thread Pool
  • Audit Thread Pool

Starting with there is one (still undocumented) new thread pool introduced for non-blocking invoke activities.

Here is a view of the System MBean Browser:


The MBean name is: 

You can change a synchronous invoke activity from a blocking call to non-blocking by using the partnerlink level property:


This thread pool is configured in SOA-Administration –> BPEL Service Engine Properties under “More BPEL Configuration Properties...” with the property DispatcherNonBlockInvokeThreads:


Be aware that the default is only 2 – so this can be a bottleneck in high load scenarios if not changed. Especially if you have multiple partnerlinks using non-blocking calls – because all of them share this thread pool…

Have fun, Stefan