Oracle pool configuration in Tomcat
I've spent several hours "googling" for any information on how to configure Tomcat datasource that would be using Oracle pool implementation that comes along with the JDBC driver in ojdbc14.jar (classes12.jar). In the end I had to resort to means that I would rather not tell anyone about.
Anyway, here is a sample configuration that actually works:
<Resource name="jdbc/orapool" auth="Container" type="oracle.jdbc.pool.OracleDataSource"
debug="0"/>
<ResourceParams name="jdbc/orapool">
<parameter>
<name>factory</name>
<value>oracle.jdbc.pool.OracleDataSourceFactory</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:oracle:thin:@oracle-database-host:1521:ORCL</value>
</parameter>
<parameter>
<name>connectionCachingEnabled</name>
<value>true</value>
</parameter>
<parameter>
<name>connectionCacheName</name>
<value>dummy1</value>
</parameter>
<parameter>
<name>connectionCacheProperties</name>
<value>{MinLimit=0, MaxLimit=2, InitialLimit=0}</value>
</parameter>
<parameter>
<name>user</name>
<value>scott</value>
</parameter>
<parameter>
<name>password</name>
<value>tiger</value>
</parameter>
</ResourceParams>
There are several tricks you should know:
- To create pool you must set connectionCachingEnabled=true, otherwise the datasource would be serving physical connections
- I would highly recommend that you set connectionCacheName for each datasource. And if you want separate datasources to be separate connection pools those names have to be unique. This is because Oracle implementation uses static singleton to manage "named" pools. connectionCacheName property references one of those "named" pools. So if two datasources have the same connectionCacheName they would actually be the using the same pool instance. If connectionCacheName is left blank, Oracle would generate a pseudo-random name and would create a new pool. The problem comes when you reload the application. If connectionCacheName is blank Oracle would create a new pool every time application is reloaded thus leaking resources.
- connectionCacheProperties do have to be specified the way they are in the example. You must not forget "{" and "}" and that there must be no spaces between opening bracket and the first property; likewise between closing bracket and the last property. You should also know that there must be no spaces between property names and that the property pairs are separated with ", ". Note the space after the comma.
Despite this painful configuration procedure you should be rewarded in the end. Here are some benefits:
- Unlike DBCP and C3P0, Oracle pool can check if connection is alive without running a SQL through it.
- Unlike DBCP and C3P0 Oracle pool sticks to connection pool limits and timeouts.
- Unlike C3P0 Oracle pool will not go into re-connection frenzy should database become unavailable.
- You can take advantage of Oracle proprietary failover mechanisms
- Unlike DBCP Oracle pool can be reset. So you can leverage that functionality using Probe ;)
Unfortunately this is not a blog yet (I'm still working on it), but if you would like to comment on this how-to please send me a message and I will publish your comment here.
by Vlad Ilyushchenko, 30 January 2006