Spring connection pooling issue

Question

I'm not experienced in Java and Spring. I try to write a program that uses JdbcTemplate for Data access. I use DBCP pooling, here it is:

<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
    <property name="url" value="jdbc:oracle:thin:@192.168.2.7:1521:xe" />
    <property name="username" value="manifesto" />
    <property name="password" value="manifesto" />
    <property name="initialSize" value="2" />
    <property name="maxActive" value="4" />
</bean>

My application perform several update operations and then throws an exception:

    7053 [SenderThread-0] DEBUG org.springframework.jdbc.datasource.DataSourceUtils  - Fetching JDBC Connection from DataSource
Exception in thread "SenderThread-0" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
192.168.2.7:1521:xe
)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:572)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:875)
    at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.update(SimpleJdbcTemplate.java:249)
    at com.talutek.manifesto.dao.firestorm.dao.spring.MessageItemsTableDaoImpl.update(MessageItemsTableDaoImpl.java:52)
    at com.talutek.manifesto.lib.MessageItemMngr.updateItem(MessageItemMngr.java:115)
    at com.talutek.manifesto.gw.SenderThread.run(SenderThread.java:42)
    at java.lang.Thread.run(Thread.java:619)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
192.168.2.7:1521:xe
)
    at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
    at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
    at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    ... 9 more
Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
192.168.2.7:1521:xe

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:496)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:490)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:202)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:465)
    at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
    at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
    ... 13 more

When I change pool settings(pool size), the application can run some more times but it then crashes. I think the problem is related to pooling but I couldn't solve it.

Any suggestions?

I'd recommend that you remove the database url, username and password information from your post. — Cameron Skinner
– Cameron Skinner, Commented Nov 1, 2010 at 21:59
@Cameron: Quite right! Anyone could connect to that 192.168.2.7 address. — skaffman
– skaffman, Commented Nov 1, 2010 at 22:03
@Skaffman: No need to be sarcastic. Removing usernames and passwords is a no-brainer, and it's not going to hurt to remove the URL as good practice. — Cameron Skinner
– Cameron Skinner, Commented Nov 1, 2010 at 22:04

stefan.m · Accepted Answer · 2012-11-16 15:59:48Z

5

The same error occurred for me when lots of threads accessed the database simultaneously, and I had a separate DBCP BasicDataSource and a separate Spring JdbcTemplate for each thread.

By making both the BasicDataSource and the JdbcTemplate a singleton shared by all threads, I could avoid this error. This is also what SpringSource recommends.

answered Nov 16, 2012 at 15:59

stefan.m

2,0324 gold badges23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aravind Yarram · Accepted Answer · 2010-11-01 21:59:39Z

1

A google search would have helped: http://www.dba-oracle.com/sf_ora_12519_tns_no_appropriate_service_handler_found.htm

Few suggestions: Use app server pools if possible Use c3p0 than DBCP

answered Nov 1, 2010 at 21:59

Aravind Yarram

80.5k49 gold badges239 silver badges335 bronze badges

1 Comment

xyzt Over a year ago

As said there: "Also, on Oracle DBA Forums, there is information referring to ORA-12519. Here, it is pointed out that ORA-12519 can be caused by low "processes" values, which can be resolved by increasing he DB parameter, "parallel_max_servers". Also, ORA-12519 can be thrown because of DB and client versions which do not match.", it maybe related to parallel_max_servers param. I will try when I get home.Thanks

Olayinka · Accepted Answer · 2015-02-03 20:59:21Z

0

Use oracle.jdbc.pool.OracleDataSource

answered Feb 3, 2015 at 20:59

Olayinka

2,8552 gold badges27 silver badges43 bronze badges

1 Comment

Markus W Mahlberg Over a year ago

Which would eliminate the use of dbcp, which is clearly intended.

Collectives™ on Stack Overflow

Spring connection pooling issue

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related