Oracle Listener refused connection ORA-12519 troubleshooting

November 11, 2011 by 14 Comments 


Oracle database 11gR2 (


New database connection attempts to Oracle database 11gR2 fail with following Oracle error:

Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found   [SQLCode: 12519], [SQLState: 66000] None None

Possible reason

One of the most common reasons for the TNS-12516 and/or TNS-12519 Oracle error being reported on Oracle 11gR2 is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS Listener become “Blocked” and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.


There are different ways to check if a database instance reaches the maximum number of processes on Oracle 11gR2. One of the quickest SQL see below:

select * from v$resource_limit where resource_name = 'processes';

If this is the case, to fix this Oracle error, increase the PROCESSES parameter value to allow the Oracle database Listener to spawn more server process as required in your environment.

It might be further investigation required though.

Update: 16-Dec-2011

Sometimes when listener throws a TNS error the number of actual processes can be below the limit.  See below why this can happen.
When the listener believes the current number of connections has reached maximum load, it may set the state of the service handler for an instance to “blocked” and begin refusing incoming client connections with either of the following errors: ora-12519 or ora-12516. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is then accepting connections connectivity resumes. The listener counts the number of connections it has established to the instance but does not immediately get information about connections that have terminated. Only when PMON updates the listener via SERVICE_UPDATE is the listener informed of current load. Since this can take as long as 10 minutes, there can be a difference between the current instance load according to the listener and the actual instance load.

If you really reached the processes limit and it stays this way for some time you will probably get the following error trying to connect:

ORA-00020: maximum number of processes (%s) exceeded

There are a couple of ‘workarounds’ for this issue … but no real solution at this time (even for Oracle 11.2) since you are not capable to connect to the DB even as SYSDBA:

– Use an existing connection with sufficient privileges (if one is logged on) to view V$SESSION / V$PROCESS and kill some sessions:
alter system kill session ‘SID, SERAL#’;

– Stop a less critical application, DB monitoring, etc.

– Kill one or more of the client connections on OS level:

* UNIX: kill -9

If none above helps and the error occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

Enjoyed this article? Please share it with others using the social site of your choice:


14 Responses to “Oracle Listener refused connection ORA-12519 troubleshooting”
  1. Gohan says: do you do a “select…” when we are not able to actually connect to a DB through any of the client?? Suggest us any methods other than this please.

  2. Kirill Loifman says:

    Often after a while you can connect. See my update above.

  3. Cindy says:

    it should work by reload the listener.

    I had experienced tns-12516 a couple of times in Oracle 11R2 env, the max-process/max-session in v$resource_limit table is far below the limited value, also when checked listener log file, the services-update is quick frequent, much less than 10min. so, i am wonder if there is other cause.

  4. Venkatesh says:

    Hey buddy it helped me a lot thanks a lot.
    it is quick and accurate solution!

  5. bw says:

    I have ora-12516 when MAX_UTILIZATION is less 30% of LIMIT_VALUE. My environment is Instant Client 11.2 ( PHP ) and Oracle and Instant Client 11.2 ( PHP ) and Oracle

  6. Kirill Loifman says:

    In this case you might face a bug 8232287 – PMON stops registering its services (ORA-12516 errors) that affects version and fixed in
    — Kirill Loifman

  7. Karl says:

    Okay, this all works fine, when you have sessions in v$session and gv$session that show what, where and who is sucking up all the processes.

    So what do you do when 1200 processes are used up, but only 33 sessions show in v$ and gv$session? In v$process, the used slots show a terminal of unknown, and program of unknown, like a zombie process. With out that information, I can not find out what is calling the database, and burning up all my processes.

    All suggestions are greatly appreciated.

  8. Kirill Loifman says:

    WIth such a situation and zombie processes, I recommend opening SR at MOS. You probably hit a bug
    — Kirill

  9. buzz_lighters says:

    I changed localhost to hostname, then this error went away!

  10. Chelo Lipe says:

    This informations was ver useful to mo!

  11. RAJA says:

    java.sql.SQLException: Listener refused the connection with the following error:
    ORA-12519, TNS:no appropriate service handler found
    The Connection descriptor used by the client was:

  12. Kirill Loifman says:

    Can be a number of reasons, also XE specific
    — Kirill

  13. Rajesh says:

    Hey Kirill
    Greetings! Recently I was helping a small scale industry to upgrade their 10g R1 database to 11g R2 for their client server environment (Developer 6i with patch 18)
    We tested the whole scenarios using Virtualbox, created Windows 2008 r2 VM, Windows 7 VM, Windows 10 VM and without any hacks the whole tests completed successfully. Trust me there were not a single hiccup anywhere!
    We moved the solution to HP GL380 g9 server with 32 GB memory and ample storage & immediately after the installation, came against “ORA-12518 Tns: Listener could not hand off client connection”, Nothing would resolve this issue, restarted the server, restarted the listener service, deleted and recreated the listener. This error was persistent whenever I tried to connect using SQL 8 client (Developer 6i) & was able to log on to database as root. Then I checked the background processes and found there were hardly 23 processes. However, I changed the processes to 450, restarted both listener and database services & the clients were able to connect normally.
    Wondering what is the real reason behind this weird behavior on a real hardware scenario!

  14. Hozone says:

    This is really useful information. It saved my day. Thanks

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email