Oracle Transparent Geteways To MSSQL
oracle通过透明网关(Oracle Transparent Geteways),访问ms sql server和其他数据库
环境:
1、在安装有oracle服务器端,ms sql server的客户端,通过oracle的透明网关访问ms sql server数据库
2、在ms sql server服务器上创建用户conn_ora,并且要能访问ms sql server数据库msdb
安装Oracle Transparent Geteways:
1、'开始'菜单的'Oracle Installation Products'下的'Universal Installer'
\\127.0.0.1\e$\oracle9.2\ORANT920\ORA92_1\NT\I386\stage\products.jar
下一步-
2、选择安装类型:
自定义,下一步
3、选择可用产品组件:
将 'Oracle Transparent Geteways 9.2.0.1.0'下的'Oracle Transparent Geteways for Microsoft SQL Server 9.2.0.1.0'选中;
将 'Oracle Transparent Geteways 9.2.0.1.0'下的'Oracle Transparent Geteways for Sybase 9.2.0.1.0'选中;
下一步;
4、设置Sybase路径
选择你的'SYBASE的完整安装路径';
下一步
5、设置数据库服务器名 和数据库名称???
Microsoft SQL Server:192.168.0.111
Microsoft SQL 数据库:msdb
服务器名称或者ip地址,数据库名字
6、继续安装:
\\127.0.0.1\e$\oracle9.2\ORANT920\ORA92_2\NT\I386\stage
\\127.0.0.1\e$\oracle9.2\ORANT920\ORA92_3\NT\I386\stage
7、安装完成:
此时,oracle安装主目录下有了'tg4msql'目录
C:\oracle\ora92\tg4msql
设置环境参数,创建dblink:
1、C:\oracle\ora92\tg4msql\admin下的inittg4msql.ora文件
文件内容:
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=local;DATABASE=MSDB"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
说明:
HS_FDS_CONNECT_INFO=ZZL.MSDB和
HS_FDS_CONNECT_INFO="SERVER=ZZL;DATABASE=MSDB"和
HS_FDS_CONNECT_INFO="SERVER=192.168.0.111;DATABASE=MSDB"
都可以,两种写法,其中‘SERVER’是服务器名称或者ip,DATABASE是数据库名称,
当然,如果在192.168.0.111这台服务器上,有两个sql server实例,则使用‘服务器名\实例名’的方式设置上面的SERVER值,
如:HS_FDS_CONNECT_INFO="SERVER=local\MSSQLSERVER;DATABASE=MSDB"
2、设置listener.ora文件
位置:C:\oracle\ora92\network\admin
文件内容:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orademo)
(PROGRAM = orademo)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = orademo)
)
(SID_DESC =
(PROGRAM = tg4msql)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = tg4msql)
)
)
其中新增:
(SID_DESC =
(PROGRAM = tg4msql)
(ORACLE_HOME = C:\oracle\ora92) #oracle的主目录
(SID_NAME = tg4msql)
)
3、配置tnsnames.ora文件
位置:C:\oracle\ora92\network\admin
文件内容:
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
SDLGDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.8)(PORT = 1521))
)
(CONNECT_DATA =
(SID = SSTORA)
(SERVER = DEDICATED)
)
)
ORADEMO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = zzl)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orademo)
(SERVER = DEDICATED)
)
)
zzl_sql200 = #数据库连接名称,任意命名
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1521)) #'192.168.0.111'为 oracle服务器的机器名
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS=OK) #Oracle Server要调用异构服务来处理
)
其中新增:
zzl_sql200 = #数据库连接名称,任意命名
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1521)) #'192.168.0.111'为 oracle服务器的机器名
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS=OK) #Oracle Server要调用异构服务来处理
)
4、配置init.ora.1115200612231
位置:C:\oracle\admin\orademo\pfile
将global_names设置为真值,及global_names=true
5、重新启动服务:
在window的开始的运行中执行:
net stop OracleOraHome92TNSListener
net stop OracleServiceORADEMO
net start OracleOraHome92TNSListener
net start OracleServiceORADEMO
说明:OracleOraHome92TNSListener 是监听服务,OracleServiceORADEMO是实例服务
6、创建dblink:
以普通用户登录oracle:如cs/cs@orademo
--drop DATABASE LINK link_anson;
CREATE DATABASE LINK link_anson CONNECT TO "sa" IDENTIFIED BY "anson" USING 'local_sql2000';
说明:ms sql server的用户名和密码必须小写,而且要加双引号,
否则会报如下错误:
错误信息:
ORA-28500: connection from ORACLE to a non-Oracle system returned this messsage:
[Transparent gateway from MSSQL][Microsoft][ODBC SQL Server Driver][SQL Server]??
'sa'????? (SQL State:28000; SQL Code: 18456)
ORA-02063: preceding 2 lines from LINK_anson
错误原因:
在CREATE DATABASE LINK LINK_anson的连接字符串中,ms sql server的用户名和密码必须小写,而且要加双引号
7、在pl/sql中执行:
select * from sysobjects@link_anson where rownum <=5;
结果如下:
1 sysrowsetcolumns 4 S 4 0 0 0 0 0 2005-10-14 1:36:15
2 sysrowsets 5 S 4 0 0 0 0 0 2005-10-14 1:36:15
3 sysallocunits 7 S 4 0 0 0 0 0 2005-10-14 1:36:15
4 sysfiles1 8 S 4 0 0 0 0 0 2003-4-8 9:13:38
5 syshobtcolumns 13 S 4 0 0 0 0 0 2005-10-14 1:36:15
访问dblink时说明:
这是基于网关的方式,因此不能在断开网络的情况下本地的oracle连接本地的ms sql server
否则会报如下错误:
ORA-28545: error diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from LINK_anson
如果要实现数据同步,请oracle使用快照,见:http://www.cnblogs.com/anson2020/archive/2007/08/31/876926.html