programing

ORA-12516, TNS: 리스너가 사용 가능한 핸들러를 찾을 수 없음

lastmoon 2023. 3. 28. 22:28
반응형

ORA-12516, TNS: 리스너가 사용 가능한 핸들러를 찾을 수 없음

내 오류:

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)

공통 DB 연결 클래스:

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;

    }

}

이 클래스를 참조하는 모든 트랜잭션과 취득 및 CRUD 작업을 수행합니다.제 코드에 무슨 문제가 있나요?

당신은 많은 연결망을 열었고 그게 문제예요.당신의 코드에서 열린 연결을 닫지 않은 것 같습니다.

데이터베이스 바운스는 일시적으로 해결될 수 있지만 연속 실행 시 다시 나타납니다.또한 데이터베이스에 대한 동시 연결 수도 확인해야 합니다.최대 DB 프로세스 파라미터에 도달한 경우 이는 일반적인 증상입니다.

이 스레드 제공:https://community.oracle.com/thread/362226?tstart=-1

sql 명령줄에서 이 문제를 해결했습니다.

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

데이터베이스를 재시작합니다.

언급URL : https://stackoverflow.com/questions/20742794/ora-12516-tnslistener-could-not-find-available-handler

반응형