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.jarandons.jarfrom Oracle client 10.2.0.3 or above - Java SDK 1.5:
ojdbc5.jarandons.jarfrom Oracle client 11.1.0.6 or above - Java SDK 1.6:
ojdbc6.jarandons.jarfrom 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:
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″ />
Hi Dhahri,
I have been away for sometime from anything Oracle related. So, I am unable to comment at the time being.
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
Hi Johnny,
I also get this problem time to time. It seems to be an Oracle 10g issue. 11g does not have this problem.
It is a very good article. Will let you know, after, i tiry it out.
Thanks Again,
Sri
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?
Hello Ken,
Unfortunately no, I have no experience with DataGuard.
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.
Hi, we are exactly following the Tomcat JDBC guideline for opening connections in JSP.
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.
You seem to have entered the wrong username/password combination.
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)))” username=”ipServUsr” password=”” />
Thanks
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.
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?
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.
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.
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.
Does the pool configured replaced the DBCP pool? Do I still need it?
Hi Jiann,
After creating this pool, you will not need the DBCP pool, and also it will be better if you completely remove it.
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.
Hi Jiann,
I edited the post to include the ONS configuration on the client side. Please read the section starting with the creation of
ons.configfile.Hi Kerem,
Thank you, I am now able to connect. Your information is definitely valuable, save me for sure the hassle. Appreciate.
Rgds,
Jiann
I am glad that I could help.
Cheers.