oracle配置透明网关连接SQLServer的流程

1、一、 安装环境1. 当前使用数据库操作系统：Windows Server 2008 x64数据库版本：oracle 10g 10204数据库实例：CRDS2. 目标数据库操作系统：Windows Server 2008 x64数据库版本：SQLServer2008数据库实例：IA3. 透明网关操作系统：Windows Server 2003数据库版本：oracle 9i 92010二、 安装流程1. Transparent Gateway for SQL Server安装从Oracle 9i数据库安装光盘setup.exe安装,选择安装客户端安装类型选择：管理员安装完客户端后，重新运行setu

2、p.exe，安装产品选择Oracle 9i Database 9.2.0.1.0, 安装类型选择"自定义",安装组件选择Oracle Net Services和Oracle Transparent Gateways, 并在此项下选择Oracle Transparent Gateway for Microsoft SQL Server, 安装过程中可以不设置连接到SQL Server的信息.如下图所示：以下的IP地址及数据库以实际环境为准2. 修改透明网关配置文件编辑%ORACLE_HOME%tg4msqladmininit%ORACLE_SID%.ora, 该文件包含了TG

3、 for SQL Server的配置信息, 其中%ORACLE_SID%是给TG的"SID", 默认为tg4msql. 修改文件中的行：其中SERVER后为SQL Server所在的服务器名称或ip地址,Database为连接到的数据库名称。 当然，如果在SQL Server服务器上，有多个sql server实例，则使用服务器名实例名的方式设置上面的SERVER值，如：HS_FDS_CONNECT_INFO="SERVER=192.168.3.60IA;DATABASE=FCDB_BOND"。3. 配置透明网关监听编辑%ORACLE_HOME%netw

4、orkadminlistener.ora, 编辑对应listener的SID_LIST%ORACLE_SID%必须为第二布中设置的SID, 默认值为tg4msql. 修改listener.ora文件后需重启listener使修改生效.如果没有listener.ora文件，请用Net Configuration Assistant或Net Manager新建一个LISTENER.以上操作都是在Transparent Gateway所在机器上。在当前使用数据库服务器上：编辑需要连接到透明网关的Oracle Server的$ORACLE_HOMEnetworkadmintnsnames.ora, 例

5、如:HOST指向Transparent Gateway所在的机器的IP，Service_name为第3步中的SID_NAME。 使用tnsping测试服务名是否连通，命令：tnsping tg4msql。如果不通，请检查防火墙及网络配置。5. 在Oracle数据库中建立连接, 指向TG for SQL Server其中tg4msql是tnsnames中建立的连接字符串。6. 确保CATHS.sql已被运行。以SYS用户连接上去，查看有没有SYS.HS_FDS_CLASS，如果没有，运行$ORACLE_HOME/RDBMS"ADMIN"CATHS.SQL。7. 测试在目标数据

6、库中建立表t，插入几条记录如运行select * from tfcdb。三、 本次配置文件透明网关当前使用数据库当前使用数据库四、 问题解决及注意事项【错误信息】【错误处理】访问dblink时说明:这是基于网关的方式，因此不能在断开网络的情况下本地的oracle连接本地的SQLServer。【错误信息】【错误处理】在建立DBlink时，SQL Server的用户名和密码必须小写，而且要加双引号【错误信息】【错误处理】以SYS运行exec dbms_hs.replace_base_caps(531, 531, 'First/Last function');【错误信息】【错误处理】

7、【错误信息】【错误处理】以上信息表示由于SQlServer使用了"仅 Windows"的身份验证方式，因此用户无法使用SQLServer的登录帐户（例如 sa )进行连接，解决方法如下设置允许SQLServer身份登录 (基本上这个很有用)操作步骤：1.在企业管理器中，展开"SQL Server组"，鼠标右键点击SQL Server服务器的名称2.选择"属性"3.再选择"安全性"选项卡4.在"身份验证"下，选择"SQL Server和Windows"5.确定,并重新启动SQL

8、 Server服务。一定要重新启动sql服务，关闭sql，重新进入，并不是重新启动sql服务。【错误信息】【错误处理】【以下内容为实践过程中从网上拷贝下来的资料】【报错信息处理】Common Errors and Solutions Associated with Heterogeneous Services and GenericConnectivityThe following list contains some of the most common errors associated with setting up Heterogeneous Services and Generic

9、Connectivity.ORA-28509: unable to establish a connection to non-Oracle systemORA-02063: preceding line from HSCause: This indicates a problem with the Oracle configuration files.Action:Make sure the HOST parameter in the tnsnames.ora file is correct Make sure the PORT number is correct Make sure the

10、 SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA ORA-02068: following severe error from HSORA-03114: not connected to ORACLECause: This indicates the required syntax for the TNSNAMES.ORA file is not present. Action: (HS=OK) has to be added to the tnsnames.ora file in the DESCRIPTION se

11、ction. ORA-02068: following severe error from HSORA-28511: lost RPC connection to heterogeneous remote agent using %tns_address% Cause: The listener is unable to spawn the HS agent or the agent cannot find the ODBC lib directory.Action: The PROGRAM line in the listener.ora file is incorrect or not s

12、pecified. Make sure LD_LIBRARY_PATH includes the $ODBC_HOME/lib directory. If not, set LD_LIBRARY_PATH and restart the listener.ORA-28500: connection from ORACLE to a non-Oracle system returned this message:Transparent gateway for ODBCH001 The environment variable is not set.ORA-02063: preceding 2 l

13、ines from HSCause: Incorrect parameter settings in the HS init.ora file.Action: Set HS_FDS_CONNECT_INFO in the HS init.ora file to the data source name located in the odbc.ini file.Example: HS_FDS_CONNECT_INFO = MS_SQLServer Wire Protocol Make sure the HS init.ora file exists in the $ORACLE_HOME/hs/

14、admin directory and has the same name as the SID in the LISTENER.ORA.ORA-28500: connection from ORACLE to a non-Oracle system returned this message:Transparent gateway for ODBCH001 The environment variable is not set.ORA-02063: preceding 2 lines from HSCause: Incorrect parameter settings in the HS i

15、nit.ora file.Action: Set HS_FDS_SHAREABLE_NAME to the full path plus filename to the libodbc.so file.ORA-28500: connection from ORACLE to a non-Oracle system returned this message:Transparent gateway for ODBCDRV_InitTdp:(SQL State: 01000; SQL Code: 0)ORA-02063: preceding 2 lines from HSCause: The HS

16、 agent cannot find the odbc.ini file.Action: The ODBCINI variable is not set in the HS init.ora file and needs to be set.ORA-00942: table or view does not exist Transparent gateway for ODBCDRV_OpenTable:DATADIRECTODBC SQL Server DriverSQL ServerInvalid object name '%table%'. SQL State: S0002

17、; SQL Code: 208)ORA-02063: preceding 2 lines from HSCause: The data source in the odbc.ini file has incorrect database information.Action: Consult the Connect for ODBC Reference Guide on how to set the parameters for your datasource.ORA-28500: connection from ORACLE to a non-Oracle system returned t

18、his message:Transparent gateway for ODBCDRV_InitTdp: DATADIRECTODBC SQL ServerDriverlibssclient15General network error. Check your network documentation.(SQL State: 08001; SQL Code: 11)ORA-02063: preceding 2 lines from HSCause: There is a problem at the network layer communicating with the foreign d

19、ata source. Action: Make sure the destination host or IP address and port number are correct for the data source in the odbc.ini file.ORA-28500: connection from ORACLE to a non-Oracle system returned this message:Transparent gateway for ODBCDRV_InitTdp: DATADIRECTODBC SQL Server DriverSQL Server Login failed(SQL State: 28000; SQL Code: 4002)ORA-02063: preceding 3 lines from HSTESTCause: The Oracle database link created for the foreign datasource has either no credentials or incorrect credentials.Action: Recreate the Oracle database link with the proper username and password. Also