Oracle10g通过透明网关访问 SqlServer2008R2.docx

Oracle10g通过透明网关访问 SqlServer-2008 R2一、 准备A机器：操作系统：Windows Server 2003 R2 32位安装数据库：Oracle Database 10g 第2版 (10.2.0.3.0)安装路径：D:oracleproduct10.2.0db_1安装10g数据库透明网关：10201_gateways_win32安装路径：D:appasusoracleproduct10.2.0tg_1B机器：192.168.1.103操作系统：Windows 7旗舰版安装数据库：SQL Server 2008 R2 64位*:网关和SQL数据库在一起，便于管理。也可单独安装一台机器。二、 配置B机器上安装了SQL和透明网关后, 在安装目录下D:appasusoracleproduct10.2.0tg_1tg4msqladmin下新建init文件，这里我沿用inittg4msql，所以sql2008数据库tg4msql必须存在inittg4msql的配置内容如下透明网关主机上的listener.ora的配置内容如下(端口由于1521占用，故采用1522)# listener.ora Network Configuration File: D:appasusoracleproduct10.2.0tg_1networkadminlistener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:appasusoracleproduct10.2.0tg_1) (PROGRAM = extproc) )(SID_DESC = (SID_NAME = tg4msql) (ORACLE_HOME = D:appasusoracleproduct10.2.0tg_1) (PROGRAM = tg4msql) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = asus-PC)(PORT = 1522) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) )重启windows监听服务，确保透明网关服务启动透明网关主机上的tnsnames.ora的配置内容如下(端口由于1521占用，故采用1522)# tnsnames.ora Network Configuration File: D:appasusoracleproduct10.2.0tg_1networkadmintnsnames.ora# Generated by Oracle configuration tools.EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )tg4msql= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1522) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = tg4msql) ) (HS=OK) )下来在A机器上的D:oracleproduct10.2.0db_1networkADMINTNSNAMES.ORA添加tg4msql = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.1.103)(PORT=1522) (CONNECT_DATA =(SID=tg4msql) (HS=OK) )测试连通性，此时A机器上的tg4msql就会被B机器监听侦测解析到。若报无监听错误,请检查B机器的网关监听服务是否配置正确并正常启动。三、 创建DBLINK,并测试在A机器上通过PL/SQL建立database linkcreate public database link TG4MSQL connect to SA identified by “sa” using tg4msql;tg4msql就是在TNSNAMES.ORA中的标识。查询DBlink所见里的从sql2008读取数据的表。四、 错误分析1) ORA-02068：以下严重错误源于 TG4MSQLORA-03135：连接失去联系原因：Oracle数据库端的TNSNAMES.ora写法有误,仔细检查是否缺少（HS=OK）或括号是否多余或者有中文括号。正确写法：tg4msql = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.1.103)(PORT=1522) (CONNECT_DATA =(SID=tg4msql) (HS=OK) )2）SQLSERVER端口确保可以telne