Unable to connect to Oracle database from dremio

1

Hi,

I am unable to Oracle XE instance from dremio. Below are my connection parameters. All I can see in the logs is listed below.

{
“name”: “oracle_test”,
“description”: “autogenrated connection from applicaiton”,
“type”: “ORACLE”,
“config”: {
“username”: “xxxxxx”,
“password”: “xxxxxx”,
“hostname”: “x.x.x.x”,
“instance”: “XE”,
“port”: 1521,
“authenticationType”: “MASTER”,
“fetchSize”: 0
},
“accelerationRefreshPeriodMs”: 3600,
“accelerationGracePeriodMs”: 3600,
“metadataPolicy”: {
“authTTLMs”: 3600,
“datasetRefreshAfterMs”: 3600,
“datasetExpireAfterMs”: 3600,
“namesRefreshMs”: 3600,
“datasetUpdateMode”: “PREFETCH_QUERIED”
}
}

2018-01-24 14:10:20,976 [qtp1492290654-189] INFO c.d.e.s.StoragePluginRegistryImpl - User Error Occurred [ErrorId: b1c4cb23-a1b0-49f5-906b-146ce2983523]
com.dremio.common.exceptions.UserException: Unable to connect to source oracle_test
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:648) ~[dremio-common-1.3.0-201711211846350824-31f8d91.jar:1.3.0-201711211846350824-31f8d91]
at com.dremio.exec.store.StoragePluginRegistryImpl.create(StoragePluginRegistryImpl.java:439) [dremio-sabot-kernel-1.3.0-201711211846350824-31f8d91.jar:1.3.0-201711211846350824-31f8d91]
at com.dremio.exec.store.StoragePluginRegistryImpl.createOrUpdate(StoragePluginRegistryImpl.java:288) [dremio-sabot-kernel-1.3.0-201711211846350824-31f8d91.jar:1.3.0-201711211846350824-31f8d91]
at com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:124) [dremio-dac-backend-1.3.0-201711211846350824-31f8d91.jar:1.3.0-201711211846350824-31f8d91]
at com.dremio.dac.service.source.SourceService.createSource(SourceService.java:145) [dremio-dac-backend-1.3.0-201711211846350824-31f8d91.jar:1.3.0-201711211846350824-31f8d91]
at com.dremio.dac.api.SourceResource.addSource(SourceResource.java:86) [dremio-dac-backend-1.3.0-201711211846350824-31f8d91.jar:1.3.0-201711211846350824-31f8d91]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_151]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_151]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_151]

Trace reports show:

DEBUG c.d.dac.service.source.SourceService - Connection Object:
{
“type” : “jdbc”,
“driver” : “oracle.jdbc.OracleDriver”,
“url” : “jdbc:oracle:thin:xxxxx/xxxxx@x.x.x.x:1521/XE”,
“username” : “xxxxx”,
“password” : “xxxxx”,
“fetchSize” : 0
}

2

Hi @drajput,

I ran into this issue myself while testing adding Oracle DS to Dremio and have already filed a bug for this. I took the JDBC URL from the log, however I needed to modify it to:

“url” : “jdbc:oracle:thin:xxxxx/xxxxx@x.x.x.x:1521/XE”, ->
“url” : “jdbc:oracle:thin:xxxxx/xxxxx@x.x.x.x:1521:XE”

in order to connect to Oracle using JDBC. Notice the “/” change to “:”. I’m working with the team in evaluating a possible modification to the Oracle DS connector.

Thanks,
Danny

3

@dbrody Yup I thought so too. Is this going to be part of any coming dremio release?

What exact changes in dremio codebase did you make to make this work?

4

Hi @drajput,

We’re still evaluating what our options are, but I’ll let you know here if Oracle DS connector has been changed. I’ve been performing test on this recently using Oracle12c.

Thanks,
Danny

5

Hi @drajput,

We’ve identified& fixed this issue, I’ve tested the fix and it should work in future releases. I’ll liaise with product management in which particular release will the fix go into.

Thanks,
Danny

6

That’s good to hear. Hope the fix can be rolled out quickly in a few weeks window.

8

Is this bug fixed in 2.0 , I am still not able to connect to Oracle. I can connect using SQL developer , but not through Dremio.

9

Hi @ashitabh_kumar,

The fix that @dbrody mentioned is in 2.0.

11

Hi @ashitabh_kumar

Are you specifying an instance name or SID when setting up the source?

13

Would you be able to enable debug logging? I’m interested in seeing what JDBC URL is getting generated.

You can edit logback.xml in the conf directory.
Find this section:

<logger name="com.dremio">
    <level value="${dremio.log.level:-info}"/>
</logger>

And edit -info to be -debug. Afterwards, make sure to restart all nodes.

14

I don’t have sudo permission to modify anything , will ask administrator.
Meanwhile I compared github code and jar file.
Inside dremio-extra-plugin-jdbc-2.0.0-201804102150500749-ee1ef6c.jar,
there is a class com.dremio.exec.store.jdbc.conf.OracleConf.class however , there is no such code in https://github.com/dremio/dremio-oss
COuld you please confirm where can I get this OracleConf.java

15

Just created a sample java code to check thin client URL and found the issues.
1.I am using 11g . JDBC URL for 11g is different from 12c
11g still uses “:” while 12c uses “/” before “SID”
“jdbc:oracle:thin:xxx/yyy@ip:1521:sid” is working on 11g
2. My password contained special character “@” which is breaking the url parsing rule. Java assumes the characters after @ is IP address

16

The OracleConf class is part of the closed source components of Dremio.

The fix that we did for the issue @dbrody mentioned was to change the JDBC URL generation from:
jdbc:oracle:thin:[user]/[password]@[host]:[port]/[instance] to
jdbc:oracle:thin:[user]/[password]@//[host]:[port]/[instance]

, the latter being the proper way of providing instance names.

Are you able to connect using the second format using other JDBC tools? Can you connect by service name using SQL Developer?

17

Hey Ashitabh,

As I understand it, the Oracle URL parser takes exception to certain special characters including @

Can you try encoding that symbol, so it becomes %40

Regards,

Christy