Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle RAC 10gR2, UPC, tomcat and jdbc drivers

RoberJul 30 2012 — edited Nov 20 2014
Hello, I have an oracle RAC 10gR2 SE (10.2.0.3) with 3 nodes on Suse Linux 10. I had various java web applications with tomcat 6.X that it's use dbcp to connect to oracle. Sometimes, I had several problems with my oracles nodes, and some nodes reboot out planned. The most time when an node was reboot (out planned) the java application hangs and I have to restart the tomcat.

In the server.xml on the tomcat, we had the follow:
    <Resource auth="Container" 
	driverClassName="oracle.jdbc.driver.OracleDriver"  
	maxActive="50"  maxIdle="10" maxWait="5" removeAbandoned="true" removeAbandonedTimeout="60" 
	type="javax.sql.DataSource" name="jdbc/TrewaDS" 
	username="TREWA16" password="" 
	url="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.8.250)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.8.236)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SRV_WANDA)))" 
	/>
Because of that, I'm looking for internet and I read that ucp uses FCF and is better that dbcp in that cases. Then, I decided to change the dbcp pool for ucp pool. For that, I replacethe ojdbc14.jar for odjbc6.jar, and add ons.jar and ucp.jar in the classpath of tomcat (tomcat/common/lib). In the server.xml on tomcat, I replace my datasource for the follow:
<Resource name="jdbc/TrewaDS"      
	auth="Container"     
	factory="oracle.ucp.jdbc.PoolDataSourceImpl"     
	type="oracle.ucp.jdbc.PoolDataSource"     
	description="FCF Trewa16"     
	connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"     
	connectionWaitTimeout="30"     
	minPoolSize="5"     
	maxPoolSize="25"     
	inactiveConnectionTimeout="20"     
	timeoutCheckInterval="60"     
	fastConnectionFailoverEnabled="true"     
	onsConfiguration="nodes=172.22.8.249:6200,172.22.8.243:6200,172.22.8.235:6200"     
	user="TREWA16"      
	password=""     
	url="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.8.250)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.8.236)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SRV_WANDA)))" 
	connectionPoolName="trewaConnectionPool"      
	validateConnectionOnBorrow="true"     
	sqlForValidateConnection="select 1 from DUAL" /> 
And I execute the follow in the database:
EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'SRV_WANDA', goal => DBMS_SERVICE.GOAL_THROUGHPUT, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);
Then I can connect correctly with oracle. My applications looks fine. However, various minutes later, in the catalina.out (tomcat's logs) appear the follow:
[2012-07-05 08:45:59,157] trewa.util.Log ERROR:  (trewa.bd.ConexionTrewa - ) - Se ha producido una excepción al obtener la conexión: oracle.ucp.UniversalConnectionPlException: Todas las conexiones de Universal Connection Pool están en uso
[2012-07-05 08:45:59,158] trewa.util.Log ERROR:  (trewa.bd.trapi.trapiui.TrAPIUIOraImpl - ) - java.lang.NullPointerException
        at trewa.util.TrUtil.createPreparedStatement(TrUtil.java:74)
        at trewa.bd.trapi.trapiui.TrAPIUIImpl.recorrerFaseActExp(TrAPIUIImpl.java:4108)
        at trewa.bd.trapi.trapiui.TrAPIUIImpl.obtenerFaseActualExpediente(TrAPIUIImpl.java:3989)
        at es.juntadeandalucia.plataforma.expediente.ExpedienteTrewa.getFaseActual(ExpedienteTrewa.java:277)
        at es.juntadeandalucia.plataforma.actions.expediente.ConsultaExpedienteAction.accesoExpediente(ConsultaExpedienteAction.java:95)
        at sun.reflect.GeneratedMethodAccessor392.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
In the message, says thar all connections in Universal Connection Pool already are in use. Then I increase the limit of maxPoolSize from 25 to 100. The java web applications have 400 users aprox. When I increase the limit, don't appear the errors, but then my nodes increase the memory and swap, oracle send alerts that memory are in 99% (I know that I have to buy more ram, but for now can't do it). However if I use dbcp oracle don't sends me any alert of memory. I suppose that with ucp I have to stablish very connections and don't remove any.

How can I configure the ucp? Should I use ucp or dbcp? Can I configure dbcp for avoid the applications hangs when a node is reboot?

anyone can help me??
Thanks in advanced.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 28 2012
Added on Jul 30 2012
4 comments
1,511 views