Friday, March 9, 2012

ORA-12516 TNS:listener could not find available handler with matching protocol

When ORA-12516 raise , you can see  below error in you application log

Caused by: org.jboss.util.NestedSQLException: Could not create connection; -
nested throwable: (java.sql.SQLException: Listener refused the connection with
the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol
stack
.......
......
       at
org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSour
ce.java:106)
        at
net.sf.hibernate.connection.DatasourceConnectionProvider.getConnection(Datasour
ceConnectionProvider.java:59)
        at
net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:292)
        ... 78 more

Background of the error:


One of the most common reasons for the TNS-12516 and/or TNS-12519 errors being reported 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.

By way of instance registration, PMON is responsible for updating the listener with information about a particular instance such as load and dispatcher information. Maximum load for dedicated connections is determined by the PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE information varies according to the workload of the instance. The maximum interval between these service updates is 10 minutes.

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.

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

Solution

Increase the processes and sessions value to allow the listener to spawn more server process as required in your environment.

2 comments:

  1. Logged in as admin, I increased the number of processes with the command:

    ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;

    But I still got the same exception and Oracle ORA-12516 error message.

    You talk about increasing the number of sessions ?

    ReplyDelete