clouddba:-DBA blog on Oracle,Oracle cloud,DevOps,PostgreSQL and Other Databases

This is mainly Oracle DBA blog which also cover performance tuning,oracle cloud dbaas,oracle rac dataguard,active dataguard,RMAN and other topics like Linux,AWS,Cassandra and other databases.Please subscribe below to get update on my blog.




Connecting to Microsoft SQL Server database from Oracle using SQL SERVER GATEWAY FOR ORACLE


Hostname Port DB Name
Oracle Database (Source) 1521 ORAPROD1
SQLSERVER Data base (Target) 1433 SQLDB_PROD
Oracle Gateway 1522 DG4MSQL


Following steps are involved..

  1. Download Oracle Database Gateways CD if you have not already installed it
  2. Install Oracle Database Gateway for Microsoft SQL Server
  3. Configure Database Gateway for Microsoft SQL Server (DG4MSQL).


  • Download Oracle Database Gateways from Oracle eDelivery site. (download the correct version)




  • Install Oracle Database Gateway for Microsoft SQL server
Unzip the downloaded zip file. It will create a directory named “gateways”
Change the directory to gateways and start installer.
-bash-3.00$ cd /software/11gR2-Gateways/gateways/
-bash-3.00$ ./runInstaller

Select PATH for the Oracle Home. The Path defaults to Oracle Home. No need to change the same



Once it prompts with above screen, open another shell and run the script as root

Click Close to Finish the installation.

  • Check if port is opened in target server (SQL SERVER) from Source (Oracle) for sql server gateway for oracle .> telnet 1433
Connected to
  • Configure Oracle Database Gateway for SQL SERVER GATEWAY FOR ORACLE .

Make sure that the MS SQL database details are correct in the dg4msql parameter file.> cat initdg4msql1.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
# HS init parameters
# alternate connect format is hostname/serverinstance/databasename
  • Add below entry to /opt/oracle/
        (ADDRESS= (PROTOCOL= TCP)(Host= 1522))

    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME= /opt/oracle/
      (PROGRAM = dg4msql)
      (SID_DESC =
      (SID_NAME = dg4msql1)
      (ORACLE_HOME= /opt/oracle/
      (PROGRAM = dg4msql)
  • Start newly created Listener  for sql server gateway for oracle .> lsnrctl stat LISTENER_dg4msql
LSNRCTL for Linux: Version - Production on 29-JAN-2018 10:49:48
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Alias                     LISTENER_dg4msql
Version                   TNSLSNR for Linux: Version - Production
Start Date                29-JAN-2018 10:41:11
Uptime                    0 days 0 hr. 8 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/
Listener Log File         /opt/oracle/
Listening Endpoints Summary...
Services Summary...
Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

  • Append following in tnsnames.ora  for sql server gateway for oracle .
 dg4msql = 
(CONNECT_DATA= (SID=dg4msql))
  • Check tnsping  for sql server gateway for oracle .
-bash-3.00$ tnsping dg4msql
TNS Ping Utility for Linux: Version - Production on 16-MAR-2018 11:27:09
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = = 1522)) (CONNECT_DATA= (SID=dg4msql)) (HS=OK))
OK (10 msec)
  •  Create a new database link pointing to this TNS using SQL Server login     credentials. This username must be already created in the Microsoft SQL Server database.

Database link created.
  • Connect and check tables from target Database.
SQL> select count(*) from T_PA_OBJECTS@SQLDB_DBLINK;

This concludes setup steps to read and write data of Microsoft SQL server tables from Oracle database.

Database Link Oracle to Heterogeneous Database Systems SAP HANA

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, 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 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 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.
  • 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:

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


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

















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





2) ORACLE DB – TNSNAMES: The tnsnames.ora used by the ORACLE DATABASE needs an entry for the DG4ODBC alias: =


(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname of the Oracle Gateway Server>)(PORT=1511))






The domain of the tns alias can differ from the one used above (, depending on the parameter in the sqlnet.ora:

  1. NAMES.DEFAULT_DOMAIN = [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.







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












Short explanation of the Heterogeneous Services Initialization parameters:




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:



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;




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.