Oracle JDBC Error description

1

Hi

I am trying to connect to Oracle, but all I receive back is “Unable to connect to source” there are not details to underlying JDBC driver error details to identify whats wrong. I checked the log but that is also not giving anything

Is there any way to find underlying JDBC error, I tried multiple combinations of JDBC url, but nothing is working

2018-01-12 15:17:50,024 [qtp242385885-164] INFO c.d.e.s.StoragePluginRegistryImpl - User Error Occurred [ErrorId: 0dbf5760-9ecb-4947-a6af-05182147ea81]
com.dremio.common.exceptions.UserException: Unable to connect to source xxx
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:648) ~[dremio-common-1.3.1-201712020438070881-a7af5c8.jar:1.3.1-201712020438070881-a7af5c8]
at com.dremio.exec.store.StoragePluginRegistryImpl.create(StoragePluginRegistryImpl.java:439) [dremio-sabot-kernel-1.3.1-201712020438070881-a7af5c8.jar:1.3.1-201712020438070881-a7af5c8]
at com.dremio.exec.store.StoragePluginRegistryImpl.createOrUpdate(StoragePluginRegistryImpl.java:288) [dremio-sabot-kernel-1.3.1-201712020438070881-a7af5c8.jar:1.3.1-201712020438070881-a7af5c8]
at com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:124) [dremio-dac-backend-1.3.1-201712020438070881-a7af5c8.jar:1.3.1-201712020438070881-a7af5c8]
at com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:116) [dremio-dac-backend-1.3.1-201712020438070881-a7af5c8.jar:1.3.1-201712020438070881-a7af5c8]
at com.dremio.dac.resource.PutSourceResource.putSource(PutSourceResource.java:81) [dremio-dac-backend-1.3.1-201712020438070881-a7af5c8.jar:1.3.1-201712020438070881-a7af5c8]

2

Hi ,

Would you be able to send us the complete server.log from Dremio? and the screenshot of the add source settings. If any sensitive data not required

https://docs.dremio.com/advanced-administration/log-files.html

Can you please do a “tnsping <service_name>”, from the co-ordinator node?

Thanks,
@balaji.ramaswamy

3

Are you using thick driver? I dont have Oracle installed on my pc. and from log i assumed you use oracle thin
and following settings work from SQL Developer.

i enabled debug logging from logback.xml and this the connection object

2018-01-12 15:17:49,989 [qtp242385885-164] DEBUG c.d.dac.service.source.SourceService - Connection Object:
{
“type” : “jdbc”,
“driver” : “oracle.jdbc.OracleDriver”,
“url” : “jdbc:oracle:thin:user/pass@host:1545/sid”,
“username” : “user”,
“password” : “pass”,
“fetchSize” : 0
}

4

What happens when you do “telnet host 1545” from your Dremio co-ordinator?

5

sorry dont have telnet installed on my pc. However as I said Oracle SQL Developer, which uses JDBC to connect to Oracle is able to connect to the specified server with provided details. Its the Dremio which isnt able to. If somehow I can see underlying driver exception it would be easier to debug.

com.dremio.common.exceptions.UserException: Unable to connect to source xxx

this is eating underlying exception

6

Let us do one thing, let us see if this connection even tried to hit the Oracle sever

on your Oracle host (If RAC might need to see on nodes of RAC)
su - oracle
cd $ORACLE_HOME/network/log
grep <dremio-co-ordinator-ip-or-host-name> listener.log

Also can you please see output of “lsnrctl services” and make sure the sid you are specifying is created as a SERVICE_NAME?

also can you ping the host given in the data source from the Dremio co-ordinator, if not try IP address in the data source settings

Kindly let me know how it goes

Thanks,
@balaji.ramaswamy

7

Balaji, I am in corporate env, where I dont have access to Oracle server. I have been using same server through JDBC for last 6 years without any issue.

Let me try to ask again, is there any settings in Dremio which I can enable to see underlying Oracle JDBC Driver error. It can tell exactly what is the problem. Your UserException class is hiding the underlying error.

8

Hi @0419,

Would you be able to share the server.log file? Just to see if there are any additional clues about the connection problem.

10

server.zip (17.6 KB)
attached

11

Thanks @0419

We are using the thin Oracle driver.

Would you be able to show a screenshot of a configured connection from SQL Developer? Similar to this one below. I’d like to see if there are any differences we’ve missed:

orcl.JPG747×394 35.4 KB

We have filed an internal ticket to improve logging visibility.

12

Thanks, only underlying JDBC error can tell exact problem. let me know when fix is available.

2018-01-15_21-30-10

13

This got resolved when I tried fully qualified SID, still please add support to show JDBC exception than generic message.

14

How did you manage to connect? I am trying to connect to an oracle XE instance. What do you mean by fully qualified SID with which you were able to connect?

15

Hi @drajput,

Are you able to connect by entering the service name into Dremio instead of the SID?

16

For my instance SID and Service name are both same. Still unable to connect

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>
SQL>
SQL> select value from v$parameter where name like ‘%service_name%’;

VALUE

XE

17

Hi @drajput,

Is this still a problem? If yes, what version of Dremio are you running on?

Thanks,
@balaji.ramaswamy

18

@balaji.ramaswamy. This sure was an issue with 1.3. I haven’t tested it with 1.4.

What’s your take on it?

19

Hi @drajput

Yes, please download the latest Dremio version from our website and test your Oracle source and please let me know

Thanks,
@balaji.ramaswamy