Our app began to raise ORA-12516, TNS:listener could not find available handler with matching protocol stack errors, and I'm trying to diagnose why.

The workload has not changed, so I think we might have introduced a bug in the application code, but I can't see why. The most obvious would be a connection leak, or unclosed idle transactions, but:

  1. we are using a jdbc pool (tomcat-jdbc-pool), and the max active value is 73, below the 150 processes Oracle is configured to use. So I would have thought the pool would throw an error before Oracle hits its own limits.

  2. do select * from gv$resource_limit; here is what I get:

---------- ------------------------------ ------------------- --------------- ------------------ -----------
         1 processes                                       32              40        150                150  
         1 sessions                                        32              45        248                248  

So it seems max utilization never hits the limit.

Does anyone have a tip on how to track this down?

EDIT: I made some progress on this. It turns out that tomcat-jdbc-pool has a query to check connections health. This query was configured as select 1 from dual;. Notice the semi-column, as this is the culprit: removing it fixes the app. I still have to find out what really happens with the ; at the end of the check query. Any advice is welcome.

  • Is it possible that there is a firewall that's half dropping connections so the client application pool thinks there are less open connections, but Oracle still sees the connections as open and doesn't terminate the associated background processes? If you're using Unix you should be able to track this down with netstat.
    – Philᵀᴹ
    Apr 20 '14 at 15:30
  • This is happening on localhost, and there is no iptables rules...
    – alci
    Apr 20 '14 at 15:39
  • Same here, this is strange. Oracle DB is 11g Express Edition, database pool is tomcat-jdbc-7.0.30, with select 1 from dual; the database produces ORA-12516 under heavy load, with select 1 from dual all is good. Bizarre.
    – wallenborn
    May 11 '15 at 10:12

I think select 1 from dual; is a syntax error. Here's what happens on the wire:

With select 1 from dual a connection's lifecycle is:

  • Initial establishment
  • First query (validation does not yet happen)
  • Return to the pool
  • Validation: the pool sends select 1 from dual
  • The database answers: ORA-01403: no data found
  • Next query

and so on. The connection is normally not closed.

With select 1 from dual; however, the following happens:

  • Initial establishment
  • First query
  • Return to the pool
  • Validation: the pool sends select 1 from dual;
  • The database answers: ORA-00911: invalid character
  • The pool terminates the connection with a normal FIN packet.

The next query then begins with a freshly established connection. Now, as long as the load stays low, you might not even notice this. But under high load, all this reconnecting must exhaust some database resource. Which one, i don't know...

  • I don't understand how ORA-01403 will be returned for the first query. May 11 '15 at 12:31
  • I don't either, but that's what i am seeing in wireshark.
    – wallenborn
    May 11 '15 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.