Steps to create Database link from Oracle Database to Microsoft SQLServer Database:
Pre-requisites
1. Check Network communication is Opened from Oracle Database Server to MSSQL Database Server using telnet
2. Download the Oracle Gateway Server Software same as your Oracle Database version.
Installation of Oracle Gateway Software
=========
Hope you have downloaded the Dg4msql software for sql server
1> Create a directory as gateway under $ORACLE_BASE directory.
2) Invoke the GUI , Select Oracle Gateway for MYSQL and enter the sql server details at the time of installation
3) Before completing the installation, OUI invokes the NETCA and create a new listener for gateway with new port (ex:1526)
Note; In case if you have defined any bash profile for TNS_ADMIN, then please comment it
4. Follow the below note for pre-req for gateway listener
How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install ( Doc ID 562509.1 )
edit your gateway listener as recommended in the above note.
5. create a tnsentry in $ORACLE_HOME/network/admin
6. export TNS_ADMIN=$gateway home/network/admin
lsnrctl stop <gateway listener>
lsnrctl start <gateway listener>
tnsping <gateway alias>
conn / as sysdba
create public database link <test> connect to "<sql server user>" identified by "<password>" using '<tnsnames.ora>'
select * from dual@test;
Pre-requisites
1. Check Network communication is Opened from Oracle Database Server to MSSQL Database Server using telnet
2. Download the Oracle Gateway Server Software same as your Oracle Database version.
Installation of Oracle Gateway Software
=========
Hope you have downloaded the Dg4msql software for sql server
1> Create a directory as gateway under $ORACLE_BASE directory.
2) Invoke the GUI , Select Oracle Gateway for MYSQL and enter the sql server details at the time of installation
3) Before completing the installation, OUI invokes the NETCA and create a new listener for gateway with new port (ex:1526)
Note; In case if you have defined any bash profile for TNS_ADMIN, then please comment it
4. Follow the below note for pre-req for gateway listener
How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install ( Doc ID 562509.1 )
edit your gateway listener as recommended in the above note.
5. create a tnsentry in $ORACLE_HOME/network/admin
6. export TNS_ADMIN=$gateway home/network/admin
lsnrctl stop <gateway listener>
lsnrctl start <gateway listener>
tnsping <gateway alias>
conn / as sysdba
create public database link <test> connect to "<sql server user>" identified by "<password>" using '<tnsnames.ora>'
select * from dual@test;