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

一、   安装环境
1.   当前使用数据库
操作系统：Windows Server 2008 x64
IP地址：192.168.3.60
数据库版本：oracle 10g 10204
数据库实例：CRDS
2.   目标数据库
操作系统：Windows Server 2008 x64
IP地址：192.168.3.60
数据库版本：SQLServer2008
数据库实例：IA
3.   透明网关
操作系统：Windows Server 2003
IP地址：192.168.3.8
数据库版本：oracle 9i 92010
二、         安装流程
1.   Transparent Gateway for SQL Server安装
从Oracle 9i数据库安装光盘setup.exe安装,选择安装客户端

安装类型选择：管理员
安装完客户端后，重新运行setup.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%\tg4msql\admin\init%ORACLE_SID%.ora, 该文件包含了TG 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.60\\IA;DATABASE=FCDB_BOND"。
3.   配置透明网关监听
编辑%ORACLE_HOME%\network\admin\listener.ora, 编辑对应listener的SID_LIST%ORACLE_SID%必须为第二布中设置的SID, 默认值为tg4msql. 修改listener.ora文件后需重启listener使修改生效.如果没有listener.ora文件，请用Net Configuration Assistant或Net Manager新建一个LISTENER.

以上操作都是在Transparent Gateway所在机器上。
4.   配置tnsnames.ora
在当前使用数据库服务器上：编辑需要连接到透明网关的Oracle Server的$ORACLE_HOME\network\admin\tnsnames.ora, 例如:

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.   测试
在目标数据库中建立表t，插入几条记录如运行select * from t@fcdb。
三、         本次配置文件
透明网关
D:\oracle\ora92\tg4msql\admin\inittg4msql.ora

当前使用数据库
D:\oracle\ora92\network\admin\ listener.ora

当前使用数据库
D:\oracle\ora92\network\admin\tnsnames.ora

四、         问题解决及注意事项
【错误信息】

【错误处理】
访问dblink时说明:这是基于网关的方式，因此不能在断开网络的情况下本地的oracle连接本地的SQLServer。
【错误信息】
【错误处理】
在建立DBlink时，SQL Server的用户名和密码必须小写，而且要加双引号
【错误信息】

【错误处理】
以SYS运行exec dbms_hs.replace_base_caps(531, 531, 'First/Last function');
【错误信息】

【错误处理】
检查listener.ora和tnsnames.ora的配置信息有误
【错误信息】

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

【错误处理】

【以下内容为实践过程中从网上拷贝下来的资料】
【报错信息处理】    
Common Errors and Solutions Associated with Heterogeneous Services and Generic Connectivity
The following list contains some of the most common errors associated with setting up Heterogeneous Services and Generic Connectivity.
ORA-28509: unable to establish a connection to non-Oracle system
ORA-02063: preceding line from HS
Cause: 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 SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA
ORA-02068: following severe error from HS
ORA-03114: not connected to ORACLE
Cause: 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 section.
ORA-02068: following severe error from HS
ORA-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 specified. 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 ODBC][H001] The environment variable is not set.
ORA-02063: preceding 2 lines from HS
Cause: 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/admin directory and has the same name as the SID in the LISTENER.ORA.
Example: If SID=hsodbc in the listener.ora file, then the HS init.ora file would be named $ORACLE_HOME/hs/admin/inithsodbc.ora
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for ODBC][H001] The environment variable is not set.
ORA-02063: preceding 2 lines from HS
Cause: Incorrect parameter settings in the HS init.ora file.
Action: Set HS_FDS_SHAREABLE_NAME to the full path plus filename to the libodbc.so file.
Example: HS_FDS_SHAREABLE_NAME=/opt/odbc/lib/libodbc.so
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Transparent gateway for ODBC]DRV_InitTdp:
(SQL State: 01000; SQL Code: 0)
ORA-02063: preceding 2 lines from HS
Cause: The HS 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.
Example: set ODBCINI=/opt/odbc/odbc.ini
ORA-00942: table or view does not exist [Transparent gateway for ODBC]DRV_OpenTable: [DATADIRECT][ODBC SQL Server Driver][SQL Server]Invalid object name '%table%'.
SQL State: S0002; SQL Code: 208)
ORA-02063: preceding 2 lines from HS
Cause: 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 this message: [Transparent gateway for ODBC]DRV_InitTdp: [DATADIRECT][ODBC SQL Server Driver][libssclient15]General network error. Check your network documentation.
(SQL State: 08001; SQL Code: 11)
ORA-02063: preceding 2 lines from HS
Cause: There is a problem at the network layer communicating with the foreign data 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 ODBC]DRV_InitTdp: [DATADIRECT][ODBC SQL Server Driver][SQL Server] Login failed
(SQL State: 28000; SQL Code: 4002)
ORA-02063: preceding 3 lines from HSTEST
Cause: 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, username and password must be in double quotes.
Example: create database link ODBC connect to "sa" identified by "pencil" using 'hsodbc'.
Summary
Heterogeneous Services and Generic Connectivity provide Oracle customers the ability to access and integrate non-Oracle data sources, providing a wide degree of flexibility in a multi-database environment. Companies who wish to use Generic Connectivity to consolidate and integrate data with Oracle require optimal connectivity to ensure the best performance. DataDirect Connect for ODBC delivers the most scalable and best performing connectivity available for Oracle Heterogeneous Services.