Before we start we check a view requirements

andreas_kuhn · ‎2022 Apr 06

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

Which Oracle Version is supported by which HANA Version: 2600176 - SAP HANA Smart Data Access Supported Remote Sources

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	oracl
Oracle(4)	12.2.0.2 (18c)	SPS 05 or later	oracle
Oracle(4)	12.2.0.3 (19c)	SPS 05 or later	oracle

Which Client Components do we need:
SAP HANA Administration Guide for SAP HANA Platform / Oracle Database ODBC Driver
The official Documentation is just talking about the Oracle ODBC driver package, so we will download the Oracle driver perfectly matching the Server Version: https://www.oracle.com/de/database/technologies/instant-client/linux-x86-64-downloads.html

But we also need the unixODBC components in a specific version. To get this we use the Information about the supported Oracle Server version and the information about odbc drivers provided by oracle: https://www.oracle.com/database/technologies/releasenote-odbc-ic.html
unixODBC Driver Manager Version
Instant Client 18 , 19 & 21: 2.3.4
Instant Client 12.1 and 12.2: 2.3.1

Reading all Information above we will use for this Demo

HANA SPS05 (exactly 2.00.055.00.1615413201) platform edition running on SuSE Enterprise 15 SP3.
This Guide is NOT for SAP HANA Cloud!
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 Oracle

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

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/ -> S...

SAP HANA Administration Guide for SAP HANA Platform -> /Data Access/SAP HANA Smart Data Access/ -> S...

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

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 SAP Note: 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: After the reboot verify all DLLs can be found. Login as <sid>adm and run the command below.

h42adm@srvhana42:/usr/sap/H42/home> ldd -v /oracle_drivers/instantclient_18_5/libsqora.so.18.1

linux-vdso.so.1 (0x00007ffff65fa000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fe523cb4000)
libm.so.6 => /lib64/libm.so.6 (0x00007fe523973000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fe523753000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fe52353b000)
librt.so.1 => /lib64/librt.so.1 (0x00007fe523333000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007fe523131000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fe522f1a000)
libclntsh.so.18.1 => /oracle_drivers/instantclient_18_5/libclntsh.so.18.1 (0x00007fe51ef5c000)
libclntshcore.so.18.1 => /oracle_drivers/instantclient_18_5/libclntshcore.so.18.1 (0x00007fe51e979000)
libodbcinst.so.2 => /usr/local/lib/libodbcinst.so.2 (0x00007fe51e764000)
libc.so.6 => /lib64/libc.so.6 (0x00007fe51e38f000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fe51e176000)
/lib64/ld-linux-x86-64.so.2 (0x00007fe52417c000)
libmql1.so => /oracle_drivers/instantclient_18_5/libmql1.so (0x00007fe51df16000)
libipc1.so => /oracle_drivers/instantclient_18_5/libipc1.so (0x00007fe51daa4000)
libnnz18.so => /oracle_drivers/instantclient_18_5/libnnz18.so (0x00007fe51d34f000)
libons.so => /oracle_drivers/instantclient_18_5/libons.so (0x00007fe51d0ff000)
libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007fe51cef5000)

If you see a line like "libclntsh.so.12.1 => not found" pls. have a look at SAP Note 2928047 - libsqora.so.11.1' : file not found

STEP 9: 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 10: 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 11: 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

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.

2928047 - libsqora.so.11.1' : file not found

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.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My Account

My Account

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

Before we start we check a view requirements

Reading all Information above we will use for this Demo

Installing Oracle

HANA Server Preparation and connecting to Oracle using SDA

	unixODBC Driver Manager Version
Instant Client 18 , 19 & 21:	2.3.4
Instant Client 12.1 and 12.2:	2.3.1