Hi all
We have a web application that reads some data connecting to db. Sometimes the connection hangs up, so we have to restart Tomcat, to re-create new connection.
Our environment is:
-Tomcat 4.1.27 on Sun OS 5.9, with jdk 1.4.0
-jdbc driver ojdbc14.jar (version Oracle JDBC Driver version - 9.0.2.0.0)
-Oracle server 8.1.7.4.0, on Sun OS 5.9

-Connection pools are created via JNDI, so inside server.xml:
<Resource name="jdbc/glprod" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/glprod">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>

-Other parameter are user, password, maxActive, maxIdle, maxWait. Are there any other useful parameter?
-Using toad (select * from v$session) I notice that db connection are missing (when we have this error)
-SQL statement are ONLY select, no complicated insert or else ...
-When connection hangs up, we have inside log file this line:
java.sql.SQLException: Eccezione IO: Socket closed
-On Oracle server I haven't find any error log or alert

Does anyone can help me?
Thanks
Aldo Funicelli

can you tell me what is/are the SQL that you running while this connection is open.
Have you tried with any other jdbc driver?

1. In your code check, if you close the connection after you executed SQL.
2. Check if you can start tomcat to debug jdbc or datasource. For example, in OC4J, you can start the container like this to debug jdbc: java -Djdbc.debug=true -Ddatasource.verbose=true -jar oc4j.jar

Hope this helps,
Senthil.

Thanks for the answer
1) I use ibatis SQLMaps as framework to connect to DB. This library is responsible to release all connection.
2) I use oracle jdbc thin client ojdbc4.jar (Driver version - 9.0.2.0.0)
The error is present only on our Unix server. The test machine is Windows 2000 (with Tomcat 4.1.27), and db connection to the same Oracle server remais always active.

I set the log level (of log4j) to debug.
I hope to find something new ...
Thanks

We have a firewall between Tomcat and Oracle server.
The exception Socket closed may be caused by this?

Thanks

We have a firewall between Tomcat and Oracle server.The exception Socket closed may be caused by this?Thanks

You may try to run a small UNIX shell script to check the connections to Oracle from the machine where Tomcat runs. For example, this might work:

while true ;
date
netstat -an | grep 1522 # or the port where Oracle listens
sleep 60
done

If you run this script on the UNIX machine, you are going to know if the connections get phisycally closed (at TCP/IP level). Maybe, you can find a pattern (for example: all the sockets gets closed 10 minutes after they are openend) and see if there are problems with the time-out policy of tomcat or Oracle.

Jose Ramon Huerga
http://www.terra.es/personal/jrhuerga/

Firewall can be the cause. I have seen one such situation earlier...

The appserver used to keep sending the keepalive message to Oracle, but the firewall sitting between them was dropping that message. Now, Oracle since it did not recieve that message, was closing the connections without notifying the appserver. Due to this, the appserver was assuming the connections are alive, whereas they were not thus resultin in a hung application. It was corrected by changing some setting in the firewall (related to keepalive, but I am not aware of the details of the changes made).

Aldo, I have a very similar situation where all our connections to Oracle on a linux box go dead after 2 - 4 hours. This happens with connections to Tomcat and another standalone Java app.

Have you found a solution to this problem?

I appreciate any help.