ORACLE 通过gateway组件建立dblink连接sqlserver数据库的实战详细过程

 

 

项目背景：

 

Oracle数据库11.2.0.1版本，linuxcentos 6.5系统，单实例

Sqlserver 2008版本，windows2008系统，单实例

 

 

1、下载地址：

（1）oracle官网

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html里面有下载gateway的

 

（2）云盘

链接: http://pan.baidu.com/s/1nv8Qnpv 密码: 3u3g

 

 

 

2、安装

在Oracle服务器上安装gateway（11gR2 gateway是单独的安装包：11.2.0.4.0x64是第五个安装包），下载下来是linux.x64_11gR2_gateways.zip这样一个压缩包，安装的时候和安装oracle软件一样，通过vnc viewer远程连接进去，解压缩，然后点击./runInstaller进行安装，主要步骤如下所示：

 

unzip  linux.x64_11gR2_gateways.zip

cd  gateways

export  DISPLAY=192.168.121.90:1

xhost +

（如果报xhost:  unable to open display  “192.168.121.90”的提示，则换成如下命令

export  DISPLAY=localhost:1

xhost +

）

su – oracle

cd/oracle/gateways/gateways/

./runInstaller

（接下来就是图形化桌面安装窗口演示部分E:\u\oracle\install\pic\*.png）：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3、配置

安装后在会ORACLE_HOME下生成 dg4msql目录，

 

（1）修改initdg4msql.ora

 

 

[oracle@xxxxx admin]$ vim initdg4msql.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=[10.1xx.1xx.1xx]:1433.ipva_s0400_b2 # alternate connect format is hostname/serverinstance/databasename HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER

 

 

 

（2）修改$ORACLE_HOME/network/admin下的tnsnames.ora和listener.ora，添加sqlserver实例信息

[oracle@hch_test_121_90 admin]$ vim listener.ora   SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SID_NAME = dg4msql)  #此处为配置文件SID，要与initdg4msql.ora中的名字对应       (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)       (PROGRAM = dg4msql) #此处为配置文件目录名称，$ORACLE_HOME/dg4msql/admin/initdg4msql.ora     )     (SID_DESC =       (SID_NAME = powerdes)       (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)     )   )   LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1521))       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))     )   )   ADR_BASE_LISTENER = /home/oracle/app/oracle

 

 

当然如果想让网关监听信息不用1521端口也可以如下实验1522端口

LISTENER_getways =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1522))     )   ) SID_LIST_LISTENER_getways =   (SID_LIST =     (SID_DESC=       (SID_NAME = dg4msql)       (ORACLE_HOME =/home/oracle/app/oracle/product/11.2.0/dbhome_1)       (PROGRAM = dg4msql)     )   )

 

 

 

[oracle@hch_test_121_90 admin]$ vim tnsnames.ora         (SERVER = DEDICATED)       (SERVICE_NAME = powerdes)     )   )   dg4msql =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = dg4msql)     )     (HS = OK)  #此处表示连接的非oracle数据库   )   PD12190 =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = powerdes)     )   )   LISTENER_POWERDES =   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1521))

 

 

测试一下：

[oracle@hch_test_121_90 admin]$ tnsping dg4msql   TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-SEP-2016 21:10:26   Copyright (c) 1997, 2009, Oracle.  All rights reserved.   Used parameter files: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora     Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dg4msql)) (HS = OK)) OK (20 msec) [oracle@hch_test_121_90 admin]$

 

 

 

 

4、在oracle服务器上创建dblink

 

PS：事先需要保证远程的sqlserver数据库是正常运行的。

 

先建立连接sqlserver的dblink：

# 先分配用户创建db link的权限 SQL> grant create database link to dw;   Grant succeeded.   SQL>     # 然后使用dw用户登录创建db link： create database link tg4msql connect to sa identified by “ipva@07”            using '(DESCRIPTION =             (ADDRESS_LIST =              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1521))            )            (CONNECT_DATA =               (SID = dg4msql)            )            (HS = OK)          )';

 

 

然后用dw帐号连接oracle数据库，在oracle中通过dblink执行查询功能，证明从oracle建立到sqlserver的dblink成功了：

SQL> select * from Dict_City@tg4msql2 where rownum < 5;      city_id province_id CityResCode ---------- ----------- ---------------------------------------- CityNote                           Status CreateTime      ModifyTime ---------------------------------------- ---------- ------------ ------------ DeleteTime   WeatherDate ------------ ------------         2        1 1_Beijing SP2-0784: Invalid or incomplete character beginning 0xAC returned        242        2 2_Chang Le                                                                                                                                                             NP                                               1 05-FEB-12    05-FEB-12            159        2 2_Fu An SP2-0784: Invalid or incomplete character beginning 0x89 returned        158        2 2_Fu Ding      city_id province_id CityResCode ---------- ----------- ---------------------------------------- CityNote                           Status CreateTime      ModifyTime ---------------------------------------- ---------- ------------ ------------ DeleteTime   WeatherDate ------------ ------------ y.                                          1 05-FEB-12     05-FEB-12       SQL>

 

 

参考文章：

http://blog.csdn.net/lk_db/article/details/51262331

http://blog.csdn.net/u010257584/article/details/50844708