Oracle Transparent GateWay For MS SQL提供了oracle访问SQL Server的功能,它们三者可以安装在同一台机器上,也可一安装在不同机器上。
由于本人Oracle DB是安装在Linux环境下,而Oracle Transparent Gateway For MS SQL只能安装在WIN32的环境下,同时不想影响目前Server3的配置,所以将Oracle Transparent Gateway安装在装有WinXP的Server2机器上。
一下是具体的安装配置;
[@more@]环境:
SERVER1:
IP: 192.168.0.165
HOSTNAME: CO44
OS: Cent OS 4.4
DB: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
用途:安装Oracle DB Server
SERVER2:
IP: 192.168.0.111
OS: WinXP Professional Edition SP2
DB: 无
用途:安装Oracle Transparent Gateway
SERVER3:
IP: 192.168.0.165
HOSTNAME: ERP01
OS: Windows 2000 Server SP4
DB:MS SQL Server 2005
用途:安装MS SQL Server DB
目的:
为了使Oracle DB能够访问到MS SQL Server DB;
安装和配置方法:
1. 在Server2上安装Oracle Transparent Gateway;
从Oracle 网站上 download安装文件: oracle_10201_gateways_win32;
由于本人Oracle DB是安装在Linux环境下,而Oracle Transparent Gateway For MS SQL只能安装在WIN32的环境下,同时不想影响目前Server3的配置,所以将Oracle Transparent Gateway安装在装有WinXP的Server2机器上。
A. 在Server2上安装:
a.输入ORACLE_HOME
b.安装的时候选择“透明网关( Oracle Transparent gateway for MS SQL Sever)”
c.输入你要连接的MS SQL Sever的机器名和数据库名
d.点击“安装”
d.安装结束
B. 在Server2上的配置:
a. 修改$ORACLE_HOME/tg4msql /admin/inittg4msql.ora文件:
HS_FDS_CONNECT_INFO=ERP01.ERP2000 --你需要连接的MS SQL Sever数据库信息,格式:servername.dbname
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
b. 修改$ORACLE_HOME/network/admin/listener.ora文件:
(红色位新增部分)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:oracleproduct10.2.0tg_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = tg4msql) --备注:该处SID_NAME必须与inittg4msql.ora文件名中红色部分一致
(ORACLE_HOME = c:oracleproduct10.2.0tg_1)
(PROGRAM = tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
c. 修改$ORACLE_HOME/network/admin/tnsname.ora文件:
tg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tg4msql)
)
(HS=OK)
)
o10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.165)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = o10g)
)
)
d. 一定要记住,在XP的Firewall下开放1521的port,否则其他机器无法访问该listener
e. 重新启动Listener
在DOS下运行lsnrctl stop;
Lsnrctl start;
C. 在Server1上配置Tnsname
a.在$ORACLE_HOME/network/admin/tnsname.ora文件中加入
tg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=192.168.0.111)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tg4msql)
)
(HS=OK)
)
D.在client端用sqlplus连接到Server1的Oracle DB
Connect scott/tigger@o10g
Create database link ora2msql identified by “sa” identified by “sa” using ‘tg4msql’;
--注意MS SQL Sever DB的用户名和口令的大小写;
查询MS SQL Sever中的table
Select * from accic@ora2msql;
查询正常;
Update accic@ora2msql set acc_na=’a’
Where acc_no=’1’;
修改正常;
Rollback;
E.说明配置正常,收工!!
注:ORA-28545和ORA-28500 的问题
在配置的时候曾出现过ORA-28545和ORA-28500的错误提示,需检查inittg4msql.ora文件中HS_FDS_CONNECT_INFO=ERP01.erp2000是否配置正确,以及文件中SID部分与listener.ora文件中SID是否一致;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10490/viewspace-906137/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10490/viewspace-906137/