5 Replies Latest reply on May 22, 2014 4:26 PM by Skh-Oracle

    Seeking for advice on exception: java.sql.SQLRecoverableException: IO Error: Read failed: Connection timed out

    User771952-Oracle

      I am seeking advice how to identify the exceptions in using WLS datasouce.

      Fact:

      1. 1. my app is using WLS datasource
        2. my app will throw exception regularly in production env: java.sql.SQLRecoverableException: IO Error: Read failed: Connection timed out when executing a statement or setEndToEndMetrics.
        3. after checking internet forum, the explaination of above exception could be, 1. WLS will disable the connection pool after trying a couple of times. 2. Root cause maybe network or firewall in the env. 


      My question(correct me if I am not on the correct way):

      1. 1. is there way I can do diagnostic for this problem, such as output jdbc log, or something you think good to do. 
        2. does WLS has parameters I can set to control the "disabled pool" behaviour?

       

      Thanks in advance for any pointer.

        • 1. Re: Seeking for advice on exception: java.sql.SQLRecoverableException: IO Error: Read failed: Connection timed out
          Joe Weinstein-Oracle

          Show the full original stacktrace of the exception from the server log. So far the symptoms have nothing to do with WLS disabling anything. It sounds more like

          the application code did a query with a query timeout set on the statement, and the DBMS just didn't respond in time.

          • 2. Re: Seeking for advice on exception: java.sql.SQLRecoverableException: IO Error: Read failed: Connection timed out
            User771952-Oracle

            Thank Joe for input. this exception happens regularly in production env every 3 hours. 

            In server log, below exception is thrown firstly

             

            java.sql.SQLRecoverableException: IO Error: Read failed: Connection timed out

                    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1139)

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

                    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)

                    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)

                    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:9353)

                    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)

                    at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:99)

                    at oracle.xxx.xxx.db.DbProperties.getAllProperties(DbProperties.java:820)

            Caused by: java.net.NoRouteToHostException: Read failed: Connection timed out

                    at jrockit.net.SocketNativeIO.readBytesPinned(Native Method)

                    at jrockit.net.SocketNativeIO.socketRead(SocketNativeIO.java:32)

                    ..................................

             

            then followed by another exception

                    java.sql.SQLException: Connection has been administratively disabled by console/admin command. Try later. java.lang.Exception: It was disabled at Fri May 16 10:02:09 UTC 2014

                   at weblogic.jdbc.common.internal.ConnectionEnv.disable(ConnectionEnv.java:364)

                    at weblogic.jdbc.wrapper.JDBCWrapperImpl.invocationExceptionHandler(JDBCWrapperImpl.java:127)

                    at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:115)

             

            I am guessing WLS connection pool has problem to be disabled, then statement could not be executed.  What kind of reason could be? then how I can identify it.

            • 3. Re: Seeking for advice on exception: java.sql.SQLRecoverableException: IO Error: Read failed: Connection timed out
              Albin

              can you verify whether u have set oracle.jdbc.ReadTimeout  and oracle.net.CONNECT_TIMEOUT properties for the connection pool?

               

              If you have set the value for above parameters then try to increase the value.

               

              Regards

              Albin I

              http://www.albinsblog.com/

              • 4. Re: Seeking for advice on exception: java.sql.SQLRecoverableException: IO Error: Read failed: Connection timed out
                Joe Weinstein-Oracle

                The 'x'ed out code in oracle.xxx.xxx.db.DbProperties.getAllProperties(DbProperties.java:820) needs to be examined to see if it

                set a query timeout on the statement. That would explain the problem.

                 

                The message "Connection timed out when executing a statement or setEndToEndMetrics" seems to imply that the application

                code that sent this was coded in complete awareness of a timeout. By default JDBC will wait forever, so this smells like

                an application setting.

                 

                Do look at the LWS datasource configuration. The CONNECT_TIMEOUT will be irrelevant. A ReadTimeout would be a bad thing,

                but isn't as likely as if the DataSOurce has a query timeout set.

                 

                The first suspect is in the code that has been hidden, I think.

                • 5. Re: Seeking for advice on exception: java.sql.SQLRecoverableException: IO Error: Read failed: Connection timed out
                  Skh-Oracle

                  NoRouteToHostException - Signals that an error occurred while attempting to connect a socket to a remote address and port. Typically, the remote host cannot be reached because of an intervening firewall, or if an intermediate router is down.

                   

                   

                  Connection timed out : means there has been no response whatsoever.

                   

                   

                  It looks like your application is trying to execute a long query and JDBC driver is waiting for a response but doesnt get one.

                   

                   

                   

                  For you to further diagnose this issue, you can use the below Oracle Note if you have a Support contract :

                    Note:1050942.1 -- How to Trace the Network Packets Exchanged Between JDBC and the RDBMS in Release 11.2

                   

                  1.  Placing the debug ojdbc6_g.jar file in the same location where the default driver ojdbc6.jar is located.  Backup the default driver ojdbc6.jar in a different location and remove it from say for example C:\Oracle\Middleware3\wlserver_10.3\server\lib directory.

                   

                   

                   

                  2.  In the WebLogic startup script, at the top of the file, place the debug driver in the front of the CLASSPATH:

                    @REM - debug testing

                    set CLASSPATH=C:\Oracle\Middleware3\wlserver_10.3\server\lib\ojdbc6_g.jar;%CLASSPATH%

                   

                   

                   

                  3.  Place the trace JVM arguments -Djava.util.logging.config.file=C:\debug\debug.properties -Doracle.jdbc.Trace=true in the WebLogic startup script by 

                      editing the Weblogic startup commands:

                   

                   

                    @REM   the debug flags need to be placed after the -Dweblogic.Name=%SERVER_NAME% or they will be ignored

                    if "%WLS_REDIRECT_LOG%"=="" (

                      echo Starting WLS with line:

                      echo %JAVA_HOME%\bin\java %JAVA_VM% %MEM_ARGS% -Dweblogic.Name=%SERVER_NAME%  -Djava.util.logging.config.file=C:\debug\debug.properties -Doracle.jdbc.Trace=true -Djava.security.policy=%WL_HOME%\server\lib\weblogic.policy %JAVA_OPTIONS% %PROXY_SETTINGS% %SERVER_CLASS%

                      %JAVA_HOME%\bin\java %JAVA_VM% %MEM_ARGS% -Dweblogic.Name=%SERVER_NAME% -Djava.util.logging.config.file=C:\debug\debug.properties -Doracle.jdbc.Trace=true  -Djava.security.policy=%WL_HOME%\server\lib\weblogic.policy %JAVA_OPTIONS% %PROXY_SETTINGS% %SERVER_CLASS%

                    ) else (

                      echo Redirecting output from WLS window to %WLS_REDIRECT_LOG%

                      %JAVA_HOME%\bin\java %JAVA_VM% %MEM_ARGS% -Dweblogic.Name=%SERVER_NAME% -Djava.util.logging.config.file=C:\debug\debug.properties -Doracle.jdbc.Trace=true -Djava.security.policy=%WL_HOME%\server\lib\weblogic.policy %JAVA_OPTIONS% %PROXY_SETTINGS% %SERVER_CLASS%  >"%WLS_REDIRECT_LOG%" 2>&1

                    )

                   

                   

                   

                   

                  4.  Create the JDBC trace .properties file :

                   

                    .level=SEVERE

                    oracle.jdbc.level=FINEST

                    oracle.net.ns.level = FINEST

                    oracle.jdbc.handlers=java.util.logging.FileHandler

                    java.util.logging.FileHandler.pattern=D:\\JDBC\\drivers\\Logging\\log112.log

                    java.util.logging.FileHandler.limit=50000000

                    java.util.logging.FileHandler.level=ALL

                    java.util.logging.FileHandler.count=10

                    java.util.logging.FileHandler.formatter=java.util.logging.XMLFormatter

                    oracle.jdbc.diagnostics.DemultiplexingLogHandler.pattern = D:\\JDBC\\drivers\\Logging\\sqlnet_%s.log

                    oracle.jdbc.diagnostics.DemultiplexingLogHandler.limit = 50000000

                    oracle.jdbc.diagnostics.DemultiplexingLogHandler.count = 1

                    oracle.jdbc.diagnostics.DemultiplexingLogHandler.formatter = java.util.logging.XMLFormatter

                    oracle.net.ns.handlers = oracle.jdbc.diagnostics.DemultiplexingLogHandler

                   

                   

                   

                  5.  Restart the Weblogic Server after any changes to the startup script or debug.properties file.

                   

                   

                   

                   

                   

                   

                   

                  You could raise a Service Request with Oracle Support to get this issue examined.

                  1 person found this helpful