Oracle JDBC: set timeout for "Unknown host specified"

Question

This and similar questions were asked many times but none of the recommended setting work for me.

What I need is to configure the timeout for the case if the DB host is not available or Oracle DB is still not up and running.

I need to check the status of the Oracle DB server in Docker, so I am executing select 1 from dual in a bash loop but the DriverManager.getConnection returns (with the mentioned exception) after 20 seconds which is too much for me. I would like to reduce this timeout to 1 sec.

I know there is an Oracle tool, called TNSPING for checking the Oracle DB Server status, but unfortunately, this tool is not part of the official Oracle DB image and I do not want to install any Oracle product in Docker just because of TNSPING.

This is what I have tried so far, but the settings I use have no impact on this timeout. The java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection exception appears after 20 sec. instead of 1 sec. Does not matter I use 1, 1000, or 10000 in the java code, the timeout is always 20 sec.

private Connection getConnection(String jdbcUrl) throws SQLException {
    String timeout = "100";
    Properties p = new Properties();
    p.put(OracleConnection.CONNECTION_PROPERTY_THIN_NET_CONNECT_TIMEOUT, timeout);
    p.put(OracleConnection.CONNECTION_PROPERTY_THIN_READ_TIMEOUT, timeout);
    p.put(OracleConnection.CONNECTION_PROPERTY_THIN_JNDI_LDAP_CONNECT_TIMEOUT, timeout);
    p.put(OracleConnection.CONNECTION_PROPERTY_THIN_JNDI_LDAP_READ_TIMEOUT, timeout);
    p.put(OracleConnection.CONNECTION_PROPERTY_THIN_OUTBOUND_CONNECT_TIMEOUT, timeout);
    p.put(OracleConnection.CONNECTION_PROPERTY_DOWN_HOSTS_TIMEOUT, timeout);
    p.put("oracle.jdbc.ReadTimeout", timeout);
    p.put("oracle.net.CONNECT_TIMEOUT", timeout);

    p.put (OracleConnection.CONNECTION_PROPERTY_USER_NAME, user);
    p.put (OracleConnection.CONNECTION_PROPERTY_PASSWORD, password);

    System.setProperty("oracle.net.READ_TIMEOUT", timeout);
    System.setProperty("oracle.jdbc.ReadTimeout", timeout);
    System.setProperty("oracle.jdbc.javaNetNio", "true");

    DriverManager.setLoginTimeout(Integer.valueOf(timeout));
    Connection connection = DriverManager.getConnection(jdbcUrl, p);
    connection.setNetworkTimeout(Executors.newSingleThreadExecutor(), Integer.valueOf(timeout));

    return connection;
}

I do not want to add a connection pool solution to my simple app, only pure JDBC can be enough for me.

What I missed here?

-- UPDATE --

It seems that the issue is environment-specific and maybe not related to Java, but still not sure.

If I execute exactly the same command in my machine and in Docker then I get back total different execution times:

command:

$ time java -jar sql-runner-0.2.0-SNAPSHOT-with-dependencies.jar -j jdbc:oracle:thin:@//somehost:1521/somedb -U "doesnotmatter" -P "password" "select 1 from dual"

The result in docker:

IO Error: Unknown host specified 

real    0m20.521s
user    0m0.764s
sys 0m0.097s

The result if I execute it on my machine:

IO Error: Unknown host specified 

real    0m0.501s
user    0m0.715s
sys 0m0.095s

The complete source code is available here.

This behavior is so strange.

Which JDBC version do you use? Try to use tcpdump to check, or user `_g.jar` version and turn tracing on. Are you connecting to standalone database or to RAC ? — ibre5041, Jul 15 '20 at 12:32
The JDBC driver I use is `ojdbc8, v12.2.0.1`. The Oracle database version that I use for testing is the official, untouched `store/oracle/database-enterprise:12.2.0.1` Docker image. You can pull and check the code from here: https://github.com/zappee/sql-runner You can contribute to the project if you can help ;) — zappee, Jul 15 '20 at 13:43
did you try something like this: https://stackoverflow.com/a/10705424 — , Jul 15 '20 at 14:13
Unfortunately the `sun.net.client.*` properties did not help. The updated source code is available here: https://github.com/zappee/sql-runner/blob/set-timeout/src/main/java/com/remal/sqlrunner/SqlStatementExecutor.java Maybe it comes from a completely different level and not from the JDBC driver? Maybe this is an environment specific issue... — zappee, Jul 15 '20 at 14:38

ibre5041 · Accepted Answer · 2020-09-21T11:14:04.100

There might be various reasons why this does not work for you:

depending on JDBC drivers version timeout can be miliseconds or seconds
some options you set are simply ignored by your driver's version
there might be various issues with DNS, like Oracle SCAN listener sends you redirect to RAC cluster node. And your client can not resolve that hostname.

You have these options to diagnose your problem:

strace:

strace -f -e trace=network -o strace.log java -jar sql-runner-0.2.0-SNAPSHOT-with-dependencies.jar -j jdbc:oracle:thin:@//somehost:1521/somedb -U "doesnotmatter" -P "password" "select 1 from dual"

Debug enabled JDBC driver (*_g.jar):

$ java -Doracle.jdbc.Trace=true \ 
-Djava.util.logging.config.file=Logging.properties \
-classpath "ojdbc8_g.jar:..." \
...

$ cat Logging.properties
.level=OFF

#.level=SEVERE
handlers=java.util.logging.FileHandler

#  example of a full pathname in Windows
java.util.logging.FileHandler.pattern=Networkpacket.log

# Predefined levels are: ALL, SEVERE, WARNING, INFO, CONFIG, FINE, FINER,
#                        FINEST, OFF

java.util.logging.FileHandler.limit = 500000000
java.util.logging.FileHandler.count = 1
java.util.logging.FileHandler.level =ALL
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
oracle.net.ns.level = ALL

## Following levels are commented to filter the network packet contents.

#oracle.jdbc.level=oFF
#oracle.jdbc.aq.level=OFF
#oracle.jdbc.driver.level=OFF
#oracle.jdbc.pool.level=OFF
#oracle.jdbc.rowset.level=OFF
#oracle.jdbc.xa.level=OFF
#oracle.sql.level=OFF

I am not familiar with `strace` log but what I can see there is in docker the DNS request-response is different. Docker: `1405 connect(5, {sa_family=AF_INET, sin_port=htons(53), sin_addr=inet_addr("127.0.0.11")}, 16) = 0`. I need to check Networkpacket.log as well. — zappee, Jul 16 '20 at 08:58

Oracle JDBC: set timeout for "Unknown host specified"

1 Answers1