[原]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
（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