Oracle: “ORA-12519, TNS:no appropriate service handler found” on JDBC-Connection

In the context of a Java/Liferay/JackRabbit-Setting developer wants to export all stored files from database to filesystem for migration-purposes. After some uncertain time this always runs repeatedly on errors of this kind:

12:19:31,488 DEBUG [MaintenanceUtil:64] Executing com.liferay.portal.convert.ConvertDocumentLibrary
12:19:31,497 INFO  [ConvertProcess:41] Starting conversion for com.liferay.portal.convert.ConvertDocumentLibrary
12:19:31,537 DEBUG [MaintenanceUtil:64] Migrating 276 document library files
12:21:07,739 ERROR [DatabaseFileSystem:225] failed to initialize file system
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
(stacktrace follows...)

After this error the application goes on until next occurrence of that error.

Monitoring the listener.log while executing the migration-application shows a massive increase of connection-requests.

[oracle@serv1 ~]$ tail -f ./diag/tnslsnr/serv1/listener/trace/listener.log | egrep -i "hostdb|ORA-|TNS-"


28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57601)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57602)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57603)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57604)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57605)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57606)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57607)) * establish * hostdb * 0
28-JUN-2013 16:52:31 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57608)) * establish * hostdb * 12519
TNS-12519: TNS:no appropriate service handler found
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57609)) * establish * hostdb * 12519
TNS-12519: TNS:no appropriate service handler found
28-JUN-2013 16:52:32 * service_update * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57610)) * establish * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57611)) * establish * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57612)) * establish * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57613)) * establish * hostdb * 0
28-JUN-2013 16:52:32 * (CONNECT_DATA=(SID=hostdb)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=appuser))) * (ADDRESS=(PR
OTOCOL=tcp)(HOST=10.107.108.140)(PORT=57614)) * establish * hostdb * 0

Apparently the app opens a new connection for every to be exported file – and this ~3-8 times per second. Monitoring the “v$session”-view shows no significant increase in the overall number of sessions – still round about 110. So the lifetime of every such connection must be very short. Actually the app uses a connection-pool on the application-server but this seems to be not properly implemented.
Digging the web for that error-message brought up the advice to rise the value for parameter “PROCESSES” on the db.
But as we can see, this limit was never hit until now:

SQL> select * from v$resource_limit where resource_name in ( 'processes','sessions');

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
------------- ------------------- --------------- -------------------- -----------
processes                     109             138        150                  150
sessions                      122             161        248                  248

SQL>

Apart from that I wouldn’t accept to rise that limit, with a corresponding restart of my otherwise well running DB, just because of an untidy implemented use of connectionpooling. And also I guess it would just defer the next appearance of the same error.

At http://www.orafaq.com I finally found a posting that shed some light on the problem. As also stated on MOS (ID 240710.1) the listener counts all incoming connections and rises the “TNS-12519 TNS:no appropriate service handler found” if the sum of connections hit the PROCESSES-Limit of that particular database. Unfortunately the listener’s count is not always accurate as he’s not aware of the disconnects until the next “service_update” from db to listener happens. So if, as in my case, there are very many short connections, it is likely to happen that the listener mistakenly locks access to the DB for new connections.
In another post I found the advice to use a shared-server-configuration to cope with the problem. This works like a connection-pool on the DB-Side and so we would just have a handful of server-processes fed by some dispatchers with user-requests instead of one server-process for every dedicated connection.

If you have XML-DB-Option installed, shared_server should be enabled by default. To find the name of our XDB-dispatcher-service we could query:

SQL> select name, value from v$parameter where name='dispatchers';

NAME        VALUE
----------- ----------------------------------------
dispatchers (PROTOCOL=TCP) (SERVICE=hostdbXDB)

SQL>

If shared_server is not yet configured, don’t worry. To enable shared_server we just have to set the parameter shared_server to a value greater than zero. This can be done dynamically without rebooting the database.

ALTER SYSTEM SET SHARED_SERVER=1 SCOPE=BOTH;

This starts one shared_server-process, what should be enough for testing. For information on suggested numbers of shared_server-processes in relation to the expected number of concurrent sessions, please dig the web.

If shared_server is configured, a dispatcher is implicitly started. To configure our dispatcher to respond on a named service we do:

ALTER SYSTEM SET DISPATCHER='(PROTOCOL=TCP) (SERVICE=hostdbSS)' SCOPE=BOTH;

Now we have to configure the JDBC-Connect-String to use this service-name. To use Oracle Service-Names in JDBC we must use this syntax (from Oracle-Docu):

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:

@//host_name:port_number/service_name

For example:

jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

If this doesn’t work you should try to use the fully qualified service-name in the format “servicename.domainname” (e.g. “hostdbXDB.private.domain.com”).

If we want every (TCP-)connection, that doesn’t explicitly request a dedicated_server, to use the shared_server, we simply omit the service_name in our dispatcher’s config and set the dispatcher to respond on all TCP-connections:

ALTER SYSTEM SET DISPATCHER='(PROTOCOL=TCP)' SCOPE=BOTH;

If we now restart our application to make it use the new setting, we should see it using the dispatcher-process when querying “lsnrctl service”:

[oracle@serv1 ~]$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 02-JUL-2013 10:07:23

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serv1.private.domain.com)(PORT=1522)))
Services Summary...
Service "hostdb.private.domain.com" has 1 instance(s).
  Instance "hostdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:14179 refused:0 state:ready
         LOCAL SERVER

Service "hostdbXDB.private.domain.com" has 1 instance(s).
  Instance "hostdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:5649 refused:0 current:74 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=serv1.private.domain.com)(PORT=32829))

In the line ” “D000″ established:5649 refused:0 current:74 max:1022 state:ready” we see the dispatcher “D000” that has served 5649 connections until now and currently holds 74 sessions connected.

More info about our shared-server-(usage) can be found by querying the following views:

select * from V$SHARED_SERVER;
select * from V$CIRCUIT;
select * from V$DISPATCHER;
select * from V$SHARED_SERVER_MONITOR;
select * from V$DISPATCHER_CONFIG;

E.g. the view “V$CIRCUIT” shows all current connections to the dispatcher.

Querying “select count(*), server from v$session group by server;” would show us the number of connections per access-path:

DEDICATED: dedicated user-connections to DB
SERVER: shared_server-connections doing some stuff
NONE: shared_server-connections on idle

If a session is inactive, it is just connected to it’s dispatcher-process and no shared_server-process is active on behalf of that session – hence the “NONE”.

While solving this problem I came across a listener-parameter new to me, with which it is possible to limit the connection-rate per second (e.g. in defense of DDoS): “CONNECTION_RATE_listener name