SAS/Access : Oracle Configuration

[mkiran]

I am configuring Oracle on new 9.4 M8 and below are the entries I made in sasenv_local after having Oracle configured on Linux. everything looks good as per SAS documentation but still getting the error. Can you please suggest where things are going wrong?

Note : I have restarted Connect and Share services after updating sasenv_lcoal.

 libname mydblib oracle user=uname password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 ! path=<mypath from tnsnames.ora>;
ERROR: Unable to load oracle client (libclntsh.so)
ERROR: Error in the LIBNAME statement.

sasenv_local entries:

ODBCHOME=/sas/sashome/AccessClients/9.4/SQLServer
export ODBCHOME

ORACLE_HOME=/sas/oracle/product/12.2.0/dbhome_1
export ORACLE_BASE=/sas/oracle
export PATH=$ORACLE_HOME/lib:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH

echo $PATH
/sas/sashome/SASFoundation/9.4:/sas/oracle/product/12.2.0/dbhome_1/lib:/sas/sashome/SASFoundation/9.4:/sas/oracle/product/12.2.0/dbhome_1/bin:/home/sas/.local/bin:/home/sas/bin:/usr/share/centrifydc/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sas/config/Lev1/Applications/SASGridManagerClientUtility/9.4/

echo $LD_LIBRARY_PATH
/sas/sashome/AccessClients/9.4/SQLServer/lib:/sas/oracle/product/12.2.0/dbhome_1/lib

[mkiran]

Below code is working with the existing configuration but not when I setup a library and with PATH variable, immediate help would be a great help.

Notworking:

libname mydblib oracle 
user=uname password="Password"
path=DBDTST1Q;

tnanames.ora entries :

DBDTST1Q=   
   (DESCRIPTION=
      (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=CWB02QDBDSDB14)(PORT=1522)))
      (CONNECT_DATA=(SID=dbdtst1q)))

/*changed SID to SERVICE_NAME as well still not working */

Working:

libname oralib1 oracle path="(DESCRIPTION=
      (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=HOST)(PORT=1522)))
      (CONNECT_DATA=(SID=DBDTST1Q)))" user=uname password="AES256Password;

[SASKiwi]

I suggest you first verify that the Oracle executable libclntsh.so exists in the Oracle path you have specified. If this is an urgent issue then I suggest you open a Tech Support track.

[mkiran]

Yes  libclntsh.so exist in the mentioned LIB path and waiting to hear back from SAS.

[gwootton]

I don't see "export ORACLE_HOME" in your sasenv_local, does adding that help?

[mkiran]

@gwootton : Yes, I have added ORACLE_HOME but not useful, got the same error.

[MarcoGhiglieri]

To me it looks related to the entry in tnsnames.ora .

Does TNSPING work? 

[mkiran]

Yes, TNS ping is working. below is the output for tnsping.

[sas@hostname admin]$ tnsping DBDTST

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 08-MAY-2024 18:49:41

Copyright (c) 1997, 2016, Oracle. All rights reserved.

Used parameter files:
/sas/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1522))) (CONNECT_DATA=(SID=dbdtst)))
OK (0 msec)

[MarcoGhiglieri]

DBDTST1Q (test above) and DBDTST (last ping) are equally defined?

[eddieleung305 · Accepted solution]

Please try to use the sas installer account (i.e. 'sas'?) to run sqlplus and tnsping to ensure the Oracle client can be executed by 'sas' account first.

Then please check the libclntsh.so is pointing to the correct Oracle 12 driver if it is a soft link.

If the Oracle client installation is corrected then I suspect that it is either accessing privilege by sas account or a broken soft link issue.

[mkiran]

@eddieleung305 : Great catch : just re-verified filesystem permissions on every directory from top level and the directory "oracle" is owned by root and it has 750 permissions, regular users(not sas) not able to access tnsnames.ora file . Changing permissions to 755 on this directory resolved the issue. Thank you!.