8 Replies Latest reply on Feb 16, 2011 4:15 PM by alwu-Oracle

    OraclePool connection gets timed out

    725104
      Hello.

      We are trying to use the OraclePool in the Jena adapter. We create it in the initialize method of a Spring bean

           java.util.Properties oraclePoolProperties = new java.util.Properties();
           oraclePoolProperties.setProperty("MinLimit", "5"); // the cache size is 5 at least
           oraclePoolProperties.setProperty("MaxLimit", "40");
           oraclePoolProperties.setProperty("InitialLimit", "5"); // create 5 connections at startup
           oraclePoolProperties.setProperty("InactivityTimeout", "1800"); // seconds
           oraclePoolProperties.setProperty("AbandonedConnectionTimeout", "900"); // seconds
           oraclePoolProperties.setProperty("MaxStatementsLimit", "10");
           oraclePoolProperties.setProperty("PropertyCheckInterval", "60"); // seconds
           
           //this.oraclePool = new OraclePool(oracleConexionUrl, null, null, prop, "OracleSemConnPool");
           String[] tokensConexionUrl = oracleConexionUrl.split("[:/@]");
           logger.debug(tokensConexionUrl[3] + " - " + tokensConexionUrl[4]);
           this.oraclePool = new OraclePool(oracleConexionUrl, tokensConexionUrl[3], tokensConexionUrl[4],
                                                                  oraclePoolProperties, "OracleSemConnPool");
           logger.debug("Done creating OraclePool");     
      ......
      Oracle oracle = oraclePool.getOracle();
      // do stuff
      oracle.dispose();

      Inemdiately after the creation of the pool, a connection is created correctly.

      However, an hour later, with no use of any other connection, when a thread tries to get a Jena Oracle connection with the pool, it gets a SQLException: Connection timed out

      Are we missing some configuration?
        • 1. Re: OraclePool connection gets timed out
          alwu-Oracle
          Hi,

          Which jdbc library are you using?

          Zhe
          • 2. Re: OraclePool connection gets timed out
            725104
            ojdbc5.jar

            Manifest-Version: 1.0
            Ant-Version: Apache Ant 1.6.5
            Created-By: 1.5.0_17-b02 (Sun Microsystems Inc.)
            Implementation-Vendor: Oracle Corporation
            Implementation-Title: JDBC
            Implementation-Version: 11.2.0.1.0
            Specification-Vendor: Sun Microsystems Inc.
            Specification-Title: JDBC
            Specification-Version: 4.0
            Main-Class: oracle.jdbc.OracleDriver
            sealed: true

            Name: oracle/sql/converter/
            Sealed: false

            Name: oracle/sql/
            Sealed: false

            Name: oracle/sql/converter_xcharset/
            Sealed: false
            • 3. Re: OraclePool connection gets timed out
              725104
              The exception is this:

              java.lang.RuntimeException: Oracle constructor:
                   at oracle.spatial.rdf.client.jena.Oracle.<init>(Oracle.java:138)
                   at oracle.spatial.rdf.client.jena.OraclePool.getOracle(OraclePool.java:230)
                   at org.fundacionctic.ogd.data.service.OracleJenaService.createOracleConnection(OracleJenaService.java:144)
                   at org.fundacionctic.ogd.data.service.OracleJenaService.getConexion(OracleJenaService.java:165)
                   at org.fundacionctic.ogd.data.D2RDataSource.updateData(D2RDataSource.java:52)
                   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                   at java.lang.reflect.Method.invoke(Method.java:597)
                   at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:276)
                   at org.springframework.scheduling.support.MethodInvokingRunnable.run(MethodInvokingRunnable.java:76)
                   at org.springframework.scheduling.timer.DelegatingTimerTask.run(DelegatingTimerTask.java:66)
                   at java.util.TimerThread.mainLoop(Timer.java:512)
                   at java.util.TimerThread.run(Timer.java:462)
              Caused by: java.sql.SQLException: Error de E/S: Connection timed out
                   at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:866)
                   at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
                   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
                   at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1469)
                   at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:389)
                   at oracle.spatial.rdf.client.jena.Oracle.<init>(Oracle.java:125)
                   ... 13 more
              Caused by: java.net.SocketException: Connection timed out
                   at java.net.SocketInputStream.socketRead0(Native Method)
                   at java.net.SocketInputStream.read(SocketInputStream.java:129)
                   at oracle.net.ns.Packet.receive(Packet.java:282)
                   at oracle.net.ns.DataPacket.receive(DataPacket.java:103)
                   at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230)
                   at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
                   at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
                   at oracle.net.ns.NetInputStream.read(NetInputStream.java:85)
                   at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122)
                   at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78)
                   at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179)
                   at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
                   at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
                   at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
                   at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
                   at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
                   at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:853)
                   ... 18 more
              • 4. Re: OraclePool connection gets timed out
                alwu-Oracle
                Hi,

                I am trying to reproduce now. There are some SQL*Net parameters we can change if the problem is caused by time out.

                Thanks,

                Zhe Wu
                • 5. Re: OraclePool connection gets timed out
                  alwu-Oracle
                  Hi,

                  I did the following test and did not get a timeout after 1 hour.
                  I am increasing the wait to 2 hours and see if I can reproduce it.
                  If you get a chance, could you please run the following code yourself?

                  ...
                  OraclePool oraclePool = new OraclePool(jdbcUrl, user, password,
                  oraclePoolProperties, "OracleSemConnPool");
                  psOut.println("Done creating OraclePool");

                  {
                  Oracle oracle = oraclePool.getOracle();
                  {
                  GraphOracleSem gos = new GraphOracleSem(oracle, modelName);
                  psOut.println("model created using oracle " + oracle);
                  gos.close();
                  }
                  oracle.dispose();
                  }

                  Thread.currentThread().sleep(1000);
                  psOut.println("sleep 1 second");
                  {
                  Oracle oracle = oraclePool.getOracle();
                  {
                  GraphOracleSem gos = new GraphOracleSem(oracle, modelName);
                  psOut.println("model created using oracle " + oracle);
                  gos.close();
                  }
                  oracle.dispose();
                  }

                  Thread.currentThread().sleep(3600000);
                  psOut.println("sleep 3600 second");
                  {
                  Oracle oracle = oraclePool.getOracle();
                  {
                  GraphOracleSem gos = new GraphOracleSem(oracle, modelName);
                  psOut.println("model created using oracle " + oracle);
                  gos.close();
                  }
                  oracle.dispose();
                  }

                  oraclePool.close();

                  Thanks,

                  Zhe Wu
                  • 6. Re: OraclePool connection gets timed out
                    alwu-Oracle
                    Hi,

                    The OraclePool runs fine after staying idle for 7200 seconds.
                    I checked the sqlnet.ora of the database I'm using. There is
                    no special configuration there.

                    -----
                    cat $ORACLE_HOME/network/admin/sqlnet.ora
                    # sqlnet.ora Network Configuration File: /scratch/alwu/rel11202/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
                    # Generated by Oracle configuration tools.

                    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

                    ADR_BASE = /scratch/alwu/rel11202
                    -----

                    Do you have customized configuration for sqlnet.ora?

                    There are a few timeout related parameters described in the following document.
                    http://download.oracle.com/docs/cd/E11882_01/network.112/e10835/sqlnet.htm#CIHCCCHD

                    I will let my program run overnight (say trying to get an Oracle instance after staying idle for 8 hours).

                    Thanks,

                    Zhe Wu
                    • 7. Re: OraclePool connection gets timed out
                      725104
                      Ok, sorry. Preparing your test i realized my pool hasnt close method !
                      oraclePool.close();

                      I had in my classpath the version 11.2 of the jena adapter, not the 11.2.0.2, the one i suppose you are using.

                      So i'm correcting now this and trying again.
                      • 8. Re: OraclePool connection gets timed out
                        alwu-Oracle
                        Hi,

                        That is a good idea.

                        As a FYI. The code runs fine overnight. Basically, the code created the pool, did something; stayed idle for 28800000 seconds, did something;
                        waited 7200 seconds, did something ... It looks all right based on the following output.

                        testOraclePool jdbc:oracle:thin:@semperf3:1522:rel11202 scott tiger basicjac
                        Done creating OraclePool
                        model created using oracle oracle.spatial.rdf.client.jena.Oracle@1e0512a
                        model size 1
                        sleep 1 second
                        model created using oracle oracle.spatial.rdf.client.jena.Oracle@64023c
                        model size 1
                        sleep 28800000 second
                        model created using oracle oracle.spatial.rdf.client.jena.Oracle@1cbda0a
                        model size 1
                        sleep 7200 second
                        model created using oracle oracle.spatial.rdf.client.jena.Oracle@1d8d39f
                        model size 1
                        sleep 1 second
                        model created using oracle oracle.spatial.rdf.client.jena.Oracle@2d95b3
                        model size 1
                        sleep 10 seconds
                        model created using oracle oracle.spatial.rdf.client.jena.Oracle@bf5555
                        model size 1

                        Thanks,

                        Zhe