Author:-SOUMEN KUMAR DAS

 

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

 

Hostname Port DB Name
Oracle Database (Source) esesslxXXXX.world 1521 ORAPROD1
SQLSERVER Data base (Target) ESESSMW9999.world 1433 SQLDB_PROD
Oracle Gateway esesslxXXXX.world 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 .
esesslxXXXX.world:ORAPROD1:> telnet ESESSMW9999.world 1433
Trying 153.88.xxx.xx...
Connected to ESESSMW9999.world.
  • 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.

esesslxXXXX.world:ORAPROD1:> 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
#
HS_FDS_CONNECT_INFO=[ESESSMW9999.world]:1433//SQLDB_PROD
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
  • Add below entry to /opt/oracle/12.2.0.1/db/network/admin/listener.ora
LISTENER_DG4MSQL =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= TCP)(Host= esesslxXXXX.world)(Port= 1522))
  )

SID_LIST_LISTENER_DG4MSQL =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME= /opt/oracle/12.2.0.1/db/dg4msql)
     (ENV=LD_LIBRARY_PATH=/opt/oracle/12.2.0.1/db/dg4msql/driver/lib:/opt/oracle/12.2.0.1/db/lib/)
      (PROGRAM = dg4msql)
    )
      (SID_DESC =
      (SID_NAME = dg4msql1)
      (ORACLE_HOME= /opt/oracle/12.2.0.1/db/dg4msql)
 (ENV=LD_LIBRARY_PATH=/opt/oracle/12.2.0.1/db/dg4msql/driver/lib:/opt/oracle/12.2.0.1/db/lib/)
      (PROGRAM = dg4msql)
    )
  )
  • Start newly created Listener  for sql server gateway for oracle .
esesslxXXXX.world:ORAPROD1:> lsnrctl stat LISTENER_dg4msql
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 29-JAN-2018 10:49:48
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=esesslxXXXX.world)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_dg4msql
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - 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/12.2.0.1/db/network/admin/listener.ora
Listener Log File         /opt/oracle/12.2.0.1/db/network/log/listener_dg4msql1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=esesslxXXXX.world)(PORT=1522)))
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 = 
(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(Host = esesslxXXXX.world)(Port = 1522))
(CONNECT_DATA= (SID=dg4msql))
   (HS=OK))
  • Check tnsping  for sql server gateway for oracle .
-bash-3.00$ tnsping dg4msql
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 16-MAR-2018 11:27:09
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/12.2.0.1/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = esesslxXXXX.world)(Port = 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.
SQL> CREATE PUBLIC DATABASE LINK SQLDB_DBLINK CONNECT TO <username> IDENTIFIED BY <password> using 'dg4msql'; 

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *