Are there any firewalls between JBoss and the DB?
No. We do not have a firewall between app server and DB servers. But, the DB servers are all AIX boxes while app servers (JBoss) are all Enterprise Linux 5.
I also have noticed a pattern of 3 to 4 hours of idleness after which the connections become stale/unusable and resule in mentioned "Closed Connection" exception.
Please let me know if you need more information.
In your datasource?
I have not tried it. But with this, what will I get? I guess I may get better logging why the connections are going down? Could you please help me to understand the advantage of such a config?
I'm under the impression that the implementation does a NOOP ping operation against the DB before handing off the connection from the connection pool (and making a fresh connection if the old one has gone sour)
Okay. Let me add this configuration to the pool and observe it for another day. I am also going to changing the value for IDLE timeout from Zero to 3.
It sounds like Oracle is configured to close unused connections after a certain period of time. Thus later when an app asks for a connection, the connection is closed. The valid-connection-checker-class-name and check-valid-connection-sql (which takes an SQL statement that it uses to verify the connection) aare used to first check the connection before handing it to a requesting app. If the check fails, JBoss AS closes the connection and restablishes it. Note that this check is done every time an app asks for a connection.
Setting the idle-timeout-minutes should cause JBoss AS to close the connection long before Oracle times the connection out, so that should will work for the 5 optional connections (the ones between min and max connections) but it will not work for the 15 min connections - those never get released.
but it will not work for the 15 min connections - those never get released.
This is wrong. If any connection is idle for the idle-timeout-minutes, then the app server will close that connection. If the connection count goes below min-pool-size, then the app server will reestablish the connection. For example, if the app server is completely idle, then every idle-time-minutes it will close and recreate the min-pool-size connections. If you set idle-timeout-minutes to 3, then every three minute the 15 connections will be closed and recreated.
I recommend that you think about the timeout and min pool size to come up with something reasonable. For example, if the app is only busy during the day but you leave the app server running overnight, then I would set a very low min pool size (e.g. 2) and set a resonable long timeout size (e.g. 30). Then during the day the app server will most likely maintain the 20 max pool size connections because it will be busy, and then at night it will drop back to 1 connection which it will reestablish every 30 minutes. Then you will not need any of the connection checkers.
I have configured background-validation-millis to 60000 and added the <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql> as well.
But still in JBoss 5.1 which is running on Linux pointing to AIX Oracle DB, if the server remains idle for some time i am getting the exception.
Can any one confirm that org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker is same as that of above mentioned configurations ?
ie. configuring OracleValidConnectionChecker is equivalent to configuring background-validation-millis.