Connectivity issues Oracle cloud

Question

I have an issue with my applications using oracle RDBMS version 12.1.0.2.0. Although I am connecting to the database correctly, sometimes I get (A) some spurious disconnections with the exception

    java.sql.SQLRecoverableException: IO Error: Connection reset
    at java.lang.Thread.run(Unknown Source)
    Suppressed: java.sql.SQLRecoverableException: Closed Connection

    ...

    Caused by: java.net.SocketException: Connection reset
    at java.net.SocketInputStream.read(Unknown Source)
    at java.net.SocketInputStream.read(Unknown Source)
    at oracle.net.ns.Packet.receive(Packet.java:311)
    at oracle.net.ns.DataPacket.receive(DataPacket.java:105)

and (B) some other times I can't even create a connection

    java.sql.SQLRecoverableException: IO Error: Software caused connection abort: recv failed
    ...
    Caused by: java.net.SocketException: Software caused connection abort: recv failed

Or

    Caused by: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:322)
        ... 6 more
    Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
        at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:445)
        at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:464)
        at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:594)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:229)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486)
        ... 11 more
    Caused by: java.net.ConnectException: Connection refused: connect
        at java.net.TwoStacksPlainSocketImpl.socketConnect(Native Method) [rt.jar:1.8.0_92]
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) [rt.jar:1.8.0_92]
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) [rt.jar:1.8.0_92]
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) [rt.jar:1.8.0_92]
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172) [rt.jar:1.8.0_92]
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) [rt.jar:1.8.0_92]
        at java.net.Socket.connect(Socket.java:589) [rt.jar:1.8.0_92]
        at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:162)
        at oracle.net.nt.ConnOption.connect(ConnOption.java:133)
        at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:411)

Some of the applications are using wildfly v8.2 with the following standalone settings

    <connection-url>connectionString</connection-url>
    <driver-class>oracle.jdbc.OracleDriver</driver-class>
    <driver>ojdbc7.jar</driver>  
    <pool>
        <min-pool-size>12</min-pool-size>
        <max-pool-size>24</max-pool-size>
        <prefill>false</prefill>
        <use-strict-min>true</use-strict-min>
    </pool>
    <security>
        <user-name>user</user-name>
        <password>password</password>
    </security>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
        <validate-on-match>false</validate-on-match>
        <background-validation>true</background-validation>
        <background-validation-millis>300000</background-validation-millis>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
    </validation>
    <timeout>
        <set-tx-query-timeout>false</set-tx-query-timeout>
        <blocking-timeout-millis>0</blocking-timeout-millis>
        <idle-timeout-minutes>8</idle-timeout-minutes>
        <query-timeout>0</query-timeout>
        <use-try-lock>0</use-try-lock>
        <allocation-retry>0</allocation-retry>
        <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
    </timeout>
    <statement>
        <track-statements>true</track-statements>
        <share-prepared-statements>false</share-prepared-statements>
    </statement>

And the rest are standalone applications using JDBC. I already checked the listener logs and according to them, the application never sent a request (B scenario). Please note that the application retries one second later and successfully connects and that the applications are deployed within the same network.

Can you please help identifying the cause of it ?

Thank you.

score 0 · Answer 1 · answered Nov 29 '16 at 09:50

(A): The error indicates that the socket has been gracefully closed. This could be a micro network outage or it could be a firewall somewhere in the middle between the app and the Database that closes a socket when it detects no activity for more than x seconds. It may help to turn on keep alive by setting the oracle.net.keepAlive connection property to true. Note that this property can also be set as a Java system property using -D.

(B): The listener may refuse socket connection attempts if a rate limit has been configured which helps during logon storms. A proper client should retry with some delay. The JDBC thin driver in 12.1.0.2 supports these two parameters in the URL (values are in seconds). For example:

(DESCRIPTION_LIST=
  (DESCRIPTION=
   (CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(RETRY_DELAY=3)
   (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost1)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost2)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=example1.com)))
  (DESCRIPTION=
   (CONNECT_TIMEOUT=60)(RETRY_COUNT=1)(RETRY_DELAY=5)
   (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost3)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost4)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=example2.com))))

The doc is here.

Connectivity issues Oracle cloud

1 Answers1