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.