Tutorial: Setting Up Oracle Access in Red Hat Environment

five — Mon, 24 Jun 2019 20:09:01 GMT

This process was a bit ugly for me, so I thought I would share the steps I used to get this connection setup and working. Along with the source where relevant, when I had a bit of help. These are really my personal notes, just thought I would share with the general public.

Caveat: this may not be the best method or best practice. It was literally achieved via reading the manual and google searches against error messages, until they went away and I could see my data. I am a competent Windows SysAdmin that has been forced to wear a Linux Admin hat just for SAS, thrashing teeth and screaming, free of charge.

General OS Instructions and Info:

Info

We are running SAS 9.4 M5, in a mult-tiered environment across 7 SAS servers that are running Oracle 6.8.

Instructions

1) Downloaded oracle ODBC installers and tools from here: (LINK to a decent writeup using SAS Viya with Oracle)

$ cd ~
$ rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm  
$ rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

2) Now, add this path to the system library list. Create and edit a new file: (LINK)

$ sudo nano /etc/ld.so.conf.d/oracle.conf

CONTENT:
/usr/lib/oracle/12.2/client64/lib/

Now run the dynamic linker run-time bindings utility:

$ sudo ldconfig

3) Next we need to make the path info persistent: (LINK)

You should already have all needed variables in /etc/profile.d/oracle.sh. Make sure you source it:

$ sudo nano /etc/profile.d/oracle.sh

CONTENT:
ORACLE_HOME=/usr/lib/oracle/12.2/client64
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME
export LD_LIBRARY_PATH
export PATH

$ source /etc/profile.d/oracle.sh

4) Create the tnsnames.ora file:

(Provided by the oracle db admin)

$ sudo nano /etc/tnsnames.ora

CONTENT:
myhost =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.edu)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myhost)
    )
  )

5) Verify that the oracle connection is working at the OS level:

sqlplus my_USERNAME/my_password@myhost
(Should show Successful login)


SQL> SELECT DISTINCT OWNER FROM ALL_OBJECTS;
SQL> SELECT owner, table_name FROM all_tables;
SQL> SELECT view_name FROM all_views;
SQL> exit

SAS Specific Instructions:

1) Edit the sas environment variables: (LINK)

$ nano /opt/sasinside/sashome/SASFoundation/9.4/bin/sasenv_local

CONTENT:
#ORACLE
export TNS_ADMIN=/etc
export ORACLE_BASE=/usr/lib/oracle
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export ORACLE_VERSION=12.2.0
export PATH=/usr/lib/oracle/12.2/client64/bin:$PATH
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib/oracle/12.2/client64/lib

2) At this point, I restarted my SAS environment. I do not know if that is really necessary or not. But I wanted to make sure that my changes went into effect.

3) I then got the super secret password and tested my libname statement: (LINK)

proc PWENCODE in="my_password"; run;

libname test oracle user=my_USERNAME password="{SAS002}DBCC5712369DE1C65B19864C1564FB850F398DCF" path=myhost schema=EDU_SCHEMA;

I got an error message, indicating that SAS was looking for Oracle 11, it included the phrase:

libclntsh.so.11.1: cannot open shared object file: No such file or directory

I knew from the manual, that when the installer originally setup the environment, there is a part of the install where he says what version of Oracle we are using. I was not able to find where to find that info after the fact. So this was my first indication of what he had put.

4) SAS was setup to use Oracle 11, not 12, so we have to create a symbolic link: (LINK)

$ cd /opt/sasinside/sashome/SASFoundation/9.4/sasexe
$ mv sasora sasora.bak   
$ ln -s orlax11 sasora
$ cd ${ORACLE_HOME}/lib   
$ ln -s libclntsh.so.12.1 libclntsh.so.11.1

5) Test my libname statement again:

libname test oracle user=my_USERNAME password="{SAS002}DBCC5712369DE1C65B19864C1564FB850F398DCF" path=myhost schema=EDU_SCHEMA;

Success! I see tables and data in those tables. Off, to let the professor know they can do their thing.

topic Tutorial: Setting Up Oracle Access in Red Hat Environment in Administration and Deployment

Tutorial: Setting Up Oracle Access in Red Hat Environment