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.eventFROM sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter p, sys.dba_queues qWHERE p.name = 'db_block_size'AND a.saddr = b.session_addrAND a.username = 'SCOTT'AND a.p1text = 'queue id'AND a.p1 = q.qidORDER 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 sysimport tracebackimport timeimport 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 serversfor 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
