8 Replies Latest reply on Aug 16, 2013 4:24 PM by Joe Weinstein-Oracle

    DBConfig.Properties Setting Idle Connection Timeout

    user5138197

      I have a java Application written several years ago. The original developer was drawn and quartered long before I got here. I am under the directive to move the application from one DMZ to another. In the new DMZ it appears that any connections through the firewall that stay idle for more then x minutes are summarily closed. This has put a damper on my db connection pool and when the app gets around to trying to use a connection it blows up. To resolve this (besides trying to teach the firewall guys that their firewall really IS shutting idle ports which they firmly deny) I am attempting to to reconfigure the Connection Pool to not keep ANY idle connections that have been idle for more then 5 minutes. However I'm a windows geek in a penguins world and drowning fast. My Currect DBConfig looks something like.

       

      #PROD ORACLE APPS

      ora_dburl=jdbc:oracle:thin:@10.1.2.3:1525:PROD

      ora_user=someencryptedstring

      ora_password=someotherencryptedstring

      ora_driver=oracle.jdbc.driver.OracleDriver

      ora_total=3

      ora_min=1

      ora_checkquery=select SYSDATE from dual

       

      I assume its something like.

       

      #PROD ORACLE APPS

      ora_dburl=jdbc:oracle:thin:@10.1.2.3:1525:PROD

      ora_user=someencryptedstring

      ora_password=someotherencryptedstring

      ora_driver=oracle.jdbc.driver.OracleDriver

      ora_total=3

      ora_min=0

      ora_SomethingThatSpecifiesMaxIdleTime=300

      ora_checkquery=select SYSDATE from dual

       

      Any help would be appreciated.

      Thanks

        • 1. Re: DBConfig.Properties Setting Idle Connection Timeout
          Joe Weinstein-Oracle

          Who is the developer/vendor for your connection pool? Their documentation

          should be clear on how to configure to synchronously and asynchronously

          test idle connections (which keeps them looking busy so they are not killed)

          and/or replacing dead old connections... It's not a driver-level behavior you want.

          • 2. Re: DBConfig.Properties Setting Idle Connection Timeout
            user5138197

            Joe,

                 Wish I knew wish I had such documentation but then I wouldnt be on this forum hoping someone might recognize it and help.

             

             

            • 3. Re: DBConfig.Properties Setting Idle Connection Timeout
              Joe Weinstein-Oracle

              If you alter the config data to put in an incorrect URL, can you get a full stacktrace of the error

              when the code tries to make a connection? That stacktrace will include the names of some

              of the pool classes. That may help us identify what products you're using...

              • 4. Re: DBConfig.Properties Setting Idle Connection Timeout
                user5138197

                It's a shared connection string so If I break it for one I'd break it for several apps. This though is the stack trace that has prompted me to attempt to timeout the connections in my pool hopefully it will give you the specifics your looking for?

                 

                08/08/2013 15:06:00 EDT, cs.shared.security.OSSecurity[resetEmail]  **DEBUG: sSQL ={Some sql I removed}

                java.sql.SQLException: Io exception: Connection reset

                    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)

                    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:231)

                    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:345)

                    at oracle.jdbc.driver.OracleStatement.open(OracleStatement.java:717)

                    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2605)

                    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:457)

                    at cs.shared.security.OSSecurity.resetPassword(OSSecurity.java:1036)

                    at actions.ForgetPasswordAction.perform(ForgetPasswordAction.java:18)

                    at ActionServlet.service(ActionServlet.java:30)

                    at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

                    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)

                    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

                    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)

                    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

                    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

                    at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)

                    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)

                    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

                    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

                    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)

                    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

                    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)

                    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)

                    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

                    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

                    at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

                    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

                    at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)

                    at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)

                    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)

                    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)

                    at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)

                    at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)

                    at java.lang.Thread.run(Thread.java:534)

                • 5. Re: DBConfig.Properties Setting Idle Connection Timeout
                  Joe Weinstein-Oracle

                  There seems to be no connection pooling in use at all. And if the firewall kills a connection you

                  haven't used for some time, you will get an exception like this.

                  • 6. Re: DBConfig.Properties Setting Idle Connection Timeout
                    user5138197

                    Joe,

                     

                    And?

                    So?

                    Thus?

                    Therefore?

                     

                    I feel like there should have been more to that "answer" then to point out that the exception you asked for is an exception and that the pooling isn't configured. Isn't that all kinda covered already by the fact that I was here looking for help with an error I had because the idle timeout wasn't set.

                     

                    Skip it 3 days and 3 replies in, your the only one that seems interested in trying to help but all we've done so far is to reestablish in different words what I already know without even a glimmer of what I need to do to get the idle timeout set. I'm just going to write a keep alive app and move on thanks for the attempts anyway.

                    • 7. Re: DBConfig.Properties Setting Idle Connection Timeout
                      Joe Weinstein-Oracle

                      Well, I am trying to help you, but was confused because you'd mentioned/suspected that

                      your db connection pool had a problem. The stack trace seems to imply there is no db

                      connection pool involved.. So, clearing that up, am I right that your problem is that you

                      make a JDBC connection, and after some time of non-use of that connection, a firewall

                      will kill it out from under you. You want to stop that happening. 'Idle timeout' is not going

                      to help. That is typically a pool property that kills or retracts connections from the application

                      if the application doesn't use them for a long time. You instead want a keepalive, right?

                      • 8. Re: DBConfig.Properties Setting Idle Connection Timeout
                        Joe Weinstein-Oracle

                        If you can get you code to add this property value to your connection request to the driver,

                        this may help:

                         

                        oracle.net.keepAlive=true