Installing Database Gateway for MSSQL Server

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 

 --------------------------------------------------------------------------------------------
Post Installation Configuration

 Edit tnsnames file in Database Home

D:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

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

dg4msql=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=T-01.HOME.LOC)
         (PORT=1621)
      )
      (CONNECT_DATA=
         (SID=dg4msql))
      (HS=OK))

-----------------------------------------------------------------------
Add service entry in LISTENERDG file under Database Gateway home

D:\product\11.2.0.3\dg_1
-----------------------------------------------------------------------


SID_LIST_LISTENERDG=
   (SID_LIST=
      (SID_DESC= 
         (SID_NAME=dg4msql)
         (ORACLE_HOME=D:\product\11.2.0.3\dg_1)
         (PROGRAM=dg4msql)
      )
      )


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

Open a new command prompt as administrator and set oracle home to dg home

set oracle_home=D:\product\11.2.0.3\dg_1

stop DG Listener

lsnrctl stop listenerdg

start DG Listener

lsnrctl start listenerdg

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


NOW again set oracle_home to database home and tnsping dg4msql service

C:\Users\farif>set oracle_home=D:\app\oracle\product\11.2.0\dbhome_1

ping dg4msql 

C:\Users\farif>tnsping dg4msql

TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 17-APR-2
013 11:20:48

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
D:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=T-01.HOME.LOC) (PORT=1621)) (CONNECT_DATA= (SID=dg4msql)) (HS=OK))
OK (100 msec)

C:\Users\farif>

------------------------------------------------------------------------------------------
create database link using username and password of SQLServer


CREATE PUBLIC DATABASE LINK "NEWHOME.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO
IDENTIFIED BY
USING 'dg4msql';

Database link created.

-------------------------------------------------------------------------------------------
connect to sqlplus and check link is working or not

SQL> select * from dual@NEWHOME.REGRESS.RDBMS.DEV.US.ORACLE.COM;

D
-
X

SQL>