1 Background
Often in many IT projects (involving Oracle as the primary RDBMS), we face an issue of bringing data from disparate systems. Now, the problem can be solved by implementing any ETL solution which has the required connectors/drivers to connect to different heterogeneous database systems and pull the required data. Often situation demands that not only an operational RDBMS should connect and fetch data from another RDBMS but many cases the requirement is to fetch data from an OLAP system (e.g. SAP HANA). However, this requires considerable effort for the Services/Consulting team to implement the ETL solution and seldom is it cost effective in nature which elevates the CAPEX. In order to alleviate this issue, another technical solution can be implemented which leverages one of the existing functionalities provided by Oracle from 11G onwards. It is called a Database Gateway. This Database Gateway uses an ODBC connection (driver) to connect to any third party database. Although the basic solution is covered under Oracle knowledge document 561033.1 (which talks about connection from Oracle to MS SQL Server hence this is an OLTP to OLTP), there is a good amount of practical knowledge required for implementing the overall solution. This independent knowledge document will serve as a ready reckoner for all personnel who do not have access to Oracle knowledge base as well as for those who have gone through the knowledge document but experiencing real life issues which were faced by the project team. This is the rudimentary purpose for collating that additional information in this document. Also this is one of a kind attempt to document connection between OLAP and OLTP avoiding any traditional ETL setup.
2 Use Case
As discussed earlier, in one of the IT projects, we required data to be pulled on-the-fly from another database and join with the oracle table. In our case the data source was SAP HANA. Since the requirement was to infuse HANA data with Oracle data on-the-fly (directly in the visualization layer), query performance was of paramount importance. Although it was discussed and agreed that this approach will incur some sort of performance degradation, we wanted to make sure that the final user experience suffers minimal impact.
The proposed technical solution deals with connectivity between Oracle and HANA although the same can be leveraged to connect other RDBMS/OLAP from Oracle too.
3 Target Audience
The target audience are the system designer/developer who intends to implement the above mentioned or use case of same likelihood use case in their project.
4 Tools and Technology Used
Oracle Database 11g R2 64 bit, Win 2002 64 bit, Oracle Gateway 64 bit, HANA ODBC Driver 1.00.120.48, UNIX ODBC Driver.
5 Application Architecture
Following diagram shows the application architecture of the overall solution. Machine 1 hosts the Oracle Server where the heterogeneous driver is installed for the target database. For this case, HDODBC driver with a version number of 1.00.120.48 from SAP SE has been used. Oracle Gateway can be installed in the same host or a different host (for this case, it is the same as server). For this use case, Machine 1 is assumed to be a WINDOWS SERVER.
6 Basic Configuration Information
The Oracle Database Gateway for ODBC comes on a separate CD. It can be installed into an existing 11gR2 database Oracle_Home (please note: if the Oracle_Home contains an already patched release of the database, you MUST apply this patch set again. The reason is the gateway installation might overwrite already patched libraries with the base version as delivered on the CD. To get a proper environment, one should again reapply the already applied patch set).
Link for oracle gateway 12C(12102):
- HANA client installation software (This includes the HANA ODBC HDBODBC driver, the version used was 1.00.120.248 and the driver file name was LIBODBCHDB.dll)
- Oracle Gateway installation software (A detailed procedure can be found in Appendix 2)
- Setup of ODBC connection
- Creation of the database link.
- CREATE PUBLIC DATABASE LINK DB_LINK CONNECT TO “SYSTEM” IDENTIFIED BY “password” USING ‘Dg4odbc’;
- Here DB_LINK is the name of the database link. Dg4odbc is the name of the system DSN.
After the installation of Oracle Gateway, the following items must be configured:
- ODBC SYSTEM DSN
- listener (in the ORACLE_HOME where the gateway software is installed)
- tnsnames (in the ORACLE_HOME where the Oracle database software is installed)
- init<SID>.ora of the hs subsystem (in the ORACLE_HOME where the gateway software is installed)
- Oracle database
Please open the ODBC Administrator (make sure to use the 64bit ODBC Administrator and configure a 64bit ODBC driver when using 64bit DG4ODBC) and configure a SYSTEM DSN choosing your ODBC driver and following its onscreen dialog. To connect to HANA, HANA odbc driver HDBODBC must be selected while creating the system DSN. It is assumed that HANA client and HANA ODBC Driver are installed.
1) GATEWAY – LISTENER: The listener needs a new SID entry like the following. The listener.ora file resides inside $ORACLE_GATEWAY_HOME/Network/Admin directory. Please ensure that sid name matches the System DSN name.
(SID_DESC =
(PROGRAM = dg4odbc)
(SID_NAME = dg4odbc)
(ORACLE_HOME = D:\oracle\product\11.2.0)
)
Please correct the ORACLE_HOME entry so that it matches the location where you have installed your gateway to.
A listener.ora file with a listener listening on port 1521 might look like. Since now all the connections would be done via oracle gateway, please ensure to configure the pertinent SIDs in the listener.ora file in case you want to connect the database with other clients such as Sqldeveloper, Toad etc.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=E:\product\11.2.0\tg_3)
(PROGRAM=dg4odbc)
)
(SID_DESC=
(SID_NAME=DEVINT)
(ORACLE_HOME=E:\product\11.2.0\tg_3)
)
)
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=<gateway host>)(PORT=1521))
)
The listener must be stopped and started after changing the listener.ora file. Use LSNRCTL STOP and START. Make sure that the proper ORACLE_SID is exported from the command prompt to ensure proper environment.
SET ORACLE_SID=<SID>
2) ORACLE DB – TNSNAMES: The tnsnames.ora used by the ORACLE DATABASE needs an entry for the DG4ODBC alias:
dg4odbc.de.oracle.com =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname of the Oracle Gateway Server>)(PORT=1511))
(CONNECT_DATA=
(SID=dg4odbc))
(HS=OK)
)
The domain of the tns alias can differ from the one used above (de.oracle.com), depending on the parameter in the sqlnet.ora:
- NAMES.DEFAULT_DOMAIN = se.oracle.com [This implementation is hosted in EU based data center.]
The most important entry is the (HS=OK) keyword. (HS=) is also a valid entry, but DBCA and NetCA will only recognize (HS=OK) entries and remove any (HS=) entries.
This keyword clearly identifies whether to make a connection point via the Oracle Heterogeneous service or not (which should be a mandatory clause for our case).
After adding the tnsnames alias and restarting the listener, a connectivity check is to use tnsping <alias>.
<command> tnsping dg4odbc
This should come back with a success message.
3) GATEWAY – INIT.ORA:
There are some restrictions on how to name the SID (described in the Net Administrators Guide in detail).
At this place only a short note: don’t use dots in the SID and keep it short!
The SID is also relevant for the initialisation file of the gateway. The name of the file is init<SID>.ora. In this example it is called initdg4odbc.ora. The file is located at %<ORACLE_HOME of the gateway>%/hs/admin.
It should contain at least the connect details:
###############################################################################
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent and also to ensure compatibility across disparate data types across the disparate databases.
#
# HS init parameters
HS_FDS_CONNECT_INFO = <ODBC SYSTEM DSN>
HS_FDS_TRACE_LEVEL = off
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
HS_NLS_LENGTH_SEMANTICS=CHAR
Short explanation of the Heterogeneous Services Initialization parameters:
- HS_FDS_CONNECT_INFO:
- HS_FDS_TRACE_LEVEL:
- HS_NLS_NCHAR:
- HS_LANGUAGE:
- HS_KEEP_REMOTE_COLUMN_SIZE:
- HS_NLS_LENGTH_SEMANTICS:
4) ORACLE DB – CONFIGURATION:
The only thing that must be done here is to create a database link: connect with the username/password that has sufficient rights to create a database link (i.e. system).
The syntax is:
create [public] database link <name>
connect to <UID> identified by <pwd> using ‘<tnsalias>’;
In other words, to connect to a HANA Server, the syntax looks like:
CREATE DATABASE LINK db_link
CONNECT TO “SYSTEM” IDENTIFIED BY “PASSWORD” USING ‘dg4odbc’;
The db link name is db_link. Username and password must be in double quotes. ‘dg4odbc’ points to the alias in the tnsnames.ora file that calls the HS subsystem.
If everything is configured correctly, a select of a SQL Server table should be successful:
SQL> select * from dummy@db_link;
DUMMY
——–
X
In case the following oracle errors (ORA-28513 and/or ORA-02063) are encountered, please take the below measures:
Set the HS_LONG_PIECE_TRANSFER_SIZE=1024 parameter to the init file of the Gateway.
(This file is located in the %ORACLE_GATEWAY_HOME%/hs/admin directory)
7 Security Assumptions
- OLTP application access to the application UI pages is done with built in security and SSO.
- Database connection to HANA is done through a service account/pw combination.
- OLTP Oracle database connection is using a jdbc connection with built-in service account/pwd combination.
8 Differentiator & Conclusion
This academic document will serve as a ready reckoner for IT professionals who seek a non-traditional way of infusing multiple heterogeneous database services (from oracle perspective) into a workable solution. However the key differentiator is that the default oracle documentation covers heterogeneous connectivity between Oracle and MS Sql Server where the connection nature is OLTP to OLTP. This document covers the heterogeneous connectivity between Oracle and HANA where the connection nature is OLTP to OLAP. To the authors best of knowledge, this is a first of a kind use case which has been established and documented. The purpose of this document is to facilitate and accelerate solution design, should this particular use case or use cases of same likelihood be explored and sought after.
9 Authors
Arnab is an Integration Expert. He has close to 13 years of IT experience in Enterprise Application Integration using middleware and SOA based solutions. He is currently associated with Ericsson India Global Services.
Debasis is a Database Champion. He has 13 years of IT experience in managing Oracle databases with different scales and complexity digress. He is currently associated with Ericsson India Global Services.
Sayak is an Information Architect. He has 13 years of IT experience in the field of Information Management implementing Telecom Industry models and working in E2E Business Intelligence Transformation programs. He is currently associated with Ericsson India Global Services.
Thanks for the tips shared on your own blog. Something else I would like to talk about is that fat loss is not about going on a fad diet and trying to shed as much weight as possible in a couple of days. The most effective way in losing weight is by acquiring it slowly and obeying some basic tips which can make it easier to make the most from your attempt to slim down. You may realize and be following some tips, nonetheless reinforcing know-how never does any damage.
Your comment is awaiting moderation.