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 220.127.116.11 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/libodbc.so) 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_DB_NAME = H1X
HS_FDS_CONNECT_INFO = H1X <===== This is the DSN name that comes from step 2 /etc/odbc.ini
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
Step 7 – Create listener.ora
Step 8 – Start listener
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.
And test it with tnsping
Step 11 – Create DB link in oracle database and run a SELECT command. Commands looks like this.
CREATE PUBLIC DATABASE LINK H1X CONNECT TO
“SYSTEM” IDENTIFIED BY “password” USING ‘dg4odbc’;
SQL> select * from dummy@H1X;
Hope this helps some one in need !