旧系统使用Oracle,新系统使用SQL Server,使用透明网关将旧系统的数据通过触发器更新到新系统中去。
一、环境
A: Oralce DB Server
- OS: CentOS 6.4 32位
- IP: 172.18.16.54
- 安装软体:Oracle 10g(10201_database_win32.zip),ORACLE_HOME:/opt/oracle/102
B: SQL Server DB Server
- OS: Win7 32位
- IP: 172.18.16.65
- 安装软体:SQL Server 2008,数据库名:pubs,用户名:sa,密码:sa
C: Oracle Gateway
- OS: Win7 32位
- IP: 172.18.16.47
- 安装软体:Oracle Gateway(win32_11gR2_gateways.zip),安装路径D:\oracle\11.2.0\tg_1
二、Oracle透明网关安装配置
1.透明网关安装
将透明网关安装在服务器C上,下载解压安装包win32_11gR2_gateways.zip后,点击setup.exe,启动安装界面,安装oracle transparent gateway for microsoft sql server,输入SQL Server数据库时可以留空稍后配置,配置监听时勾选“执行典型配置”。
2.配置init文件
透明网关安装完后会生成一个默认的配置文件:initdg4msql.ora(D:\Oracle\11.2.0\tg_1\dg4msql\admin),这个文件的命名格式为:init<sid>.ora,所以文件initdg4msql.ora的sid为dg4msql,我们可以使用这个文件,也可以新建一个文件,这里选择新建文件initdg4msqlpubs.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=[172.18.16.65]//pubs
HS_FDS_TRACE_LEVEL=ON #此处设为ON,可在D:\Oracle\11.2.0\tg_1\dg4msql\trace目录下生成跟踪文件
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
如果HS_FDS_CONNECT_INFO配置不正确,则会报如下错误:
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Oracle][ODBC SQL Server Driver][DBNETLIB]无效的连接。{08001,NativeErr = 14}[Oracle][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (ParseConnectParams()). {01000,NativeErr = 14}[Oracle][ODBC SQL Server Driver]无效的连接字符串属性{01S00}
3.配置监听
修改监听文件listener.ora(D:\Oracle\11.2.0\tg_1\NETWORK\ADMIN)。
# listener.ora Network Configuration File: D:\Oracle\11.2.0\tg_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msqlpubs) #此处与配置文件initdg4msqlpubs.ora中的sid对应
(ORACLE_HOME = D:\Oracle\11.2.0\tg_1)
(PROGRAM = dg4msql) #此处为配置文件目录名,Oracle 11g为dg4msql,Oracle 10g为tg4msql
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.16.47)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\Oracle\11.2.0\tg_1
4.重启监听
进入目录D:\Oracle\11.2.0\tg_1\BIN,双击LSNRCTL.exe,运行命令stop和start命令重启监听,或者在Windows服务管理界面重启OracleOraGtw11g_home1TNSListener服务。
修改Oralce服务器A上的ORACLE_HOME/network/admin/tnsname.ora文件。
# tnsnames.ora Network Configuration File: /opt/oracle/102/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.16.54)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
#pubs_using需要顶头,前面不能有空格
pubs_using =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.16.47)(PORT = 1521))
(CONNECT_DATA =
(SID = dg4msqlpubs)
)
(HS = OK)
)
四、创建数据库链接
登录Oracle数据库,普通账户即可,运行以下语句:
create database link dblinkpubs --dblink名称
connect to sa --SQL Server数据库用户名
identified by "sa" --SQL Server数据库密码,用引号括起来
using 'pubs_using'; --服务名
数据查询语句:
select "FEmpNO" from TEmployees@dblinkpubs; --区分大小写,字段需用双引号括起来
五、总结
1.运行数据查询语句时报错,连接代理时 Net8 诊断到错误
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from DBLINKPUBS
这个错误碰到了两次,第一次是由于SQL Server数据库远程连接未打开,打开远程连接后就没报错了;第二次是在安装配置完Oracle网关并连接成功后,再安装PLSQL软件并添加TNS_ADMIN系统环境变量后就报上面的错误了,将PLSQL软件卸载并删除TNS_ADMIN系统环境变量后就没报错了。
2.由于SQL Server本身不支持UNIX,Oracle无法直接在UNIX下访问SQL Server,所以Oracle For SQL Server的透明网关只在支持Win。