How to setup Oracle Database Gateway for SQL Server

Data Fetch on Oracle Server From SQL Server


Installation ORACLE GATEWAY FOR MS SQL



Install Database Gateway

 

 

Change home location if you want

 

 

Select Database Gateway for database vendor, here we are installing gateway for MSSQL Server 

 

 Add details of SQL Server (HOSTNAME, INSTANCE NAME, and DATABASE) 

 

 

Net Configuration Assistant will be started automatically, run wizard to create and configure Listener for database gateway

 

 

As already a listener is running for database named LISTENER so it is better to create a new listener with different name here we gave name LISTENEREG to datbase gateway listener 

 

Use any available port other than 1521 

 

 

 

 

 

 

 

 

IN MY CASE



--------------------------------------------------------------------------------------------

Post Installation Configuration

 Edit tnsnames file in Database Home

File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
--------------------------------------------------------------------------------------------

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

dg4msql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)

adventureDW =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521))
(CONNECT_DATA=(SID=adventureDW))
(HS=OK)
)

afcssql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521))
(CONNECT_DATA=(SID=afcssql))
(HS=OK)
)

-------------------------------------------------------------------------------------------------------

 Edit listener file in Database Home

File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
--------------------------------------------------------------------------------------------------------------------
 
 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =
        (ADDRESS_LIST=
                  (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.2)(PORT=1521))
)
 
SID_LIST_LISTENER=
      (SID_LIST=
            (SID_DESC=
                   (SID_NAME=dg4msql)
                   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                   (PROGRAM=dg4msql)
           )
            (SID_DESC=
                   (SID_NAME=adventureDW)                         # SQL DB NAME FOR TEMP
                   (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                   (PROGRAM=dg4msql)
         )
          (SID_DESC=
                 (SID_NAME=afcssql)                                         # SQL DB NAME
                 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
                 (PROGRAM=dg4msql)
       )
)

　
ADR_BASE_LISTENER = /u01/app/oracle



CREATE
------------------------------------------------------------------------------------------------------------
initadventureDW.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=[SQL2005MAGRANIA]:1433//AdventureWorksDW
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

initafcssql.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=[SQL2005MAGRANIA]:1433//AFCSSQL
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

　

initdg4msql.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=[SQL2005MAGRANIA]:1433//AdventureWorks
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

　

DATABASE LINK

That last step is to configure and test the db link….
 

CREATE PUBLIC DATABASE LINK 'mssqldb' CONNECT TO 'mssql_user' IDENTIFIED BY 'mssql_pass' USING '
dg4dmssqldb'

 
and test it….

SELECT * FROM MSSQLTABLE@

Wrap up

So far we’re seeing fast response times for our queries going through the gateway.  Hopefully the gateway will allow us to move our data from the staging environment to the ERP at the same pace before we started running into issues with the linked server.  Actually, I’m surprised by the performance of gateway, we’re able to move a table that is 58 columns wide and contains 138,000 records in about 13-14 seconds.