ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 594|回复: 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>';





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

本版积分规则

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