Oracle Instant Client failing on Ubuntu-based agent despite correct TNS_ADMIN path

Question

I am attempting to perform an SQL query using oracle-instantclient-basic-21.5 through an Ubuntu 20.04.3 agent hosted by Azure Devops. The query itself (which reads: python query_data) works when I am running it on my own machine with specs:

• Windows 10
• Path=C:\oracle\product\11.2.0.4\client_x64\bin;...;...
• TNS_ADMIN=C:\oracle\product\tns
• Python 3.8.5 using sqlalchemy with driver="oracle" and dialect = "cx_oracle"

I am running the following:

pool:
  vmImage: 'ubuntu-latest'

steps:
 - script: |
    sudo apt install alien
  displayName: 'Install alien'

 - script: |
    sudo alien -i oracle-instantclient-basic-21.5.0.0.0-1.x86_64.rpm
  displayName: 'Install oracle-instantclient-basic'

 - script: |
    sudo sh -c 'echo /usr/lib/oracle/21/client64/ > /etc/ld.so.conf.d/oracle-instantclient.conf'
    sudo ldconfig
  displayName: 'Update the runtime link path'

 - script: |
    sudo cp tns/TNSNAMES.ORA /usr/lib/oracle/21/client64/lib/network/admin
    sudo cp tns/ldap.ORA /usr/lib/oracle/21/client64/lib/network/admin
    sudo cp tns/SQLNET.ORA /usr/lib/oracle/21/client64/lib/network/admin
    sudo cp tns/krb5.conf /usr/lib/oracle/21/client64/lib/network/admin
  displayName: 'Copy and paste correct TNS content'

 - task: UsePythonVersion@0
  inputs:
    versionSpec: '3.8'

 - script: |
    export ORACLE_HOME=/usr/lib/oracle/21/client64
    export PATH=$ORACLE_HOME/bin:$PATH
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
    export TNS_ADMIN=$ORACLE_HOME/lib/network/admin
    python query_data
  displayName: 'Attempt to run python script with locally valid environment variables'

with the error TNS:could not resolve the connect identifier specified. What I have done:

• Checked that the locations I am referring to match the actual oracle-instantclient-basic installation

• Copied the TNSNAMES.ORA, ldap.ORA etc. that I am using on my own machine and verified that they are present in the desired location (/usr/lib/oracle/21/client64/lib/network/admin)

• Checked that TNS_ADMIN points to the correct path (/usr/lib/oracle/21/client64/lib/network/admin)

The sql query does not complain about a missing client, so it is aware of the installation. Why doesn't it read the TNS_ADMIN path or its contents correctly?

Answer 1

• On Linux change the file names to lowercase tnsnames.ora and sqlnet.ora and ldap.ora. If you run, say, strace sqlplus a/b@c you can see that is looks for the lowercase names.

• With Instant Client, don't set ORACLE_HOME.

• There's no need to set LD_LIBRARY_PATH since ldconfig is used

• There's no need to set TNS_ADMIN since you have moved the configuration files to the default location.

• You can simplify your install by using alien -i --scripts oracle-instantclient-basic-21.5.0.0.0-1.x86_64.rpm This will automatically do the ldconfig step for you.

Hopefully you have installed the Python cx_Oracle module somehow.