Oracle Connection Pooling

Owned by PatrickB
Last updated: Apr 26, 2007 by Andrew PetroVersion comment

Updating Jar Files

If you update the ojdbc14.jar in your uportal lib be sure to update the one in $TOMCATHOME/common/lib at the same time. Failure to do so can lead to row lock contention inside of Oracle.

Connection Pooling with Oracle JDBC Drivers

  1. Copy ojdbc14.jar to $TOMCATHOME/common/lib
  2. Configure /properties/uPortal.xml:
<Context path="/uPortal" docBase="uPortal" crossContext="true">
  <!-- Turn off tomcat session persistance -->
  <Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false">
    <Store className="org.apache.catalina.session.FileStore"/>
  </Manager>

  <Resource name="jdbc/PortalDb" auth="Container" type="oracle.jdbc.pool.OracleConnectionCacheImpl"
    description="Portal Database"/>
    <ResourceParams name="jdbc/PortalDb">
      <parameter> <name>factory</name><value>org.apache.naming.factory.BeanFactory</value></parameter>
      <parameter><name>driverType</name><value>thin</value></parameter>
      <parameter><name>networkProtocol</name><value>tcp</value></parameter>
      
      <!-- Fill in your database information here -->
      <parameter><name>serverName</name><value>your.oracle.edu</value></parameter>
      <parameter><name>databaseName</name><value>UPORTAL</value></parameter>
      <parameter><name>portNumber</name><value>1521</value></parameter>
      <parameter><name>user</name><value>uportal</value></parameter>
      <parameter><name>password</name><value>changeme</value></parameter>
      <!-- leave these as they are for now -->
      <parameter><name>maxLimit</name><value>150</value></parameter>
      <parameter><name>cacheScheme</name><value>3</value></parameter>
    </ResourceParams>

  <!-- Disables restart persistence of sessions -->
  <Manager pathname=""/>

</Context>

Explaining cacheScheme

3 is DYNAMIC_SCHEME

In this default scheme, you can create new pooled connections above and beyond the maximum limit, but each one is automatically closed and freed as soon as the logical connection instance that it provided is no longer in use. (As opposed to the normal scenario when a pooled connection instance is finished being used, where it is returned to the available cache.)

The other schemes are FIXED_RETURN_NULL_SCHEME (returns null if there are no more connections) and FIXED_WAIT_SCHEME (waits until a connection is available).