Using SAP HANA Smart Data Access to get HANA and Oracle working together

SAP HANA smart data access (SDA) allows you to access remote data as if the data were stored in local tables in SAP HANA, without copying the data into SAP HANA.

Database Version SAP HANA 2.0 release version Adapter name
Oracle 11.2.0.1 SPS 03 or later oracle
Oracle 12.1.0.2 SPS 00 or later oracle
Oracle 12.2.0.1 SPS 03 or later oracle
Oracle(4) 12.2.0.2 (18c) SPS 05 or later oracle
Oracle(4) 12.2.0.3 (19c) SPS 05 or later oracle

Reading all Information above we will use for this Demo

  • HANA SPS05 (exactly  2.00.055.00.1615413201) running on SuSE Enterprise 15 SP3
Serverhostname: srvhana42  SID: H42 Instance: 00
  • Oracle Database Express Edition (XE) Release 18.4.0.0.0 (18c) (https://www.oracle.com/database/technologies/xe18c-downloads.html) running on Windows Server 2016
  • Oracle Instant Client Downloads for Linux x86-64 (64-bit)
    • Version 18.5.0.0.0:
    • instantclient-basic-linux.x64-18.5.0.0.0dbru.zip (All files required to run OCI, OCCI, and JDBC-OCI applications)
    • instantclient-odbc-linux.x64-18.5.0.0.0dbru.zip (Additional libraries for enabling ODBC applications)
  • unixODBC http://www.unixodbc.org/

Installing the Oracle Database can be done via the official Documentation: https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinw/installation-guide.html

After the Installation we use the Oracle SQL Developer to connect to the default created service called “XEPDB1” and create a test table called “A_ORA_HANA_TEST”

The default user here is SYSTEM and the password is “Passw0rd”.


Official Documentation:

SAP HANA Administration Guide for SAP HANA Platform -> /Data Access/SAP HANA Smart Data Access/ -> Setting Up ODBC Drivers (2.0 SPS 06)

SAP HANA Administration Guide for SAP HANA Platform -> /Data Access/SAP HANA Smart Data Access/ -> Setting Up ODBC Drivers -> Oracle Database ODBC Driver (2.0 SPS06)

IMPORTANT: In a scale-out landscape, install the driver on all hosts and make sure all below modified files are the same on each host.

STEP 1: After downloading the 2 Oracle Components and unixODBC the folder with access by the HANA Server should look like this:

srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # ll -h
total 72M
-rwxrwxrwx 1 nobody nobody  70M Mar  9 09:58 instantclient-basic-linux.x64-18.5.0.0.0dbru.zip
-rwxrwxrwx 1 nobody nobody 637K Mar  9 09:59 instantclient-odbc-linux.x64-18.5.0.0.0dbru.zip
-rwxrwxrwx 1 nobody nobody 1.8M Mar  9 10:01 unixODBC-2.3.4.tar.gz

STEP 2: Unpacking of files:

srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # gzip -dk unixODBC-2.3.4.tar.gz
srvhana44:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # tar xvf unixODBC-2.3.4.tar
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # unzip instantclient-basic-linux.x64-18.5.0.0.0dbru.zip
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 #  unzip instantclient-odbc-linux.x64-18.5.0.0.0dbru.zip
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # ll -h
total 87M

-rwxrwxrwx 1 nobody nobody 70M Mar  9 09:58 instantclient-basic-linux.x64-18.5.0.0.0dbru.zip
-rwxrwxrwx 1 nobody nobody 637K Mar  9 09:59 instantclient-odbc-linux.x64-18.5.0.0.0dbru.zip
drwxr-xr-x 2 root  root  4.0K  Mar 10 13:28 instantclient_18_5
drwxrwxr-x 2 saprouter Saprouter  8.0K Aug 31 2015 unixODBC-2.3.4
-rwxrwxrwx 1 nobody  nobody  9.5M  Mar  9 10:01 unixODBC-2.3.4.tar
-rwxrwxrwx 1 nobody  nobody  1.8M  Mar  9 10:01 unixODBC-2.3.4.tar.gz

STEP 3: Installing unixODBC:

For installing unixODBC on SuSE 15 SP3, the SuSE Developer Tools Module has to be installed and activated via yast. Then run “zypper install –type pattern devel_basis” to fully install the C compiler setup.
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # cd unixODBC-2.3.4/
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18/unixODBC-2.3.4 # ./configure
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18/unixODBC-2.3.4 # make
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18/unixODBC-2.3.4 # make install

unixODBC libraries are now installed into path /usr/local/lib

STEP 4: Copy the Oracle ODBC driver package to the Host Machine:

srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18/unixODBC-2.3.4 # cd ..
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # mkdir /oracle_drivers/
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # cp -r instantclient_18_5/ /oracle_drivers/
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # ll -h /oracle_drivers/
total 4.0K

drwxr-xr-x 4 root  root 4.0K Mar 10 14:01 instantclient_18_5

STEP 5: Testing ISQL for the <sid>adm user (h42adm in this sample) and setting environment variables

srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # su -l h42adm
h42adm@srvhana42:/usr/sap/H42/HDB00> cd
h42adm@srvhana42:/usr/sap/H42/home> isql –help
isql: error while loading shared libraries: libodbc.so.2: cannot open shared object file: No such file or directory

This error will show up when the environment variables are missing. These will be set in a file called .customer.sh in the <sid>adm home folder. The .customer.sh is mentioned as subfile in the usual .bash.rc which has to be kept default. Below you will find an export of .bash.rc as it is in default format.

h42adm@srvhana42:/usr/sap/H42/home> cat .bashrc 

#  *********************************************************************
# ***********************************************************************
# ****                                                                                         ****
# ***           $HOME/.bashrc                                                        ***
# ***           init script for BOURNE and KORN-shell,                  ***
# ***           sourced by $HOME/.profile                                       ***
# ***                                                                                             ***
# ***                                                                                             ***
# ***           To allow correct administration for SAP R/3, the       ***
# ***           following section in this file is required and may        ***
# ***           not be changed:                                                         ***
# ***                                                                                              ***
# ***             # SAP environment                                                  ***
# ***                                                                                              ***
# ***           To facilitate Hotline Support nothing else should be  ***
# ***           changed. If necessary site-specific modifications      ***
# ***           can be included in the file .customer.sh                     ***
# ***           which will be sourced by this script. See Section :    ***
# ***                                                                                             ***
# ***             # User specific environment                                    ***
# ***                                                                                             ***
# ***           Copyright (c) 2000 SAP-AG                                      ***
# ***                                                                                             ***
# ****                                                                                          ****
# ***********************************************************************
#  *********************************************************************

# set the env variable USER for sapgenpse
USER=`id | awk -F\( ‘{print $2}’ | awk -F\) ‘{print $1}’`
export USER

# SAP environment
if [ -f $HOME/.sapenv.sh ]; then
. $HOME/.sapenv.sh
fi # SAP environment

# User specific environment
if [ -f $HOME/.customer.sh ]; then
. $HOME/.customer.sh
fi # User specific environment

Create the .customer.sh file with the below content.

h42adm@srvhana42:/usr/sap/H42/home> vi .customer.sh

# User specific environment variables unixODBC and HANA SDA related
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/oracle_drivers/instantclient_18_5
export ODBCINI=$HOME/.odbc.ini
export TNS_ADMIN=~/

h42adm@srvhana42:/usr/sap/H42/home> exit
logout

srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # su -l h42adm
h42adm@srvhana42:/usr/sap/H42/HDB00> cd

h42adm@srvhana42:/usr/sap/H42/home> isql –help
unixODBC 2.3.4

STEP 6: Creating the file tnsnames.ora in the <sid>adm home folder using the content below. There has to be an empty space at the last line! (also review this KBA: 2869176 – SDA connection to Oracle DB failed with error: Data source name not found and no default driver specified)

h42adm@srvhana42:/usr/sap/H42/home> vi tnsnames.ora

ORCL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.80)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XEPDB1)
)
)

STEP 7: Creating / Modify the file .odbc.ini in the <sid>adm home folder using the content below.

h42adm@srvhana42:/usr/sap/H42/home> vi .odbc.ini

[ORA18C]
Driver=/oracle_drivers/instantclient_18_5/libsqora.so.18.1
ServerName=ORCL

h42adm@srvhana42:/usr/sap/H42/home> exit
logout

srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # reboot

! This reboot of the complete Server is required !

STEP 8: Connection test using unixODBC. If this is NOT working, HANA will also fail. The unixODBC test using ISQL is the base all is build on to.

h42adm@srvhana42:/usr/sap/H42/home> isql -v ORA18C SYSTEM MySecurePassword
+—————————————+
| Connected!
|
| sql-statement
| help [tablename]
| quit
|
+—————————————+
SQL> quit

STEP 9: Making sure all files are ready and have the correct permissions

h42adm@srvhana42:/usr/sap/H42/home> ls -al

total 120

drwxr-xr-x 11 h42adm sapsys 4096 Mar 21 12:38 .
drwxr-xr-x 6 h42adm sapsys 75 Jun 30 2021 ..
-r-xr-xr-x 1 h42adm sapsys 2253 Mar 21 12:38 .bashrc
-rw-r—– 1 h42adm sapsys 181 Mar 21 12:38 .customer.sh
-rw-r—– 1 h42adm sapsys  84 Mar 11 10:16 .odbc.ini
-rwxr-x— 1 h42adm sapsys 3021 May 25  2020 .sapenv.sh
-rw-r—– 1 h42adm sapsys 214 Mar 11 10:15  tnsnames.ora

STEP 10: HANA connection setup in HANA Studio

Source Name: Oracle_Test
Adapter Name: ORACLE (GENERIC ODBC)
Source Location: indexserver
Connection Properties:
-> Adapter Version: 12c
-> Connection Mode: Data source name
-> Configuration file: property_orcl.ini
-> Data Source Name: ORA18C
-> DML Mode: readonly

Credentials:
-> Credentials Mode: Technical user
-> User Name: SYSTEM
-> Password: Passw0rd

As alternative you can use this SQL command:

CREATE REMOTE SOURCE ORA_XEPDB1 ADAPTER “oracle” CONFIGURATION ‘DSN=ORA18C’ WITH CREDENTIAL TYPE ‘PASSWORD’ USING ‘user=SYSTEM;password=Passw0rd’;

SAP HANA Administration Guide for SAP HANA Platform / Create an Oracle Remote Source

Conclusion:

After reading this Blog post carefully and following it step by step you will have a working SDA (Smart Data Access) connection to an Oracle Server.

If there are still left over questions or any suggestion, please use the comment section below.