We want to create database link from Oracle database to SQL Server database,
Oracle Database 126.96.36.199.0 on Oracle Linux 6.5 x86_64
SQL Server 2008 on Windows Server 2008 R2
We need Software Oracle Database Gateways which can download from here
Here is step by step procedure for install Oracle Gateway and configure it for create database link from Oracle to SQL Server:
1) Install Oracle Database Gateways under ORACLE_HOME and check checkbox – Oracle Database Gateway for Microsoft SQL Server
2) Provide any existing Microsoft SQL Server details where you are planning to connect. It actually writes in configuration file and does not really verify
3) Run root script with root user
4) Make sure that the MS SQL database details are correct in the dg4msql parameter file.
vi $ORACLE_HOME/dg4msql/admin/initdg4msqltestdb.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=SQLServer_HOST_IP:1433//testdb # alternate connect format is hostname/serverinstance/databasename HS_FDS_TRACE_LEVEL=off #HS_FDS_RECOVERY_ACCOUNT=RECOVER #HS_FDS_RECOVERY_PWD=RECOVER HS_NLS_NCHAR = UCS2 # Oracle Database Characterset HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
5) Append following in existing listener.ora. Change the name, path and port as required.
vi /u0/app/grid/product/11.2.0/network/admin/listener.ora # listener.ora Network Configuration File: /u0/app/grid/product/11.2.0/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = my_oracle_db_ip)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u0/app/grid ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (PROGRAM = /u0/app/oracle/product/11.2.0/bin/dg4msql) (ENV = "LD_LIBRARY_PATH=/u0/app/oracle/product/11.2.0/dg4msql/driver/lib:/u0/app/oracle/product/11.2.0/lib") (SID_NAME = dg4msqltestdb) (ORACLE_HOME = /u0/app/oracle/product/11.2.0) ) )
6) Reload listener for get new configuration and Verify that the service is registered with the listener
lsnrctl reload LISTENER lsnrctl status LISTENER …… Service "dg4msqltestdb" has 1 instance(s). Instance "dg4msqltestdb", status UNKNOWN, has 1 handler(s) for this service...
7) Append following in tnsnames.ora
DG4MSQLTETSDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = my_oracle_host_ip)(PORT = 1521)) (CONNECT_DATA = (SID = dg4msqltestdb) ) (HS = OK) )
tnsping DG4MSQLTESTDB Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = my_oracle_host_ip)(PORT = 1521)) (CONNECT_DATA = (SID = dg4msqltestdb)) (HS = OK)) OK (50 msec)
9) create database link in oracle database, This username must be already created in the Microsoft SQL Server database with proper permissions.
CREATE DATABASE LINK testdb CONNECT TO “SQLUser” IDENTIFIED BY “SQLUserPass” USING ‘DG4MSQLTESTDB’;
10) test database link :
select count(1) from “SQLTable”@testdb;
If you want to create multiple Database Links from oracle Database to different Sql Server databases you need to create different initdg4msql.ora file under $ORACLE_HOME/dg4msql/admin with corresponding parameters and also add entries in listener.ora and tnsnames.ora files.