Getting up and running with Universal Connection Pool


Martin Nash (@mpnsh) kindly pointed out a mistake on the ONS configuration in the resource element below. Thanks for that!


This post was written for 11g Release 2 and Oracle Linux 5. Where applicable I have added information on how to do this with 12c Release 1 and Oracle Linux 7.2.


Oracle’s next generation connection pooling solution, Universal Connection Pool, can be a bit tricky to set up. This is especially true when a JNDI data source is to be used-most example don’t assume such a scenario. A lot of information is out there on the net, but no one seems to have given the full picture. During the research for chapter 11 of “Pro Oracle Database 11g RAC on Linux” I learned this the hard way. Since the book has been published, a few minor changes changes have been made to the software I used at the time, and those merit an update.

Please note that this article’s emphasis is to get  this example running-it is by no means meant to be secure enough for a production release! You need to harden  the setup considerably for production, but it serves well for demonstration purposes (only).


I have used a four node RAC system as the source for my data. A 2 node cluster database with service “TAFTEST” runs on nodes 1 and 2. It’s administrator-managed and the service has both nodes set aside as “preferred” nodes. The database nodes run Oracle Enterprise Linux 5.564 bit with RAC For the sake of simplicity, I used my Windows laptop to host the Tomcat instance, which is now updated to version 6.0.30. I am using apache Ant to build the application. The current stable ant build is 1.8.2. My JDK is also upgraded to the latest and greatest, version 1.6.0_23. I am using the 32bit client package to supply me with ons.jar, ojdbc6.jar and ucp.jar.

The Oracle Linux 7.2 system uses the following components:

  • Oracle Linux 7.2 64bit
  • ant-1.9.2-9.el7.noarch
  • tomcat-7.0.54-8.el7_2.noarch including tomcat-admin-webapps-7.0.54-8.el7_2.noarch for the web interface
  • Oracle client located in /u01/app/oracle/product/
  • Owned by “oracle” for convenience


Part of this excercise is to demonstrate FCF and FAN events, which means we need an Oracle client for the remote ONS configuration (please refer to chapter 11 of the RAC book for a detailed description of local vs remote ONS configurations). I downloaded the 32bit client from for Windows and installed it to c:\oracle\product\11.2.0\client_1, chosing the administrator option in Oracle Universal Installer.


Start by downloading Tomcat for your platform-I have successfully tested Tomcat on Linux and Windows. I deployed apache-tomcat-6.0.30 to c:\ for this test. Once it’s unzipped, copy the necessary JAR files from the Oracle client installation into %TOMCAT_HOME%\lib. These are ojdbc6.jar, ons.jar and ucp.jar. Next, you should set a few environment variables. To keep things simple, I edited  %tomcat_home%\bin\ and added these:

  • set JAVA_HOME=c:\program files\Java\jdk1.6.0_23
  • set JAVA_OPTS=-Doracle.ons.oraclehome=c:\oracle\product\11.2.0\client_1

I’m also interested in the final content of %JAVA_OPTS%, so I modified catalina.bat as well and added this line into the section below line 164:

echo Using JAVA_OPTS: “%JAVA_OPTS%”

If you want to deploy the application automatically you need to have a look at the tomcat manager app documentation. The default settings do not allow you to invoke the manager app in the interest of security.

In Oracle Linux 7.2 I went with Tomcat tomcat-7.0.54-8.el7_2.noarch.

This concludes the TOMCAT setup.


Ant is a build tool used for deployment and compiling the sample application I am going to adapt. Simply download the zip file (version 1.8.2 was current when I wrote this) and deploy it somewhere conveniently. Again, a few environment variables are helpful which I usually put into a file called sp.bat:

@echo off
set ant_home=c:\apache-ant-1.8.2
set path=%ant_home%\bin;%path%
set java_home=c:\program files\Java\jdk1.6.0_23
set path=%java_home%\bin;%path%

This makes building the application a lot easier. Just change into the application directory and enter sp to get set up.

For Oracle Linux 7.2 I just used the ANT build that came with the distribution, ant-1.9.2-9.el7.noarch.


My earliest contact with Tomcat was a long time ago with version 3 and since then I remember the well written documentation. The “docs” application, usually accessible as http://localhost:8080/docs has a section about the first application. It’s highly recommended to read it:


To get started, I copied the “sample” directory from %tomcat_home%\webapps\docs\appdev to c:\temp and started adapting it. First of all my sp.bat script is copied in there. With a command prompt I changed into sample and edited the build.xml file. The first major section to go over is starting in line 129. I changed it to read like this:

 <property name=""      value="ucp"/>
 <property name="app.path"      value="/${}"/>
 <property name="app.version"   value="0.1"/>
 <property name="build.home"    value="${basedir}/build"/>
 <property name="catalina.home" value="C:/apache-tomcat-6.0.30"/>
 <property name="dist.home"     value="${basedir}/dist"/>
 <property name="docs.home"     value="${basedir}/docs"/>
 <property name="src.home"      value="${basedir}/src"/>
 <property name="web.home"      value="${basedir}/web"/>

The properties to change/add are, app.version, catalina.home. If you decided it was a good idea to use the manager app, you can add the respective properties in this section.

With all this done, try to compile the application as it is:

C:\TEMP\sample>ant compile
Buildfile: C:\TEMP\sample\build.xml
Trying to override old definition of datatype resources


 [javac] C:\TEMP\sample\build.xml:301: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds

Total time: 0 seconds


That’s it-the application has been created. You can now start tomcat and try to deploy the app. Open a cmd window, change to %tomcat_home%\bin and execute startup.bat. This will start tomcat-the new window shows any problems it might have when deploying application. This window is very useful for example when it comes to troubleshooting incorrect XML configuration files.

With the application built, it is time to deploy it. In the easiest case you simply copy the war-file to CATALINA_HOME/webapps.

When pointing your browser to http://localhost:8080/ucp/ you should be greeted by the familiar tomcat sample application.


So far this hasn’t been groundbreaking at all. It’s only now that it’s getting more interesting: the JNDI data source needs to be defined and used in our code. Instead of messing around with resources and res-ref configuration in the global %tomcat_home%\conf directory it is advisable to add the context to the application.

Back in directory c:\temp\sample create a new directory web\META-INF. Inside META-INF you create a file “context.xml” which takes the JNDI data source definition. This is the original version. For 12c and auto-ONS you don’t need to add the ons configuration.

 description="UCP JNDI Connection Pool"
 sqlForValidateConnection="select 1 from DUAL" />

It really is that simple to implement-if it only were equally simple to find how to do this… The next step is to modify the Servlet to reference the JNDI data source. The code for the servlet is shown below-it’s basically the existing servlet code amended with the JNDI and JDBC relevant code. It actually does very little: after looking the JDNI name up it grabs a session from the pool and checks which instance it is currently connected to. It then releases all resources and exits.

 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * See the License for the specific language governing permissions and
 * limitations under the License.
package mypackage;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.ValidConnection;
import javax.naming.*;
import java.util.Enumeration;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public final class Hello extends HttpServlet {
  public void doGet(HttpServletRequest request, HttpServletResponse response)
    throws IOException, ServletException 
    PrintWriter writer = response.getWriter();
    writer.println("<title>Sample Application Servlet Page</title>");
    writer.println("<body bgcolor=white>");

    writer.println("<table border=\"0\">");
    writer.println(" <td>");
    writer.println("<img src=\"http://localhost/sample/images/tomcat.gif\">");
    writer.println("</td> ");
    writer.println(" <td>");
    writer.println(" <h1>Sample UCP Application Servlet</h1> ");
    writer.println("<p>This is the output of a servlet that is part of");
    writer.println("the Hello, World application. Adapted to use UCP</p>");
    writer.println("</td> ");
    writer.println("</tr> ");
    writer.println("</table> ");
    // this is the UCP specific part!
    writer.println(" <h1> UCP </h1> ");

    Context ctx = null;
    Connection conn = null;
    PoolDataSource pds = null;

    try {
      // look the data source up
      ctx = new InitialContext ();
      Context envContext = (Context) ctx.lookup("java:/comp/env");

      javax.sql.DataSource ds =
        (javax.sql.DataSource) envContext.lookup ("jdbc/UCPPool");

      // as documented on OTN
      // application-development/planned-unplanned-rlb-ucp-tomcat-2265175.pdf
      pds = (PoolDataSource)ds;

      writer.println("Got the datasource");
      conn = pds.getConnection();
      writer.println("<p>Successfully obtained a session from the pool</p>"); 
      // do some work - not much though
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("select 'Hello World from ' " + 
        " ||sys_context('userenv','instance_name') from dual");
      while ( {
        writer.println("<p>Your session is established against instance " + 
          rs.getString(1) + "</p>");
      conn = null;
    } catch (SQLException sqle) {
      try {
        if (conn == null || !((ValidConnection) conn).isValid()) {
          writer.println(" <pre>" + sqle + "</pre> ");
          String fcfInfo = ((OracleJDBCConnectionPoolStatistics)
            System.out.println("FCF information: " + fcfInfo);
      } catch (Exception e) {}
    } catch (Exception e) {
    } finally {

Done! Now let’s compile the code and deploy it to Tomcat before testing. The most common problem I get with the code is a JNDI error, stating the “jdbc/UCPPool” is not defined. This can happen in 2 cases:

  • You have a typo in the resource definition, in which case the context really doesn’t exist (it’s case sensitive)
  • You have non-compatible line breaks in the context.xml file. In this case I’d try having all the contents of the file in 1 line (use “J” in vi to join lines together)


You should now see a number of sessions as user “scott” against the database-query gv$session for username “SCOTT” and you should see the result of your hard work.


14 thoughts on “Getting up and running with Universal Connection Pool

  1. Simon Haslam

    Nice write-up Martin… I see you’re using SCAN listener(s) too :)

    One question though: you say “Part of this excercise is to demonstrate FCF and FAN events, which means we need an Oracle client for the remote ONS configuration” – why? For the ucp.jar file? Your UCP config points to remote ONS daemons so you don’t need a local ONS server, for example.

    1. Martin Post author

      Hi Simon, thanks for passing by.

      I have added the directive because the documentation says you need to set java -Doracle.ons.oraclehome=%ORACLE_HOME% (to stay in the windows scenario) variable for the application. I have modifed JAVA_OPTS in the startup.bat file to have that included. Documentation in this context is the Universal Connection Pool for JDBC Developer’s Guide chapter 8 under “configuring ONS”.

  2. Pingback: Log Buffer #213, A Carnival of the Vanities for DBAs | The Pythian Blog

  3. Pingback: Log Buffer #213, A Carnival of the Vanities for DBAs · Database, MySql, Oracle, SQL and more

  4. Pingback: Getting up and running with Universal Connection Pool (via Martin’s Blog) « lava kafle kathmandu nepal

  5. vas

    I always get out-of-memory exception when I try to DEBUG my UCP connectionpool code in eclipse. And running Jconsole showed me that for every active connection it is creating 3 and some times 4 or 2 UCP-worker-threads and 3 Timer Threads and some 8-9 Normal Threads. In my Junit am putting the code to sleep for quite sometime after getting a connection. And during this wait for every 30 sec or so it is creating a fresh batch of the above mentioned Thread ARMY.

    Any suggestion or pointers is very much appreciated.


    1. Martin Bach Post author

      Hi Vas,

      I’m afraid I can’t advise on the use of UCP in eclipse. My development environment is vim in a putty session. Anybody else coming across this thread please feel free to comment!

      Best regards,


      1. vas

        Hi Martin,

        Thanks a lot for the quick response. This also happens without eclipse too. While the code is at sleep(), I see the spike in UCP-worker-Threads every 30 sec.


  6. javaclaus

    Great article. I have started implementing Oracle UCP for my spring web app. Do you have any knowledge on getting UCP logging working for a web app. Currently, I use log4j so any help would be appreciated.

    1. Martin Bach Post author

      Hi there,

      I’m not a java developer and for that reason concentrated on the technology I know. I should have an example of UCP logging somewhere on the blog as well, but you have to consult the documentation for Websphere and Weblogic I’m afraid. It must be possible somehow, and my Tomcat example uses log4j as well.


  7. Hans-Peter Störr

    Thanks for the great article!
    Does anyone also have the problem that when shutting down tomcat it complains bitterly that the application has started loads of threads UCP-worker-thread-* and Timer-* but has failed to stop them? I haven’t found anything on how to avoid this. Thanks!


  8. Pingback: My Fight with FCF « ORAganism

  9. Pingback: Getting up and running with UCP and Application Continuity « Martins Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s