RStudio Connect QuickStart - Oracle connection issue

leungi · October 6, 2019, 9:37pm

SQL driver works fine, but not Oracle; screenshot of error below.

Host, userid, password and ports were confirmed to be correct.

cole · October 7, 2019, 11:03am

Thanks for testing!! That error message is misleading, I believe, as it is probably complaining about the "Oracle Driver Manager" not existing. Have you tried following the directions here, under How can I connect to an Oracle database??

Oracle is a special beast

leungi · October 7, 2019, 1:15pm

Thanks for the reference.

I'm aware of ROracle being a different beast, and setting them up in Windows/Linux is always a pain; hence, I was hoping QuickStart may have all this setup already (it is Pro service after all ).

I followed the guide, installed Oracle instantclient, but stuck at this step, and I suspect it may be to do with RS Pro driver setup:

rstudio@rstudio-quickstart:/opt/rstudio-drivers/oracle$ ls -l
total 20
drwxr-xr-x 3 root root 4096 Sep 27  2017 bin
drwxr-xr-x 2 root root 4096 Sep 27  2017 docs
-rw-r--r-- 1 root root  437 Sep 27  2017 LICENSE
drwxr-xr-x 2 root root 4096 Sep 27  2017 setup
drwxr-xr-x 2 root root 4096 Sep 27  2017 snippets

Given that /lib doesn't exist in that folder, the following returns error:

rstudio@rstudio-quickstart:~$ ln -s /usr/lib/oracle/19.3/client64/lib/* /opt/rstudio-drivers/oracle/lib/
ln: target '/opt/rstudio-drivers/oracle/lib/' is not a directory: No such file or directory

cole · October 7, 2019, 3:25pm

Yeah, we definitely didn't do a whole lot with the drivers for the QuickStart besides install them, and I mostly try to stay away from Oracle as much as possible in general... so...

Interesting! Maybe the right approach would be mkdir -p /opt/rstudio-drivers/oracle/lib to create that directory first? And then try the ln -s command?

You could also try the alternative suggestion there:

rsession-ld-library-path=/usr/lib/oracle/12.1/client64/lib

leungi · October 7, 2019, 3:37pm

@cole: thanks for the guidance as usual.

I still believe this issue lies with QuickStart VM setup, especially after reading this RS Community post.

In the post, @josh mentioned that Oracle professional driver is actually present in /opt/rstudio-drivers/oracle/bin/lib /liboracleodbc_sb64.so.

In our case, the folder /opt/rstudio-drivers/oracle/bin/lib doesn't event exist to begin with, hence liboracleodbc_sb64.so is not available for use, even though Oracle shared libraries (from instantclient install) are.

josh · October 7, 2019, 6:34pm

What is in /opt/rstudio-drivers/oracle/bin?

leungi · October 7, 2019, 6:43pm

Thanks for joining in @josh.

rstudio@rstudio-quickstart:~$ ls /opt/rstudio-drivers/oracle/bin
lib  third-party-licenses.txt
rstudio@rstudio-quickstart:~$ ls /opt/rstudio-drivers/oracle/bin/lib
en-US  liboracleodbc_sb64.so  license.lock  OracleODBC.did  rstudio.oracleodbc.ini

Maybe I should try:

ln -s /usr/lib/oracle/19.3/client64/lib/* /opt/rstudio-drivers/oracle/bin/lib/

instead of

ln -s /usr/lib/oracle/19.3/client64/lib/* /opt/rstudio-drivers/oracle/lib/

leungi · October 7, 2019, 6:47pm

Still no luck.

rstudio@rstudio-quickstart:~$ sudo ln -s /usr/lib/oracle/19.3/client64/lib/* /opt/rstudio-drivers/oracle/bin/lib/

rstudio@rstudio-quickstart:~$ ls -l /usr/lib/oracle/19.3/client64/lib/*
lrwxrwxrwx 1 root root        21 Oct  7 07:54 /usr/lib/oracle/19.3/client64/lib/libclntshcore.so -> libclntshcore.so.19.1
-rwxr-xr-x 1 root root   8041608 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/libclntshcore.so.19.1
lrwxrwxrwx 1 root root        17 Oct  7 07:54 /usr/lib/oracle/19.3/client64/lib/libclntsh.so -> libclntsh.so.19.1
lrwxrwxrwx 1 root root        17 Oct  7 07:54 /usr/lib/oracle/19.3/client64/lib/libclntsh.so.10.1 -> libclntsh.so.19.1
lrwxrwxrwx 1 root root        17 Oct  7 07:54 /usr/lib/oracle/19.3/client64/lib/libclntsh.so.11.1 -> libclntsh.so.19.1
lrwxrwxrwx 1 root root        17 Oct  7 07:54 /usr/lib/oracle/19.3/client64/lib/libclntsh.so.12.1 -> libclntsh.so.19.1
lrwxrwxrwx 1 root root        17 Oct  7 07:54 /usr/lib/oracle/19.3/client64/lib/libclntsh.so.18.1 -> libclntsh.so.19.1
-rwxr-xr-x 1 root root  79961792 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/libclntsh.so.19.1
-rwxr-xr-x 1 root root   3609536 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/libipc1.so
-rwxr-xr-x 1 root root    478432 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/libmql1.so
-rwxr-xr-x 1 root root   6587832 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/libnnz19.so
-rwxr-xr-x 1 root root   2339896 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/libocci.so.19.1
-rwxr-xr-x 1 root root 130515320 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/libociei.so
-rwxr-xr-x 1 root root    153624 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/libocijdbc19.so
-rwxr-xr-x 1 root root    115976 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/liboramysql19.so
-rw-r--r-- 1 root root   4157408 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/ojdbc8.jar
-rw-r--r-- 1 root root    313025 Apr 17 06:41 /usr/lib/oracle/19.3/client64/lib/ottclasses.zip
-rw-r--r-- 1 root root     37519 Apr 17 06:39 /usr/lib/oracle/19.3/client64/lib/xstreams.jar

rstudio@rstudio-quickstart:~$ sudo systemctl restart rstudio-server

josh · October 7, 2019, 6:47pm

What is the output of ldd /opt/rstudio-drivers/oracle/bin/lib/liboracleodbc_sb64.so?

leungi · October 7, 2019, 6:51pm

I noticed that one (libocci.so.12.1) of the two shared libraries you noted before is missing, even though this (/usr/lib/oracle/19.3/client64/lib/libocci.so.19.1) exists.

I wonder why the symbolic link is not created.

Maybe I'll just go with lower version of instantclient.

rstudio@rstudio-quickstart:~$ ldd /opt/rstudio-drivers/oracle/bin/lib/liboracleodbc_sb64.so
        linux-vdso.so.1 (0x00007fff2ab36000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f4bdec6a000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f4bdea4b000)
        libocci.so.12.1 => not found
        libclntsh.so.12.1 => /opt/rstudio-drivers/oracle/bin/lib/libclntsh.so.12.1 (0x00007f4bdaa3f000)
        librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f4bda837000)
        libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f4bda4ae000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f4bda110000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f4bd9ef8000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f4bd9b07000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f4be1239000)
        libnnz19.so => /opt/rstudio-drivers/oracle/bin/lib/libnnz19.so (0x00007f4bd93bf000)
        libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f4bd91a5000)
        libaio.so.1 => not found
        libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f4bd8f8a000)
        libclntshcore.so.19.1 => /opt/rstudio-drivers/oracle/bin/lib/libclntshcore.so.19.1 (0x00007f4bd89ea000)
        libaio.so.1 => not found

josh · October 7, 2019, 7:23pm

My guess is that /usr/lib/oracle/19.3/client64/lib is not in the LD_LIBRARY_PATH or equivalent.
Not sure what provides libaio.so.1.

leungi · October 7, 2019, 9:28pm

Courtesy of guidance from @josh and @cole, for future QuickStart users:

save some pain by installing Oracle instantclient 12.1

rstudio@rstudio-quickstart:~$ sudo apt install alien

rstudio@rstudio-quickstart:~$ sudo alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

rstudio@rstudio-quickstart:~$ sudo alien -i oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

rstudio@rstudio-quickstart:~$ sudo apt-get install libaio1 libaio-dev

rstudio@rstudio-quickstart:~$ ldd /opt/rstudio-drivers/oracle/bin/lib/liboracleodbc_sb64.so
        linux-vdso.so.1 (0x00007ffcc4d9e000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f009a257000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f009a038000)
        libocci.so.12.1 => /opt/rstudio-drivers/oracle/bin/lib/libocci.so.12.1 (0x00007f0099cfa000)
        libclntsh.so.12.1 => /opt/rstudio-drivers/oracle/bin/lib/libclntsh.so.12.1 (0x00007f0095cee000)
        librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f0095ae6000)
        libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f009575d000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f00953bf000)
        libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f00951a7000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f0094db6000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f009c826000)
        libnnz19.so => /opt/rstudio-drivers/oracle/bin/lib/libnnz19.so (0x00007f009466e000)
        libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f0094454000)
        libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f0094252000)
        libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f0094037000)
        libclntshcore.so.19.1 => /opt/rstudio-drivers/oracle/bin/lib/libclntshcore.so.19.1 (0x00007f0093a97000)

system · October 14, 2019, 9:28pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.