Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 

Firstly thanks to my customer for the situation that forced me to explore this scenario.

I was unable to find a simple document on this explaining such a setup. Hence this blog.

The scenario is to access HANA tables from within Oracle via database links.

Source database : SAP HANA SP 08 Rev 85

Target database : Oracle running on RHEL 6.x X64

In middle Oracle gateway running on RHEL 6.x X64

Target oracle database will migrated to HANA in 6 months, but in the interim period we need to access HANA data sitting inside oracle seamlessly to refresh few materialized views.

After evaluating options such as CSV file export/import, ETL tools, SAP SLT, Data Services, etc, the favorable option was Oracle Gateway.

To get this Oracle Gateway running was quite a work for the first time. Therefore this blog, to help others.

The way it should works is :-

From within oracle database if a sql statement like SELECT * FROM DUMMY@H1X; is fired, it should bring the data from SAP HANA database (H1X).

First some basics, which is important to understand.

How does it work?

SQL commands are fired from a oracle database (sqlplus), which will reach out for Oracle Gateway via DBLINK > tnsnames.ora

Oracle Gateway will have parameter in its init<sid>.ora file and loads unixODBC libraries from its LD_LIBRARY_PATH.

unixODBC will load HANA odbc drivers and goes through DSN setting to read data from HANA database.

Meaning Oracle DB > DBLINK > tnsnames.ora > Oracle Gateway > unixODBC drivers > Data source DSN (odbc.ini) > HANA odbc drivers > HANA DB

If you notice above Oracle Gateway and HANA odbc do not talk to each other directly. Instead talk through unixODBC drivers.

This is the most important to understand, else you will not be able to setup this correctly.

Step by step - How to setup the above scenario

Step 1 - First step is to make unixODBC working

Installed unixODBC rpms (both 32 and 64 bit) on RHEL machine where you will run Oracle Gateway.





Step 2 - Install SAP HANA client (64bit) on RHEL machine where you will run Oracle Gateway. Please refer to the HANA client installation guide.

Step 3 - Create /etc/odbc.ini   contents looks like below




Step 4 - Install Oracle Gateway software. You may please google to find step-by-step on how to install using Oracle Universal Installer.

Step 5 - Set environment variable of user running oracle gateway. In my case it is


Its very important to have 64bit unixODBC libraries path (/usr/lib64/ in LD_LIBRARY_PATH because we run RHEL x64 and Oracle 64 bit software.

Step 6 - Create init<sid>.ora. In my case I will call this as dg4odbc (initdg4odbc.ora). The content should like like below.


HS_FDS_CONNECT_INFO = H1X                                   <===== This is the DSN name that comes from step 2 /etc/odbc.ini





set ODBCINI=/etc/odbc.ini

Step 7 - Create listener.ora














Step 8 - Start listener

lsnrctl start

Step 9 - Lets first test unixODBC is working

Login as user which will run oracle gateway and check LD_LIBRARY_PATH (refer step 4 above) and use below commands.

isql -v <DSN name from step 2 /etc/odbc.ini> <hana user name> <password>

For example isql -v H1X SYSTEM password


| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |


SQL> select * from dummy




| X    |


SQLRowCount returns 1

1 rows fetched

If you see these output you are half way through. unixODBC is working.

Now its time to work on oracle database from where data will be read with SELECT statements.

Step 10 - Add entries in tnsnames.ora   In my case it will look like below.

dg4odbc  =






And test it with tnsping

Step 11 - Create DB link in oracle database and run a SELECT command.    Commands looks like this.


"SYSTEM" IDENTIFIED BY "password" USING 'dg4odbc';

SQL> select * from dummy@H1X;




Hope this helps some one in need !

Labels in this area