Oracle -- Intermittent [Connection is not valid.] message

1

I’m using Dremio v. 4.1.8. Much of the time, my Oracle connection works. However, I run across the following error,often once or twice a day, when trying to pull data via my Oracle connection

The source [myoraclesourcename ] is currently unavailable. Info: [Connection is not valid.]

If I resubmit my query, or use the UI to browse to another dataset used by that same connection, the error goes away, often for several hours/rest of the day.

I’m assuming this message might be occurring because for some reason, the connection closed, but don’t know for sure because of the ambiguity of the message.

Is there a fix or a workaround for this?

2

We use Oracle all the time without issues. Have your DBA check the Oracle listener log. We are on Dremio 4.2.1.

1 Like
3

@jabaier117

What @mcotter suggested was good. In addition, you can also install Oracle client on the Dremio coordinator and try a tnsping and see how the return comes. Run the tnsping a few times in a loop and see if you get a time out

Thanks
Bali

4

@balaji.ramaswamy Thank you for the suggestion.

5

@balaji.ramaswamy Our Oracle DBAs have asked us to turn on Oracle tracing, so they can further help troubleshoot this connection is not valid issue we’re still seeing.

Is this possible with Dremio? Here’s what we’ve attempted, but no oracle.log is getting generated. Any ideas?

  • Updated the DREMIO_JAVA_SERVER_EXTRA_OPTS with
    “-Doracle.jdbc.Trace=true -Djava.util.logging.config.file=[mydremiopathhere]/conf/oracleLogConfig.properties”

where the oracleLogConfig.properties looks like this:
.level=SEVERE
oracle.jdbc.level=ALL
oracle.jdbc.handlers=java.util.logging.FileHandler
java.util.logging.FileHandler.level=ALL
java.util.logging.FileHandler.pattern=/var/log/dremio/oracle.log
java.util.logging.FileHandler.count=1
java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter

  • restarted Dremio

Thanks.

6

@jabaier117

Looking at https://docs.oracle.com/cd/B28359_01/java.111/b31224/diagnose.htm, it looks good but not sure why it is not generating a log file. Have we checked in Oracle’s listener. logfile if there is an entry for the Dremio coordinator IP when the error happens? Atleast we can see if this even reaching the Oracle server

Thanks
Bali

7

@balaji.ramaswamy Yes. Our DBAs checked. They could not find errors in the listener logfile that corresponded to errors coming from our coordinator’s IP.

They DBAs also suggested we change the connection to use an ldap based connection string such as:

jdbc:oracle:thin:@ldap://oraldap:389/myhostname,cn=OracleContext,dc=xxx,dc=xxx

rather than hard coding a single RAC node. But the Dremio UI does not appear to support the format above. Is there any way to create such a connection from Dremio, or would that require an enhancement to the plugin?

8

@jabaier117

Currently we do not have one like that,