topic Re: SAS/Access : Oracle Configuration in Administration and Deployment

SAS/Access : Oracle Configuration

mkiran — Wed, 08 May 2024 13:44:19 GMT

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

Re: SAS/Access : Oracle Configuration

mkiran — Wed, 08 May 2024 18:38:38 GMT

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;

Re: SAS/Access : Oracle Configuration

SASKiwi — Wed, 08 May 2024 20:15:56 GMT

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.

Re: SAS/Access : Oracle Configuration

mkiran — Wed, 08 May 2024 20:18:44 GMT

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

Re: SAS/Access : Oracle Configuration

gwootton — Wed, 08 May 2024 20:41:33 GMT

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

Re: SAS/Access : Oracle Configuration

mkiran — Wed, 08 May 2024 21:47:56 GMT

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

Re: SAS/Access : Oracle Configuration

MarcoGhiglieri — Wed, 08 May 2024 22:17:00 GMT

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

Does TNSPING work?

Re: SAS/Access : Oracle Configuration

mkiran — Wed, 08 May 2024 22:52:17 GMT

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

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)

Re: SAS/Access : Oracle Configuration

MarcoGhiglieri — Thu, 09 May 2024 13:47:42 GMT

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

Re: SAS/Access : Oracle Configuration

eddieleung305 — Thu, 09 May 2024 14:01:56 GMT

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.

Re: SAS/Access : Oracle Configuration

mkiran — Thu, 09 May 2024 14:12:38 GMT

@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!.