|
项目背景：
Oracle数据库11.2.0.1版本，linuxcentos 6.5系统，单实例
Sqlserver 2008windows2008
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、安装
在服务器上安装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:uoracleinstallpic*.png）：
3、配置
安装后在会ORACLE_HOME下生成dg4msql目录，
）修改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_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 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1522))
SID_LIST_LISTENER_getways =
(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 =
(ADDRESS_LIST =
(CONNECT_DATA =
(SERVICE_NAME = dg4msql)
(HS = OK) #此处表示连接的非数据库
PD12190 =
LISTENER_POWERDES =
测试一下：
[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用户登录创建：
createdatabaselink tg4msql connecttosaidentifiedby“ipva@07”
using'(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.90)(PORT=1521))
)
(CONNECT_DATA=
(SID=dg4msql)
(HS=OK)
)';
然后用dw帐号连接数据库，在中通过dblink执行查询功能，证明从建立到sqlserver的成功了：
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
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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
