Wednesday, 2 September 2009

Oracle XE Concurrent Connection Problem

I was hitting a problem with an application connecting to an oracle XE database. Over intermittant periods of time, the application would be unable to connect to the database anymore, throwing the following exception.

ORA-12519: TNS:no appropriate service handler found 

Issuing the following command

lsnrctl services

Showed other stuff and the following important line

Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:688 refused:0 state:blocked
         LOCAL SERVER

I googled around a lot, and the best answer I could come up with was on an Oracle forum. It essentially said that the oracle process tells the listener how many connections are available, and then every now and again it tells the listener when connections have been closed. So the problem will manifest itself if a lot of connections are opened before the database tells the listener about the closures. By default in Oracle XE, 40 connections are allowed, which after accounting for background processes leaves 19 client connections.

So the solutions that I could come up with

1) Not use Oracle - moving to MySQL should remove  this problem, but may introduce others ;-)
2) The real fix would be to stop the client from creating too many connections via a connection pool. Depending on the application, this may or may not be possible
3) A quick fix to mask the problem would be to alter the number of allowed processes via issuing the following command as sysdba, and the restarting the database and listener

ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE

No comments:

Post a Comment