java - tipo - listener refused the connection with the following error ora 12520

ORA-12516, TNS: el oyente no pudo encontrar el manejador disponible

Question

Mi 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)

Mi clase de conexión de db común:

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;

    }

}

Cada transacción remito a esta clase y hago mis operaciones de búsqueda y CRUD. ¿Hay algún problema con mi código?

Answer 1

Abriste muchas conexiones y ese es el problema. Creo que en su código, no cerró la conexión abierta.

Un rebote de la base de datos podría resolver temporalmente, pero volverá a aparecer cuando realice una ejecución consecutiva. Además, debe verificarse el número de conexiones concurrentes a la base de datos. Si se ha alcanzado el parámetro de procesos DB máximo, este es un síntoma común.

Cortesía de este hilo: https://community.oracle.com/thread/362226?tstart=-1

Answer 2

Solucioné este problema con la línea de comandos de SQL:

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

Reiniciar la base de datos.