DBA Catalog: SQL Server

We want to create database link from Oracle database to SQL Server database,
Environment :
Oracle Database 11.2.0.3.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;

Note :
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.

DBA Catalog

Google it ....

Wednesday, October 28, 2015

Database Link between Oracle Database and SQL Server Database