Ошибка ora 12516

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?

Источник

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.

Источник

November 13, 2020

Sometimes You can get ” ORA-12516 TNS:listener could not find available handler with matching protocol stack  ” error.

ORA-12516 TNS:listener could not find available handler

Details of error are as follows.

ORA-12516 TNS:listener could not find available handler with matching protocol stack

Cause: None of the known and available service handlers for the given SERVICE_NAME support the client's

protocol stack: transport, session, and presentation protocols.




Action: Check to make sure that the service handlers (e.g. dispatchers) for the given SERVICE_NAME are registered

with the listener, are accepting connections, and that they are properly configured to support the desired protocols.

ORA-12516 TNS:listener could not find available handler with matching protocol stack

This error is related with out of available Oracle processes.

Oracle Processes Parameter

To solve this error, you should increase the PROCESSES parameter as follows.

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 1500
SQL>
SQL>
SQL> alter system set processes=2000 scope=spfile;

System altered.

SQL>

Restart database after this operation.

Or you can create a static listener and start it as follows.

Add the following listener descriptions to the listener.ora file under $ORACLE_HOME/network/admin.

Check your Hostname, Port, DB Name and fill them correctly.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.63.34)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER = 
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = MSDB)
(ORACLE_HOME = /u01/app/db_home_18c )
(SID_NAME = MSDB1 )
)
)



[[email protected] ~]$ lsnrctl start LISTENER

Do you want to learn more details about RMAN, then Click this Link and read the articles.

 3,955 views last month,  8 views today

Источник

The above solution only masks the problem. After almost die, I found a solution that really works. For some reason, Oracle driver running with hibernate OracleDialect (or Oracle10gDialect) needs extra parameters.

I was mapping my jdbc connection through Spring using 

  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
    <property name="url" value="jdbc:oracle:thin:@localhost:1521:mydb" />
    <property name="username" value="mydbuser" />
    <property name="password" value="12345" />
   </bean>

Then, I changed it to use JNDI:

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="java:comp/env/jdbc/mydb" />
</bean>

With this information in my context.xml (Tomcat):

<Resource name="jdbc/mydb"
        type="javax.sql.DataSource"
        auth="Container"
        driverClassName="oracle.jdbc.driver.OracleDriver"
        url="jdbc:oracle:thin:@localhost:1521:mydb"
        username="mydbuser"
        password="12345"
        maxActive="20"
        maxIdle="10"
        maxWait="-1"
        validationQuery="select 1 from dual" />

This solves my problem. Probably one of the following configurations maxActive, maxIdle or maxWait provides to Oracle a needed value to close the session and return it to the pool. I keep this way in my system, as I had a pending task to migrate to JNDI, however, if you do not want to use JNDI, try to search similar values in hibernateProperties that might work.

Obs.: JNDI can be used in Jetty adding tag of class org.eclipse.jetty.plus.jndi.Resource to jetty.xml.

Источник

1. Описание проблемы

Сообщается об ошибке при удаленном входе в базу данных:

При возникновении проблемы перейдите в файл предупреждений базы данных alert.log, чтобы проверить и обнаружить, что ошибка отображается следующим образом:

2. Причина проблемы

Сообщение об ошибке обычно появляется, когда запуск процесса завершается неудачно, когда экземпляр Oracle создает некоторые вспомогательные фоновые процессы (например, подпроцесс m00x из mmon или подпроцесс W00x и т. Д.), И существует много возможностей возникновения ошибки, включая недостаточные ресурсы экземпляра Oracle, Недостаточно ресурсов операционной системы и т. Д. Одним из наиболее распространенных является то, что использование процесса экземпляра экземпляра достигает верхнего предела настройки параметра во время фактической работы базы данных, что вызывает проблемы.

Через представление v $ resource_limit вы можете запросить, достиг ли процесс верхнего предела в течение жизненного цикла экземпляра.

База данных не может войти в систему из-за ошибки (sys as sysdba не может войти в систему), поэтому вам нужно сначала отключить занятый процесс, остановить несколько котов, чтобы освободить соединение, а затем вы можете подключиться.

После входа в базу данных запросите представление v $ resource_limit, результаты запроса будут следующими:

По результатам запроса вы можете увидеть, что максимальное количество MAX_UTILIZATIOON, используемых процессом, достигло 150, что ограничено LIMIT_VALUE. Весьма вероятно, что общее количество процессов достигнет верхнего предела и базе данных не удастся создать новый фоновый вспомогательный процесс.

Теперь вы можете перезапустить службу tomcat, запускать один за другим, проверять значение процесса и сеанса каждый раз, когда вы запускаете tomcat.

select count (*) from v $ process; // Запрос количества используемых процессов
 select count (*) from v $ session; // Запрос количества сеансовых соединений

Если вы обнаружите, что рост процесса достигнет верхнего предела, это означает, что это действительно причина, по которой база данных сообщает об ошибке, и вам необходимо изменить верхний предел параметров процесса и сеанса.

3. Решение

Изменить верхний предел параметров процесса и сеанса

altert system set processes = 250 scope = spfile;
alter system set sessions = 380 scope = spfile;

 PS: значение процессов и сессий, требования к официальному документу Oracle: сессии = процессы * 1,5 + 5

Перезапустите службу oracle после модификации, запустите все службы tomcat и повторно запросите занятость процесса и сеанса, а количество используемых процессов и сеансов меньше максимального предела.

ps: Но также возможно, что процесс базы данных внезапно достигает максимального значения в определенное время. Ситуация, с которой я столкнулся, заключается в том, что параметр не изменяется, и его можно использовать нормально после перезапуска tomcat

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка ora 12500
  • Ошибка opvl ippon smart winner 2000
  • Ошибка ora 12154 как исправить
  • Ошибка opt fail
  • Ошибка ora 06592