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:

SELECT 
  u.tablespace
, 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
, Q.NAME
, s.event
FROM 
  sys.v_$session s
, sys.v_$sort_usage u
, sys.v_$parameter p
, sys.dba_queues q
WHERE p.name   = 'db_block_size'
AND a.saddr    = b.session_addr
AND a.username = 'SCOTT'
AND a.p1text   = 'queue id'
AND a.p1       = q.qid
ORDER BY 
  a.machine
, 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/wlst.sh resetdatasource.py    
#
# based on:
#     http://biemond.blogspot.com/2010/04/resetting-weblogic-datasources-with-ant.html
#

#
# Crontab:
#  10 1 * * 6 /bin/bash -x /opt/weblogic/Middleware/Oracle_OSB/common/bin/wlst.sh a.py 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(datetime.datetime.now())
n1 = datetime.datetime.now()

#
# 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)
domainRuntime()

drs = ObjectName("com.bea:Name=DomainRuntimeService,Type=weblogic.management.mbeanservers.domainruntime.DomainRuntimeServiceMBean");
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!'
  else:
    print 'Server: ' + serverName
    dsBean = ObjectName('com.bea:ServerRuntime=' + serverName + ',Name=' + serverName + ',Location=' + serverName + ',Type=JDBCServiceRuntime')
    if dsBean is None:
      print 'not found'
    else:
      datasourceObjects = mbs.getAttribute(dsBean, 'JDBCDataSourceRuntimeMBeans')
      #
      # Loop over datasources
      for datasourceObject in datasourceObjects:
        if datasourceObject is None:
          print 'datasource not found'
        else:
          dsName = mbs.getAttribute(datasourceObject,'Name')

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

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

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 
/opt/weblogic/Middleware/Oracle_OSB/common/bin/wlst.sh 
/data/scripts/resetdatasource.py weblogic welcome1 
t3://myadminserver:7001 MyDQXaDS 
>>/data/logs/resetdatasource.log 2>&1