Can't open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1'

Question

Background :
I deploy my shiny app to shiny server on Linux Ubuntu 16.04. My app needs to connect to Oracle DB so I use ODBC to access from my Ubuntu server.

Problem :
When I open my app in browser, it shows "Disconnected from the server"

So I check my log file /var/log/shiny-server/*.log. The error message shows as following:

Warning: Error in : nanodbc/nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1' : file not found 58: Anonymous

Execution halted

Information
I have no idea how it went wrong, so I try to list related information to the error. Please let me know if there are other clues I can provide so that you would be easier to help me.

The following is the way I connect to oracle db in my app

# ui.r
library(shiny)
shinyUI(
     absolutePanel(
       tableOutput( "table" )  
    )   
)

# server.r
library(shiny)
library(DBI)
library(odbc)
con <- DBI::dbConnect(odbc::odbc(),
                      "MES_CY" ,    # data source name
                      UID = "xxx",
                      PWD = "xxx"
)

shinyServer(function(input, output) {
  output$table <- renderTable(
    dbGetQuery(con,'
      select  * from MyTable
    ') 
  )
})

Since the app works fine locally, I suspect that maybe my server can't connect to DB.

Therefore, I adopt the identical code to connect to DB using R in my shiny server

con <- DBI::dbConnect(odbc::odbc(),
                      "MES_CY" ,   #  my data source name
                      UID = "xxx",
                      PWD = "xxx"
)
dbGetQuery(con,'
    select  * from MyTable
') 

It works and I can get my data.

Then I check if it is available to access oracle using isql, it's fine and I can query data from my db.

chaosheng@Shiny-Srv:~$ isql -v MES_CY
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

I also check if all dependencies are installed using ldd /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

linux-vdso.so.1 =>  (0x00007ffe103d1000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f832bdcc000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f832bac3000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f832b8a6000)
libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f832b68c000)
libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f8328d1d000)
libodbcinst.so.1 => /usr/lib/x86_64-linux-gnu/libodbcinst.so.1 (0x00007f8328b0b000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f8328740000)
             /lib64/ld-linux-x86-64.so.2 (0x0000556d2817c000)
libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f8328373000)
libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f8328171000)
libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007f8327f66000)

The following is my configuration of odbcinst.ini and odbc.ini

#odbcinst.ini
[Oracle 11g ODBC driver]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =
Driver Logging  = 7

#odbc.ini
Description = OracleSQL
Driver = Oracle 11g ODBC driver
Trace = No
TraceFile = /tmp/oracleodbc.log
Database = MES_PROD
Servername = MES
UserID = xxx
Password = xxx
Port = 1521
ReadOnly = No

The following is the environment variables I set in $HOME/.bashrc

export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin:$TNS_ADMIN

Here is my current env

XDG_SESSION_ID=1707
TERM=xterm
SHELL=/bin/bash
SSH_CLIENT=xx.xxx.xx.xx xxxxx xx
OLDPWD=/usr/lib/oracle/11.2/client64/lib
SSH_TTY=/dev/pts/0
USER=chaosheng
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:/usr/lib/oracle/11.2/client64/lib:
TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin:
MAIL=/var/mail/chaosheng
PATH=/usr/lib/oracle/11.2/client64/bin:/home/chaosheng/bin:/home/chaosheng/.local/bin:/usr/lib/oracle/11.2/client64/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin:/usr/lib/oracle/11.2/client64/bin
PWD=/home/chaosheng
LANG=en_HK.UTF-8
SHLVL=1
HOME=/home/chaosheng
LANGUAGE=en_HK:en
LOGNAME=chaosheng
SSH_CONNECTION=xx.xxx.xx.xx xxxxx xx.xxx.xx.xx xx
LESSOPEN=| /usr/bin/lesspipe %s
XDG_RUNTIME_DIR=/run/user/1000
ORACLE_HOME=/usr/lib/oracle/11.2/client64
LESSCLOSE=/usr/bin/lesspipe %s %s
_=/usr/bin/env

Answer 1

As some community members suggested, my problem is quite similar to this problem : Can't open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1 when create "Database Monitor" item in zabbix.

Despite the fact that the cause of the two problems is virtually identical, the solution might be slightly different.
Therefore, I still post how I solve my problem.

To begin with, I made few setting mistakes in my configuration files.

In the first place, I need to set my env variables in $HOME/.profile ( /home/MyName/.profile )
Nevertheless, I only set these in $HOME/.bashrc ( /home/MyName/.bashrc )

Secondly, I use the default setting in /etc/shiny-server/shiny-server.conf in the beginning.

run_as shiny; 
preserve_logs true;
server {
  listen 3838;

  location / { 
    # Host the directory of Shiny Apps stored in this directory
    site_dir /srv/shiny-server;

    # Log all Shiny output to files in this directory
    log_dir /var/log/shiny-server;

    # When a user visits the base URL rather than a particular application,
    # an index of the applications available in this directory will be shown.
    directory_index on;
  }
}

Obviously, the above setting didn't work.

Since I put my environment variables' setting in /home/MyName/.profile, I need to specify run_as MyName so that any env variables defined in /home/MyName/.profile would be available to the R process.

preserve_logs true;
server {
  listen 3838;

  location / {
    run_as MyName; 
    # Host the directory of Shiny Apps stored in this directory
    site_dir /srv/shiny-server;

    # Log all Shiny output to files in this directory
    log_dir /var/log/shiny-server;

    # When a user visits the base URL rather than a particular application,
    # an index of the applications available in this directory will be shown.
    directory_index on;
  }
}

I have extremely limited knowledge in linux system.

Maybe the above is not the whole picture of the problem, but it just solves the issue.