32

My error:

java.sql.SQLException: Listener refused the connection with the following error:

ORA-12516, TNS:listener could not find available handler with matching protocol
stack
The Connection descriptor used by the client was:
//10.2.5.21:9001/XE

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:261)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:
414)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtensio
n.java:35)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSou
rce.java:297)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java
:221)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java
:165)
        at utilityService.DB_util.setOracleConnectionActive(DB_util.java:99)
        at utilityService.DB_util.getRecPreparedAuthentication(DB_util.java:124)

My common db connection class:

package utilityService;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.pool.OracleDataSource;


public class DB_util {
    String propValue = "";
    ResultSet rec = null;
    Statement stm = null;
    PreparedStatement pre_stm = null;
    CallableStatement call_stm = null;
    Connection conn1 = null;

    /**
     * Constructure to get oracle connection
     */
    public DB_util() {

        Util util=new Util();
        propValue=util.getFilePathToSave();
        //propValue = Util.propValue;// get oracle connection
        setOracleConnectionActive();
    }

    /**
     * Close all oracle connections and result sets.
     */
    public void setOracleConnectionClose() {
        try {
            if (conn1 != null || !conn1.isClosed()) {
                if (rec != null) {
                    rec.close();
                    rec = null;
                }
                if (stm != null) {
                    stm.close();
                    stm = null;
                }
                if (pre_stm != null) {
                    pre_stm.close();
                    pre_stm = null;
                }
                if (call_stm != null) {
                    call_stm.close();
                    call_stm = null;
                }
                conn1.commit();
                conn1.close();
                conn1 = null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * return a result set according to sql sent
     * 
     * @param SQL
     * @return
     */
    public ResultSet getRec(String SQL) {
        try {
            setOracleConnectionActive();
            stm = conn1.createStatement();
            rec = stm.executeQuery(SQL);

            return rec;
        } catch (Exception ex) {
            ex.printStackTrace();
            return rec;
        }

    }

    /**
     * Activate oracle connection
     */
    private void setOracleConnectionActive() {
        try {
            if (conn1 == null || conn1.isClosed()) {
                OracleDataSource ods = new OracleDataSource();
                if (propValue != null) {
                    ods.setURL(propValue);
                }
                conn1 = ods.getConnection();
                System.out.println("DB connection CONNECTED......");
                conn1.setAutoCommit(false);
            }
        } catch (Exception ex) {
            //setOracleConnectionActive();
            ex.printStackTrace();
            System.out.println("DB connection FAILED......");
        }
    }

    /**
     * send prepared result set with user authenticate
     * 
     * @param SQL
     * @param strInputUserMobile
     * @param strInputUserName
     * @param strInputUserPassword
     * @return
     */
    public ResultSet getRecPreparedAuthentication(String SQL,
            String strInputUserMobile, String strInputUserName,
            String strInputUserPassword) {

        try {
            setOracleConnectionActive();
            pre_stm = conn1.prepareStatement(SQL);
            pre_stm.setString(1, strInputUserMobile);
            pre_stm.setString(2, strInputUserName);
            pre_stm.setString(3, strInputUserPassword);
            rec = pre_stm.executeQuery();

            return rec;
        } catch (Exception ex) {
            ex.printStackTrace();
            return rec;
        }

    }

    /**
     * insert sql to db which is send as a sql
     * 
     * @param SQL
     * @return
     */
    public int insertSQL(String SQL) {
        int output = 0;
        try {
            setOracleConnectionActive();
            stm = conn1.createStatement();
            output = stm.executeUpdate(SQL);
            conn1.commit();
            output = 1;

        } catch (Exception ex) {
            try {
                conn1.rollback();
                output = 0;
            } catch (SQLException e) {
                e.printStackTrace();
                output = 0;
            }
            ex.printStackTrace();

        }
        return output;

    }

    /**
     * Send a callable statement according to sent sql
     * 
     * @param SQL
     * @return
     */
    public CallableStatement callableStatementSQL(String SQL) {

        int output = 0;
        try {
            setOracleConnectionActive();
            call_stm = conn1.prepareCall(SQL);

        } catch (Exception ex) {
            try {
                conn1.rollback();
                output = 0;
            } catch (SQLException e) {
                e.printStackTrace();
                output = 0;
            }
            ex.printStackTrace();

        }
        return call_stm;

    }

}

Every transaction I refer this class and do my fetching & CRUD operations. Is there any issue with my code?

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Priyan RockZ
  • 1,605
  • 7
  • 40
  • 68
  • 2
    What does `util.getFilePathToSave()` give you - sounds like it would be a file path rather than a JDBC URL, but then you wouldn't be getting an error from the listener. Do you get this error immediately, or after it's performed some operations - maybe you aren't closing your connections and the listener/DB is running out of processes? – Alex Poole Dec 23 '13 at 12:01
  • thanks dear Alex.its gives jdbc url.i'll check my connection's open & close – Priyan RockZ Dec 25 '13 at 05:00

4 Answers4

37

You opened a lot of connections and that's the issue. I think in your code, you did not close the opened connection.

A database bounce could temporarily solve, but will re-appear when you do consecutive execution. Also, it should be verified the number of concurrent connections to the database. If maximum DB processes parameter has been reached this is a common symptom.

Courtesy of this thread: https://community.oracle.com/thread/362226?tstart=-1

Alex W
  • 37,233
  • 13
  • 109
  • 109
Devaraj Mahesan
  • 458
  • 1
  • 7
  • 15
  • FYI, you can restrict max number of connections with connection-pool libraries like [hikari-cp](https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing#the-formula). Basic formula is `(number_of_cores * 2 + 1)`. `* 2` to take advantage of blocking nature of read/write to Disk. – prayagupa Dec 17 '18 at 01:11
22

I fixed this problem with sql command line:

connect system/<password>
alter system set processes=300 scope=spfile;
alter system set sessions=300 scope=spfile;

Restart database.

Jeff Miller
  • 1,424
  • 1
  • 10
  • 19
  • I did this but also checked how my application behaved. In my case, a connection pool was being used to use objects thru a dblink. It a explicit commit / session close is not performed, the process remains and the limit is reached. Leaving a minimumIdle=0 in the pool solved the real issue. – Alfabravo Jul 22 '16 at 14:53
  • 7
    May the lady of luck bless you for `"Restart database"`. – AlikElzin-kilaka Nov 21 '19 at 11:34
0

For me the problem was not the number of connexions, but the "matching protocol" part. Changing the ojdbc version solved the problem.

Sorin Dumitru
  • 99
  • 1
  • 3
0

A connection pooling solution can prevent these intermittent connection establishment failures. This is from "High Performance Java Persistence book" by Vlad Mihalcea

Bharat
  • 386
  • 5
  • 20