Monday, January 30, 2012

AQAdapter: Reset your datasource

Using the AQ Adapter in a cluster environment, can cost you a lot of temporary tablespace. When you use AQ for dequeuing events and process them further with OSB or SOA Suite, there is always a connection to the database.

This connection is waiting for messages to appear on the Queue (it is subscribed). From middleware perspective everything works fine, data is being dequeued and your proxy-service or composite is running.

But at database level, the session is consuming tablespace. This is the temporary tablespace, so far so good. But if you use this on heavy load for a long time, the temporary space will grow to hundreds of mega or even gigabytes.

To determine how much temp space your sessions are using, you can execute the following SQL:

, ROUND(((u.blocks*p.value)/1024/1024),2)||'M' "SIZE"
, s.sid||','||s.serial# SID_SERIAL
, s.username
, s.program
, s.machine
, s.logon_time
, s.event
  sys.v_$session s
, sys.v_$sort_usage u
, sys.v_$parameter p
, sys.dba_queues q
WHERE   = 'db_block_size'
AND a.saddr    = b.session_addr
AND a.username = 'SCOTT'
AND a.p1text   = 'queue id'
AND a.p1       = q.qid
, u.blocks;

I try to solve this by playing with the AQAdapter settings and de XA-datasource behind it. Non of them fixed the issue. The only way I solved it was by resetting the data-source. On the Internet I find a nice article from Edwin Biemond and refined his script into one I prefer. This is the result:

# Usage:
# $ORACLE_HOME/common/bin/    
# based on:

# Crontab:
#  10 1 * * 6 /bin/bash -x /opt/weblogic/Middleware/Oracle_OSB/common/bin/ weblogic t@ctwl4cc t3://l2-mslaccadm02:7201 MslDQXaDS >>/data/logs/resetdatasource.log 2>&1
import sys
import traceback
import time
import datetime

print 'Started at: ' + str(
n1 =

# Parse command line paramteres
adminUser       = sys.argv[1]
adminPassword   = sys.argv[2]
adminUrl        = sys.argv[3]
datasourceNames = String(sys.argv[4]).split(",")

connect(adminUser, adminPassword, adminUrl)

drs = ObjectName("com.bea:Name=DomainRuntimeService,");
domainconfig = mbs.getAttribute(drs, "DomainConfiguration");
servers = mbs.getAttribute(domainconfig, "Servers");

# Loop over servers
for server in servers:
  serverName = mbs.getAttribute(server, 'Name')
  if serverName == "AdminServer":
    print 'Server: ' + serverName + ' skipped!'
    print 'Server: ' + serverName
    dsBean = ObjectName('com.bea:ServerRuntime=' + serverName + ',Name=' + serverName + ',Location=' + serverName + ',Type=JDBCServiceRuntime')
    if dsBean is None:
      print 'not found'
      datasourceObjects = mbs.getAttribute(dsBean, 'JDBCDataSourceRuntimeMBeans')
      # Loop over datasources
      for datasourceObject in datasourceObjects:
        if datasourceObject is None:
          print 'datasource not found'
          dsName = mbs.getAttribute(datasourceObject,'Name')

          # Loop over datasource to be reset
          for datasourceName in datasourceNames:
            if dsName == datasourceName:
              print '    Reset: ' + dsName
                mbs.invoke(datasourceObject, 'reset', None, None)
              except Exception, err:
                print 'print_exc():'
                print 'print_exc(1):'
                traceback.print_exc(limit=1, file=sys.stdout)
              print '  Skipped: ' + dsName

n2 =
print 'Duration: ' +  str(n2 - n1)
print 'Finished at: ' + str(

The script can be scheduled via the crontab on the admin server. The following example starts the script every sunday at 01:10 AM.

10 1 * * 6 /bin/bash -x 
/data/scripts/ weblogic welcome1 
t3://myadminserver:7001 MyDQXaDS 
>>/data/logs/resetdatasource.log 2>&1

Wednesday, January 25, 2012

Oracle Service Bus Cookbook

Former Oracle collegueus and business parnerts wrote a nice cok book on the usages of Oracle Service Bus. The book is full of examples and guide you through the working and usage of the Oracle Service Bus. The is very technical of useful for developers who just ant to start the OSB. Experienced developers will use this book for the complete examples on the different technologies using OSB, such as JMS, EJB.

When you use this book, it is expected that you understand the concepts of XML, XLST, WSDL, WebServices, JMS and SOAP and basic knowledge of SQL. This is not explained in the book. Which is a advantage! Refer to the w3schools for such things.

You can obtain it via Packt Publishing.

Some subjects I miss in the book; best practice on execption handing, throtteling, deployment.

The examples in the book are based Oracle Service Bus patch set #3, but can also be used on top of patch set #4. This release is already available since august 2010. Why didn't' the authors use this version? I expect that the examples in the book can also be applied on the upcoming patch set release #5.