查看: 1019|回复: 0

[范例] Create link from in Oracle to SQL Server

[复制链接]
认证徽章
论坛徽章:
0
发表于 2017-11-6 10:26 | 显示全部楼层 |阅读模式
Example of how to create link from Oracle database on Linux host to SQL Server using Database Gateway for ODBC.  This required USS to compile FreeTDS on Linux.  Unix admin compiled FreeTDS on server under /user/local/lib

1.  Install dg4odbc gateway
On linux, installed the 11.2.0.4 Database Gateway for ODBC by running .../gateways/runInstaller
Only select option for Oracle Database Gateway for ODBC 11.2.0.4.0 on the Available Product Components screen
image2016-5-11 16-41-15.png
Installed at /lv01/apps/oracle/oracle/product/11.2.0.4/gateway
Added [url=http://gateway/lv01/apps/oracle/oracle/product/11.2.0.4/gateway:NUMMY]gateway:/lv01/apps/oracle/oracle/product/11.2.0.4/gateway:NUMMY[/url] entry to /etc/oratab and created /dba/bin/gateway.env to setup the environment when needed

2.  Create Listener and start it
. oraenv gateway
cd $ORACLE_HOME/network/admin vi listener.ora
The ENVS=LD_LIBRARY_PATH needs to include path the unixODBC, FreeTDS  and ORACLE_HOME lib directories, pick an appropriate SID to distinguish what it is used for.
listener.ora from the unix server


LISTENER_DG4ODBC =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <host>(PORT = 1591))
    )
  )
SID_LIST_LISTENER_DG4ODBC =
  (SID_LIST =
    (SID_DESC=
      (SID_NAME=AMR)
      (ORACLE_HOME=/lv01/apps/oracle/oracle/product/11.2.0.4/gateway)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/usr/lib64:/usr/local/lib:/lv01/apps/oracle/oracle/product/11.2.0.4/gateway/lib)
    )
  )
ADR_BASE_LISTENER_DG4ODBC = /lv01/apps/oracle/oracle
lsnrctl start LISTENER_DG4ODBC

3.  Create init<SID>.ora file under $ORACLE_HOME/hs/admin.  SID matches SID used in listener above
Example. Created initAMR.ora file on bvdev42 with following contents.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = AMR
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=American_America.AL32UTF8
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_RPC_FETCH_REBLOCKING = OFF
#
# ODBC specific environment variables
#
set ODBCINI=/lv01/apps/oracle/oracle/product/11.2.0.4/gateway/hs/admin/odbc.ini


4.  Create odbc.ini in location mentioned in init<SID>.ora
[ODBC Data Sources]
AMR=FreeTDS
[AMR]
Driver=/usr/local/lib/libtdsodbc.so
Description=unixODBC connection to AMR MSSQL
Database=<sql server db name>
Server=<sql server host>
Port=<port>
TDS_Version=8.0
5.  Create tnsnames.ora entry
<Connect strings> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <host>(PORT = <port number>)
    )
    (CONNECT_DATA =
      (SID = AMR)
    )
    (HS = OK)
  )


6.  Create database link
create database link <link name> connect to "<sql server user>" identified by "<password>" using '<connect string>';





您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

【有奖讨论】解决存储挑战了解一下
奖品:米家车载空气净化器 、米家声波电动牙刷 、小米运动蓝牙耳机

在数字经济时代,井喷式增长的数据,在释放大量商业价值的同时,也随之对企业的IT基础设施带来了不容忽视的挑战!如何存储、管理、使用这些数据呢?这是一条比以往更艰难的路~

活动时间:9月20日-10月11日

参与讨论>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表