4

While testing the Oracle XE connection establishing mechanism I bumped into the following issue. Although connections are closed on each iteration, after 50-100 connections Oracle starts throwing intermittently the following exception:

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 oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
    at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:157) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
    at com.vladmihalcea.book.high_performance_java_persistence.jdbc.connection.OracleConnectionCallTest.test(OracleConnectionCallTest.java:57) [test-classes/:na]

The test can be found on GitHub:

for (int i = 0; i < callCount; i++) {
    try {
        long startNanos = System.nanoTime();
        try (Connection connection = dataSource.getConnection()) {
        }
        timer.update(System.nanoTime() - startNanos, TimeUnit.NANOSECONDS);
        sleep(waitMillis);
    } catch (SQLException e) {
        LOGGER.info("Exception on iteration " + i, e);
    }
}

If I try to open/close connections with a 35 ms wait step, everything works fine. If I lower the wait to 10 ms the exception starts to be thrown from tim to time.

One possible reason could be explained by this article: http://www-01.ibm.com/support/docview.wss?uid=swg21603472

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.

  • 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-1251"

I wanted to know if this is some sort of bug or is it simply just how Oracle is designed to work.

Update

On Oracle 11g Enterprise Edition, it works just fine so it's an XE limitation.

Fix

Using connection pooling is probably the best way of fixing this issue, which also reduces the connection acquisition time and levels-up traffic spikes.

7

It's not a bug. This behaviour is expected. If you need to increase the connections, just change the processes limit:

SQL> show parameter processes
processes                            integer     150

SQL> alter system set processes = <integer> scope=[...];
SQL> shutdown immediate;
SQL> startup;

-- Either using pfile or spfile, you'll need to bounce the db, because processes parameter is static.

And for completeness sake, it worths saying that when in a production environment and you need to increase processes limit, you may also need to increase correlated parameters, such as transactions and sessions (derived from processes). You can query v$resource_limit to determine current limits and values in use:

SQL> select * from v$resource_limit where resource_name in ('processes', 'sessions', 'transactions');
  • But running this code would only require one process only, because the connection is closed before being opened again. Increasing the process count would be required by the concurrency requirements of some application, right? It seems strange to require 150 processes for a single threaded app as this simple test case. – Vlad Mihalcea Aug 12 '15 at 17:25
  • But there are also background process running. While submitting your code against Oracle database, open a sqlplus windows and monitor the processes count, and their respective active tasks. Queries to monitor: 1 - select * from v$resource_limit where resource_name = 'processes'; 2 - select count(1) from v$process; 3 - select pname, username, program from v$process... That way, we'll have a better picture of what's going on. Note: the number of processes also differ in shared vs dedicated servers. – GuilhermeMesquitaX Aug 12 '15 at 18:20
  • Before running the test: select count(1) from v$process says there are 29 processes. Running the test says there are 30 processes, as expected. The max processes is set to 100, so it's something broken in the PMON connection close notification mechanism. – Vlad Mihalcea Aug 12 '15 at 18:25
  • 1
    there is no 'scope=pfile' syntax. – miracle173 Aug 13 '15 at 3:15
  • @miracle173, that's right. Thanks for correcting. – GuilhermeMesquitaX Aug 13 '15 at 11:53

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.