java - 监听器拒绝连接，并出现以下错误 : ORA-12519, TNS:未找到适当的服务处理程序

Question

我使用 Spring 和 Hibernate 并使用 Oracle 11g 作为数据库我使用了 JBoss 5。

我无法自动重新建立连接

我使用了c3p0-0.9.1.1.jar

这是我应用程序中的配置:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
         <bean id="dataSource" class = "com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
       <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>

        <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:production"/> 



        <property name="user" value="admin"/>
        <property name="password" value="admin"/>
             <!-- pool sizing -->
        <property name="initialPoolSize" value="1" />
        <property name="minPoolSize" value="1" />
        <property name="maxPoolSize" value="200" />
        <property name="acquireIncrement" value="3" />
        <property name="maxStatements" value="0" />

        <!-- retries -->
        <property name="acquireRetryAttempts" value="30" />
        <property name="acquireRetryDelay" value="1000" /> <!-- 1s -->
        <property name="breakAfterAcquireFailure" value="false" />

        <!-- refreshing connections -->
        <property name="maxIdleTime" value="180" /> <!-- 3min -->
        <property name="maxConnectionAge" value="10" /> <!-- 1h -->

        <!-- timeouts and testing -->
        <property name="checkoutTimeout" value="30000" /> <!-- 30s -->
        <property name="idleConnectionTestPeriod" value="60" /> <!-- 60 -->
        <property name="testConnectionOnCheckout" value="true" /> 
        <property name="preferredTestQuery" value="SELECT 1 FROM DUAL" />
        <property name="testConnectionOnCheckin" value="true" /> 

    </bean>
</beans>

但是我有这个错误:

com.mchange.v2.resourcepool.BasicResourcePool] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1a88493f -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:389)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:454)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:802)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
2016-04-06 10:00:09,300 INFO  [STDOUT] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0) 10:00:09,300 WARN  [BasicResourcePool:1841] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@696dbdb5 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:389)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:454)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:802)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
2016-04-06 10:00:11,925 WARN  [com.mchange.v2.resourcepool.BasicResourcePool] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@8003d4a -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:389)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:454)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:802)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
2016-04-06 10:00:11,956 WARN  [com.mchange.v2.resourcepool.BasicResourcePool] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@75354d7 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:389)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:454)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:802)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
2016-04-06 10:00:11,956 WARN  [com.mchange.v2.resourcepool.BasicResourcePool] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@7ddd60e7 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:389)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:454)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:802)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
2016-04-06 10:00:11,956 WARN  [com.mchange.v2.resourcepool.BasicResourcePool] (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@2704e3a7 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:389)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:454)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:802)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
2016-04-06 10:00:11,972 WARN  [org.hibernate.util.JDBCExceptionReporter] (http-172.30.50.21-80-11) SQL Error: 0, SQLState: null
2016-04-06 10:00:11,972 ERROR [org.hibernate.util.JDBCExceptionReporter] (http-172.30.50.21-80-11) An attempt by a client to checkout a Connection has timed out.
2016-04-06 10:00:11,972 WARN  [org.hibernate.util.JDBCExceptionReporter] (http-172.30.50.21-80-58) SQL Error: 0, SQLState: null
2016-04-06 10:00:11,972 WARN  [org.hibernate.util.JDBCExceptionReporter] (http-172.30.50.21-80-53) SQL Error: 0, SQLState: null
2016-04-06 10:00:11,972 ERROR [org.hibernate.util.JDBCExceptionReporter] (http-172.30.50.21-80-53) Connections could not be acquired from the underlying database!

我的应用程序使用以前的数据库配置可以正常工作，但我的问题是，当我的应用程序有很多连接(超过 200 个用户)时，就会出现错误，这就是我使用连接池的原因。

当我在服务器中出现此错误时，oracle 服务可以正常工作，因为我可以直接在数据库中执行查询。只是问题出在我部署在 jboss 和 oracle 中的应用程序的连接之间

在_BaseRootDAO.java中我有这个方法:

/**
 * Return a new Session object that must be closed when the work has been completed.
 * @return the active Session
 */
public Session getSession() {
    return getSession(
        getConfigurationFileName());
}

/**
 * Return a new Session object that must be closed when the work has been completed.
 * @param configFile the config file must match the meta attribute "config-file" in the hibernate mapping file
 * @return the active Session
 */
protected Session getSession(String configFile) {
    if (null != session && session.isOpen()) return session;
    else if (null != sessionFactory) {
        Session s = currentSession.get();
        if (null == s || !s.isOpen()) {
            s = sessionFactory.openSession();
            currentSession.set(s);
        }
        return s;
    }
    else {
        Session s = currentSession.get();
        if (null == s || !s.isOpen()) {
            s = getSessionFactory(configFile).openSession();
            currentSession.set(s);
        }
        return s;
    }
}

更新:

这是 Oracle 数据库中的当前设置

RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION

sessions    93  156
enqueue_locks   81  149
enqueue_resources   18  52

我尝试使用最新版本的c3p0

c3p0-0.9.5.2.jar 已安装 c3p0-0.9.1.1.jar

Answer 1

我的用例:
我正在运行与 DB(Oracle 12c) - 1 个实例的并行连接，以测试我是否从序列中获得不同的序列值。随着并行连接的增加，有时我得到了“ORA-12519，TNS:找不到适当的服务处理程序”

什么有效:

1. 进程 limit_value 已达到。我增加了它。

 ALTER system SET processes=XXX SCOPE=SPFILE;

逐渐增加XXX(例如+200左右)，因为如果您一次性将其增加到太高的值，您可能无法启动Oracle实例，因为它无法支持它(READ:内存限制，操作系统限制，...)

提交；

重新启动数据库。
(重要:不要忘记重新启动数据库，以使更改在 spfile 中生效。对于静态和动态参数，更改都会记录在 spfile 中，并且仅在下次重新启动时生效。)

验证我的更改(limit_value):

select resource_name, current_utilization, max_utilization, limit_value
from v$resource_limit
where resource_name = 'processes';

show parameters processes; // will also work

注意:您可能会注意到sessions参数也会发生变化。这是因为 session 和进程 协同工作，Oracle 将根据所选进程 limit_value 重新计算 session limit_value。