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 10.1.3.5.2, 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:

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

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

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

declare
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(queue_table => 'ESB_MONITOR', purge_condition => NULL, purge_options => po);
END;
/

declare
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(queue_table => 'ESB_CONTROL', purge_condition => NULL, purge_options => po);
END;
/

References:

  • New BPEL 10g Purge Scripts From 10.1.3.5 MLR#2 [ID 1110833.1]
  • ESB Purge Instances scripts in 10.1.3.4 and 10.1.3.5 [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 10.1.3.5 project. It is tested using JDBC 11.2.0.3 and OC4J 10.1.3.5.2.

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

clip_image002

Then adapt the jndi properties used in the class to your environment and run TestJMSClient.java.
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
OK
Lookup Queue java:comp/resource/COMMS_SUBMITORDER_PRODUCER/Queues/AIA_SALESORDERJMSQUEUE
OK
Creating Queue Connection...
OK
Creating Queue Session...
OK
Creating Sender...
OK
Creating Text Message...
OK
Sending message...
OK
Sent message: Testing: Tue Jul 10 16:21:02 CEST 2012 - ID:C47B70A81A140F5AE0436538A8C0198C

You might run into several connectivity issues:

Issue 1:

oracle.j2ee.rmi.RMIMessages EXCEPTION_ORIGINATES_FROM_THE_REMOTE_SERVER
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 node1.aiaperf.com:6003. 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 11.1.1.6 (PS5)

Up to release 11.1.1.5 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 11.1.1.6 there is one (still undocumented) new thread pool introduced for non-blocking invoke activities.

Here is a view of the System MBean Browser:

image

The MBean name is: 
oracle.dms:Location=soa_server1,name=/soainfra/engines/bpel/requests/non-block-invoke,type=soainfra_bpel_requests

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

image

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

image

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