Vivek Agarwal’s Portal/Java Blog

An IBM Gold Consultant’s weblog about IBM, Lotus, WebSphere, J2EE, IT Processes, and other IT technologies

Firewall dropping Oracle database connections in WebSphere connection pool?

Posted by Vivek Agarwal on July 4, 2009


We recently switched one of our client’s WebSphere Application Server and WebSphere Portal based infrastructure to a new physical environment. During our testing we found that we were getting stale connection exceptions in SystemOut.log and in the application logs. On digging a little deeper, our team tracked it down to the fact that the Cisco firewall was dropping our Oracle database connections after they had been inactive for a certain amount of time. When our team discussed the issue with the network team, they were essentially told that all was good with the network infrastructure. The application team tweaked some of the connection pool settings but that only helped alleviate the issue slightly and we started encountering performance issues. Eventually, we figured out a solution that works!

The solution that we ended up with was to work around this issue on the Oracle end. We modified the sqlnet.expire_time parameter in sqlnet.ora on the Oracle 10g server down from the default “30” (30 minutes) to “1” (1 minute).

The explanation on why this worked

The parameter SQLNET.EXPIRE_TIME is used to specify the time interval, in minutes, to send a probe to verify that client/server connections are active. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server so that it can free up server-side resources that are not in use.

A side-effect of the probe is that there is TCP-IP activity between the client and the server each time the probe is executed, and the firewall designates the link as an active one. By lowering the probe interval down to a minute, we managed to fool the firewall into leaving the database connections in the connection pool alone, and not terminate them even though the client may not execute a query using the connection for an extended period of time.

I am sure you can tweak the value of this parameter to determine the optimal value for your firewall and situation; dropping down the sqlnet.expire_time to a minute is probably too radical and redundant in reality. Even though the probe generated by this setting is small, it still does generate network traffic that may be meaningful in some situations.

Advertisements

13 Responses to “Firewall dropping Oracle database connections in WebSphere connection pool?”

  1. […] Firewall dropping Oracle database connections in WebSphere … […]

  2. Doug said

    Was one of the other options you tried just to allow the DataSource Connection Pool to drop down to zero held connections? That’s what we’ve always done to prevent these kinds of problems, but I have no idea which approach has better performance implications.

  3. Guido said

    We faced exactly the same problem and solve it like Doug mentioned.

  4. […] Firewall dropping Oracle database connections in WebSphere connection pool? « Vivek Agarwal’s Por… (tags: portal websphere database) […]

  5. Vivek Agarwal said

    Doug,

    You are absolutely right that one of the options that we could have gone with is to change the connection pool configuration settings of unusedTimeout and reapTime such that the combo was lower than the firewall idle timeout setting, while ensuring that the minConnections was set to zero. The downside to this is that depending on usage patterns and firewall idle timeout setting, you may end up incurring the cost of creating new database connections too often impacting performance and defeating the purpose of the connection pool. The upside is that this solution works with any database (and is not dependent on an Oracle-specific setting), and in fact can be used on any kind of “object pool” that must maintain remote connections through a firewall.

    Thanks for raising this point!

    -Vivek.

  6. Richard said

    We had the exact same issue on a setup that uses Tomcat in conjunction with the Apache DBCP component. The solution was to use the validationQuery configuration parameter in pair with testOnBorrow=true. So when a connection is interrupted by the Firewall. The next time the application needs to use a connection from the pool, the pool validate the connection before releasing it. If the connection is dead it simply remove it from the pool and use another existing one or create a new one if need be.

    The validationQuery parameter value on Oracle that we are using “Select 1 from dual”

  7. abt12 said

    Great information here. Thanks for taking the time to post guys. I first tried the validationQuery and testOnBorrow parameters from the post above but I still had issues after the change. The web application experiencing the problems is compiled and we don’t have the source. It could be a coding issue. I’m now going to try setting SQLNET.EXPIRE_TIME to 10 minutes in an attempt to prevent any “stale” connections.

  8. Owen said

    I have same problem.
    This post is perfect solution.

    Thank you!

  9. Dan Rizzi said

    Is it possible to address this by adjusting settings on firewall side? I’ve been poking around and found that some have made reference to disabling inspections of sqlnet.

    Thanks

  10. Himanshu said

    Hi
    I have a issue with firewall, while communicating b/w 2 Webshere portal server.Please let me know if any body have any idea how to surpaas firewall b/w these two portal server. Is there any configuration at portal server where we can mention to allow particular server through firewall.

  11. […] https://vivekagarwal.wordpress.com/2009/07/04/firewall-dropping-oracle-database-connections-in-websph… […]

  12. shashi said

    not woking

  13. cloud computing services,cloud computing hosting,clouding computing,cloud computing service,cloud computing platform,cloud computing server,cloud computing security,cloud computing solution,cloud computing technologies,cloud based solution,cloud serv…

    […]Firewall dropping Oracle database connections in WebSphere connection pool? « Vivek Agarwal’s Portal/Java Blog[…]…

Sorry, the comment form is closed at this time.

 
%d bloggers like this: