Oracle jdbc connection takes long time, then "java.sql.SQLRecoverableException: IO Error: Broken pipe"

Question

In my application I use connection to Oracle. Sometimes It shows Broken-pipe error, not all the time.

1. Why connection takes over 1 minute(Causes "Broken pipe" error)? DB and application in the same host.
2. Why reconnect 5 seconds cycle? I guess "Con time=30066" means that tried 7 times.

Please help and thank you.

2016-08-05 10:00:40 (Con time=1041,  Qry time=12)
2016-08-05 10:00:43 (Con time=89,  Qry time=1)
2016-08-05 10:00:46 (Con time=65,  Qry time=7)
2016-08-05 10:00:49 (Con time=59,  Qry time=1)
2016-08-05 10:00:52 (Con time=58,  Qry time=1)
2016-08-05 10:00:55 (Con time=62,  Qry time=2)
2016-08-05 10:00:58 (Con time=100,  Qry time=1)
2016-08-05 10:01:01 (Con time=66,  Qry time=1)
2016-08-05 10:01:04 (Con time=60,  Qry time=1)
2016-08-05 10:01:08 (Con time=63,  Qry time=1)
2016-08-05 10:01:11 (Con time=68,  Qry time=0)
2016-08-05 10:01:14 (Con time=61,  Qry time=1)
...
2016-08-05 15:09:01 (Con time=30066,  Qry time=9)
2016-08-05 15:10:01 (Con time=65,  Qry time=11)
2016-08-05 15:11:06 (Con time=5059,  Qry time=11)
2016-08-05 15:12:07 (Con time=91,  Qry time=11)
2016-08-05 15:13:07 (Con time=73,  Qry time=12)
2016-08-05 15:14:07 (Con time=56,  Qry time=275)
2016-08-05 15:15:27 (Con time=20093,  Qry time=13)

java.sql.SQLRecoverableException: IO Error: Broken pipe
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:498)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
        at java.sql.DriverManager.getConnection(DriverManager.java:322)
        at java.sql.DriverManager.getConnection(DriverManager.java:297)
        at DbCon.getDate(DbCon.java:325)
        at DbCon.main(DbCon.java:104)
Caused by: java.net.SocketException: Broken pipe
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:103)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:147)
        at oracle.net.ns.DataPacket.send(DataPacket.java:210)
        at oracle.net.ns.NetOutputStream.flush(NetOutputStream.java:230)
        at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:321)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:268)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:190)
        at oracle.net.ns.NetInputStream.read(NetInputStream.java:107)
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124)
        at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80)
        at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:350)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOSESSKEY(T4CTTIoauthenticate.java:407)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:416)
        ... 8 more



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import oracle.jdbc.OracleConnection;

public class DbCon {


    /**JDBC Driver*/
    public static String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
    /**URL*/
    public static String JDBC_URL="jdbc:oracle:thin:XXXXXXXXXXXXXXX";

    public static void main(String[] args) {
        while(true){
            System.out.println(getDate());
            try {
                Thread.sleep(3000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }

    }


    /**
     * Get date for test
     * @return
     */
    public static String getDate() {
            Connection con = null;// SqlConnection
            ResultSet rs = null;// IDataReader
            PreparedStatement ps = null;
            String str = "";
            long timeCon = System.currentTimeMillis();
            try {

                    Class.forName(JDBC_DRIVER);
                    con = DriverManager.getConnection(JDBC_URL);
                    str = " (Con time="+(System.currentTimeMillis()-timeCon)+", ";
                    StringBuffer sbSql = new StringBuffer()
                                    .append("SELECT SYSDATE FROM DUAL");
                    ps = con.prepareStatement(sbSql.toString());
                    long timeQry = System.currentTimeMillis();
                    rs = ps.executeQuery();
                    while (rs.next()) {str = rs.getString(1) + str;}
                    str = str +" Qry time="+(System.currentTimeMillis()-timeQry)+")";
                    return str;
            } catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}
            finally {
                    if (rs != null) {try {rs.close();} catch (Exception ex) {ex.printStackTrace();}}
                    if (ps != null) {try {ps.close();} catch (Exception ex) {ex.printStackTrace();}}
                    if (con != null) {try {con.close();} catch (Exception ex) {ex.printStackTrace();}}
                    str =  " (Con time="+(System.currentTimeMillis()-timeCon)+")";
            }
            return str;
    }


}

Answer 1

Cause:

The problem could be lookup issues between IPv6 versus IPv4. If the Domain Name System (DNS) server is not configured to handle IPv6 queries, the application may have to wait for the IPv6 query to time out for IPv6 queries.

These threads are waiting for a response for an IPv6 query. It is likely the the DNS server is not responding to the IPv6 query.

at java.net.Inet6AddressImpl.getLocalHostName(Native Method) at java.net.InetAddress.getLocalHost(InetAddress.java:123)

Solution:

My system only uses IPv4, so I set the following argument for batch command.

java -Djava.net.preferIPv4Stack=true ...

And Now It is Okay.

reference: http://www-01.ibm.com/support/docview.wss?uid=swg21170467