ORA-12516, TNS:listener could not find available handler

Question

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?

Answer 1

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