Supporting Oracle FCF in Tomcat

We use Tomcat with Apache HTTPD in our company. And as a database backend, we use a two node Oracle Real Application Cluster (RAC).

We were having stale connection problems when a RAC node was behaving abnormally, because the JDBC pool which was created by Tomcat was not getting any notification about it. So I started searching for a way to take advantage of Oracle’s Fast Connection Failover (FCF) mechanism. FCF is superior to Transparent Application Failover (TAF) as it is event based and supports load balancing across the RAC nodes. It is also superior to having no failover mechanism whatsoever. 🙂

FCF leverages Oracle Notification Service (ONS) to get information about database events. With the help of ONS, a JDBC pool which has FCF enabled can see if a RAC node is up or down and can act accordingly. To be able to use the method I am about to show, you will have to have a minimum Oracle RAC version of 10.2.0, because Oracle versions prior to that do not support “Remote ONS”, which is an essential part of this configuration.

To have a working Oracle connection pool in your Tomcat server, first you must have the necessary JAR files in your Tomcat classpath. Also you must use the appropriate JAR files for your JVM. If you are using Java SDK 1.5 or 1.6, you will have to get the JAR files from Oracle Client 11.1.0.6 or above. If you don’t use the appropriate JAR files with your JVM, you will most probably have severe problems. Pick one of the following configurations:

  • Java SDK 1.4: ojdbc14.jar and ons.jar from Oracle client 10.2.0.3 or above
  • Java SDK 1.5: ojdbc5.jar and ons.jar from Oracle client 11.1.0.6 or above
  • Java SDK 1.6: ojdbc6.jar and ons.jar from Oracle client 11.1.0.6 or above

I am using Tomcat 6.0.20 and Java SDK 1.6 as of this writing. And I have ojdbc6.jar and ons.jar from Oracle client 11.1.0.7.

Copy these two JAR files to /common/lib directory of your Tomcat server. If one of these files does not exist in your environment, your pool will not work.

Now, create a file named ons.config in the directory $ORACLE_HOME/opmn/conf/ and add the following to that file:

localport=6100
remoteport=6200
nodes=rac1:6200,rac2:6200

You will override the settings written in this file in the JDBC Pool in your server.xml file. Oracle just needs to see this file to get Remote ONS working in Tomcat.

Add the following to your Tomcat startup script:

export ORACLE_HOME="Path to your Oracle Home Directory"
export CATALINA_OPTS="$CATALINA_OPTS -Doracle.ons.oraclehome=$ORACLE_HOME"

Now your environment is complete. To get FCF working, you have to create a special JDBC pool in your server.xml. You can create the pool in both GlobalNamingResources or your web application’s own context. The advantage of creating the pool globally gives you the ability to use the connection in every web application defined in your Tomcat server. I will create the pool globally in this example.

Edit your server.xml and add the following into GlobalNamingResources:


Let’s explain the important directives in this configuration:

  • name: The name of the pool to be used when getting a connection from it.
  • ONSConfiguration: Remote ONS configuration which is used for getting notifications from the RAC. Important: The IP addresses or hostnames to use here should point to the main IP addresses of the RAC nodes, not the VIP addresses.
  • fastConnectionFailoverEnabled: Enable FCF mechanism.
  • implicitCachingEnabled: Enable implicit caching. This is a requirement of FCF, without implicit caching, FCF will not work.
  • connectionCacheProperties: Here, you can set the connection cache limits. To get a thorough explanation of these variables, refer to Implicit Connection Caching Page at Oracle.
  • connectionCacheName: The name of the cache. If you don’t set a name, Oracle will create a unique name everytime the cache is created, which is not recommended. Also if you create more than one JDBC pool (for example, if you have two user schemas to connect on Oracle) be sure to give a unique name for each of those pools, because this name is used on server side (database side).
  • url: Be careful to use the VIP addresses of RAC nodes in your url, not the main IP addresses.

For all web applications to see the newly created pool, you can add the following to the global context.xml of your Tomcat server:

Restart your Tomcat server and you are done. You now have a FCF enabled JDBC pool. I suggest you to read the following documents thoroughly:

Fast Connection Failover
Implicit Connection Caching

You may also like...

25 Responses

  1. Jiann says:

    Does the pool configured replaced the DBCP pool? Do I still need it?

    • Kerem says:

      Hi Jiann,

      After creating this pool, you will not need the DBCP pool, and also it will be better if you completely remove it.

      • Jiann says:

        Hi Karem,

        I am unable to use due to failure to setup the ONS, and I am using tomcat similarly to yours. Can give me some pointers how do you setup the ONS? I asked my DBA and he said the ONS at the DB side has already setup, but I believe on the client side also requires this, am I right? Any help will be very much appreciated.

  2. Mike says:

    Just what I was looking for, thanks. BTW, how did you figure out the names of the attributes on the Resource element that the OracleDataSource would process? I did some searching around but I didn’t see it documented anywhere.

    I have this working in code so it will be nice to move it to my Tomcat config.

    • Kerem says:

      Hi Mike,

      I had tried to get this working for nearly a week. Some bits from Oracle documentation, some bits from forums, there were lots of pieces in the puzzle, but at last I managed to make it work. 🙂

      I remember to have found the attribute names in Oracle documentation, but it has been some time, so I don’t exactly remember which documentation page that was.

  3. Mike says:

    Also, I noticed that you used “(LOAD_BALANCE = yes)” in your JDBC URL. From my understanding, you only need this if you’re not using runtime Load Balancing Advisory. LOAD_BALANCE will just round robin the RAC nodes from the client but the advisory support can take into account server load when creating a connection.

    If you already have ONS working, the advisory stuff is easy (I think). Checkout the Universal Connection Pool document around page 51:
    http://download-west.oracle.com/docs/cd/B28359_01/java.111/e10788.pdf

    Sound right to you?

    • Kerem says:

      I checked the document and saw that we may not have set up Load Balancing Advisory. I will examine it with our DB admin to see if we can sort this out. We have been using this URL since our RAC was setup and configured by people from Oracle, so I directly passed that one. Thanks very much for pointing it out.

  4. gtcol says:

    Hi Kerem
    I read your article, it is very well described for Tomcat users. I’ve Weblogic v10.3, jdk 1.6, jdbc6_g.jar with Oracle RAC 10.2.0.4 on RHEL4. We recently had issue with production rac, App server continuously thre stack thread error. We don’t have the fastConnectionFailoverEnabled=”true”
    clause in our jdbc pool file. Does it mean we haven’t enabled FCF in our RAC? Is FCF defaulted to true?
    Following is our jdbc pool file element:


    60
    0
    1
    0
    200
    10
    600
    0
    1000
    0
    60
    jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.21.61)(PORT=10521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.21.64)(PORT=10521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.21.67)(PORT=10521)))(CONNECT_DATA=(SERVICE_NAME=ipServ)))
    nodes=192.168.21.61:6200,192.168.21.64:6200,192.168.21.67:6200
    HawkeyeLog” driver=”oracle.jdbc.driver.OracleDriver” url=”jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.21.61)(PORT=10521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.21.64)(PORT=10521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.21.67)(PORT=10521)))(CONNECT_DATA=(SERVICE_NAME=ipServ)))” onsConfig=”nodes=192.168.21.61:6200,192.168.21.64:6200,192.168.21.67:6200″ username=”ipServUsr” password=”” />

    Thanks

    • Kerem says:

      Hi, as far as I know, if you don’t have fastConnectionFailoverEnabled=”true”, it will default to false and you won’t be able to use FCF for this pool. Also, you must enable Implicit Caching or FCF will definitely not work. I have never worked with WebLogic, but the directives should be the same.

  5. ASR says:

    I try to setup exactly but im getting below exception.

    java.sql.SQLException: User credentials doesn't match the existing ones
            at oracle.jdbc.pool.OracleImplicitConnectionCache.validateUser(OracleImplicitConnectionCache.java:296)
            at oracle.jdbc.pool.OracleImplicitConnectionCache.defaultUserPrePopulateCache(OracleImplicitConnectionCache.java:216)
            at oracle.jdbc.pool.OracleImplicitConnectionCache.(OracleImplicitConnectionCache.java:198)
            at oracle.jdbc.pool.OracleConnectionCacheManager.createCache(OracleConnectionCacheManager.java:298)
            at oracle.jdbc.pool.OracleDataSource.cacheInitialize(OracleDataSource.java:412)
            at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:393)
            at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:179)
            at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:157)
            at org.apache.jsp.Testfanpool_jsp._jspService(Testfanpool_jsp.java:73)
    

    any help in this will be greatly appreciated.

  6. ASR says:

    Thanks Kerem , actually i realized i was not connection oracle connection pool datasource that problem is addressed, but when the DB is shutdown its not able to failover smoothly.

    Can you please share your sample JSP code.

  7. Ken says:

    Before I go home-brewing from your examples, I’d like to ask if you’ve had any experience applying this (or a similar) approach against an Oracle DataGuard 10g environment…no load-balancing, fast connection failover enabled?

  8. srinivasan says:

    It is a very good article. Will let you know, after, i tiry it out.

    Thanks Again,

    Sri

  9. Kerem:
    Great article, the connection pool has been favorable to me, yet recently I have been running into this error.

    java.sql.SQLException: Invalid or Stale Connection found in the Connection Cache
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:490)
    at oracle.jdbc.pool.OracleImplicitConnectionCache.getConnection(OracleImplicitConnectionCache.java:403)
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:374)
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:178)
    at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:156)

    My Resource attributes are as such:
    connectionCacheName=”CXCACHE-pb_28″
    connectionCacheProperties=”{MaxStatementsLimit=5, MinLimit=1, MaxLimit=100, ValidateConnection=true, InactivityTimeout=10, ConnectionWaitTimeout=10}”
    connectionCachingEnabled=”true”
    maxActive=”10″
    maxIdle=”1″
    minIdle=”1″
    removeAbandoned=”true”
    validationQuery = “select 1 from dual”
    testOnBorrow = “true”

    Any idea on why this is happening?

    Thanks,
    Johnny

  10. dhahri says:

    Hi Kerem

    unfortunately I have the same issue in 11g, can any help me please
    Here my resource conf

    driverClassName=”oracle.jdbc.OracleDriver”
    maxActive=”1000″
    maxIdle=”300″
    minIdle=”2″
    maxWait=”500″
    connectionCacheName=”Cachecswebppf”
    connectionCachingEnabled=”true”
    fastConnectionFailoverEnabled=”true”
    connectionProperties=”oracle.jdbc.ReadTimeout=30000″ />

  11. dhahri says:


    dhahri:

    Hi Kerem
    unfortunately I have the same issue in 11g, can you help me please or any one has solution please
    Here my resource conf
    driverClassName=”oracle.jdbc.OracleDriver”
    maxActive=”1000″
    maxIdle=”300″
    minIdle=”2″
    maxWait=”500″
    connectionCacheName=”Cachecswebppf”
    connectionCachingEnabled=”true”
    fastConnectionFailoverEnabled=”true”
    connectionProperties=”oracle.jdbc.ReadTimeout=30000″ />

  1. August 3, 2013

    […] 1521))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = SERVICE)))" /> Ref: http://keremerkan.net/posts/supporting-oracle-fcf-in-tomcat/ Spring Datasource Configuration: <beans xmlns="http://www.springframework.org/schema/beans&quot; […]

Leave a Reply

Your email address will not be published. Required fields are marked *