Pages

Thursday 3 January 2013

ORA-12519: TNS:no appropriate service handler found while connecting to database.




New database connection attempts to Oracle database fail with following Oracle error





Reason:

One of the most common reasons for TNS-12519 Oracle erroris 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 that thresholds are below the configured limit, the database listener will start to accept new connections

Solution:


Check if a database instance reaches the maximum number of processes using below command.

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

eg:

SQL> col LIMIT_VALUE format a20;
col RESOURCE_NAME format a20;
col INITIAL_ALLOCATION format a15;
select * from v$resource_limit where resource_name = 'processes';SQL> SQL> SQL>

RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE
-------------------- ------------------- --------------- --------------- --------------------
processes                            299             300        300             300

1 row selected.


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 or Kill some inactive and unwanted sessions using alter system kill session ‘SID, SERAL#’;

2 comments:

  1. I doubt increasing the PROCESSES parameter is -always- going to be a sollution. I think you need to understand what is creating your current processes.

    For example, I'm using sqlldr to load a big dataset in parallel, that's causing my limit of 150 processes to be reached. I'm letting oracle decide on the level of parallelism automatically. There's already a bottleneck at the disk queue, so it's unlikely that more parallelism would increase upload speed.

    My guess here is that increasing the PROCESSES parameter would make sqlldr go for even more parallelism, causing the new and higher PROCESSES limit to be reached, again.

    The sollution would be, i guess, to somehow limit the level of parallelism of sqlldr.

    Then again, i'm not an Oracle expert, just guessing here...

    ReplyDelete
    Replies
    1. Thanks for the comment. Yeah increasing processes is not always solution. We can check inactive sessions and terminate them to allow other sessions to use required processes and with respect to sql loader, it again goes with load on the database and processed being used during that time.

      Regards,
      Sangamesh

      Delete