Skip to Content
Technical Articles
Author's profile photo Andreas Kuhn

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.


Before we start we check a view requirements

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

Reading all Information above we will use for this Demo

  • HANA SPS05 (exactly platform edition running on SuSE Enterprise 15 SP3.
    This Guide is NOT for SAP HANA Cloud!
Serverhostname: srvhana42  SID: H42 Instance: 00

Installing Oracle

Installing the Oracle Database can be done via the official Documentation:

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”.

HANA Server Preparation and connecting to Oracle using SDA

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
-rwxrwxrwx 1 nobody nobody 637K Mar  9 09:59
-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
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 #  unzip
srvhana42:/nfs-share/SAP_InstallMedia/HANA_SDA/Oracle/18 # ll -h
total 87M

-rwxrwxrwx 1 nobody nobody 70M Mar  9 09:58
-rwxrwxrwx 1 nobody nobody 637K Mar  9 09:59
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: 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 in the <sid>adm home folder. The 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                     ***
# ***           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/ ]; then
. $HOME/
fi # SAP environment

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


Create the file with the below content.

h42adm@srvhana42:/usr/sap/H42/home> vi

# 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

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


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


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

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
-rw-r—– 1 h42adm sapsys  84 Mar 11 10:16 .odbc.ini
-rwxr-x— 1 h42adm sapsys 3021 May 25  2020
-rw-r—– 1 h42adm sapsys 214 Mar 11 10:15  tnsnames.ora

STEP 10: HANA connection setup in HANA Studio

Source Name: Oracle_Test
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 Mode: Technical user
-> User Name: SYSTEM
-> Password: Passw0rd

As alternative you can use this SQL command:


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


Other useful SAP Notes / KBAs:

2677618 – ORA-12541: TNS:no listener when creating SDA remote source to Oracle database

If you are configuring SAP HANA Smart Data Access (SDA) to Oracle SuperCluster, the unixODBC driver also needs to be configured as described in 2501150 – HANA SDA connection to Oracle fails with ORA-12504.


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.


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Hakan Haslaman
      Hakan Haslaman

      Great Blog! Thanks for this.

      Author's profile photo Venkata Giduthuri
      Venkata Giduthuri

      Is this applicable to SAP HANA Cloud?

      Author's profile photo Seungjoon Lee
      Seungjoon Lee

      No, this is not applicable to SAP HANA Cloud.

      Author's profile photo Diego Petrone
      Diego Petrone

      This was exactly what I needed to do. Thank you so much!