本文介绍了如何确保在tnsnames.ora文件中主机参数的正确性，确认端口号无误，以及在TNSNAMES.ORA和LISTENER.ORA中SID名称的一致性。同时提到了通过ODBC和OLE DB代理来访问ODBC数据提供者和不支持SQL处理的OLE DB数据提供者的方法。
Using DataDirect Connect® for ODBC with Oracle Heterogeneous Services
Introduction
Corporate developers frequently need to integrate and consolidate data residing in diverse database systems. Oracle users make use of Oracle Heterogeneous Services to integrate non-Oracle data into their primary database. Using the Generic Connectivity agent, Oracle users can communicate to another database or data store, such as XML, via ODBC.
DataDirect Connect
for ODBC provides the most reliable and highest performing data access for users of Oracle Heterogeneous Services. Connect for ODBC does not require the installation and configuration of database client libraries. This dramatically improves performance and scalability and simplifies configuration of Oracle Generic Connectivity, while providing access to a larger number of various data sources.
This paper explains how to use DataDirect Connect
for ODBC with Oracle Heterogeneous Services.
DataDirect Connect for ODBC in an Oracle Heterogeneous Services Environment
Generic Connectivity Architecture
Generic Connectivity is implemented by using a Heterogeneous Services ODBC agent. An ODBC agent is included as part of your Oracle system. Be sure to use the agent shipped with your particular Oracle system and installed in the same $ORACLE_HOME.
To access the non-Oracle data store using Generic Connectivity, the agent works with an ODBC driver. The ODBC driver that you use must be on the same platform as the ODBC agent. The non-Oracle data stores can reside on the same machine as the Oracle database or a different machine.
Installation Steps
This example shows the configuration of Generic Connectivity on a SUN Solaris system using DataDirect's ODBC driver to connect to Microsoft SQL Server. Please check your Oracle documentation for specific version and platform support.
1) Install the data dictionary tables and views for Heterogeneous Services.
Using the server manager or sqlplus logged on as sys, run caths.sql. For example using the server manager you can use the following example:
SQL> connect internal SQL> @ /rdbms/admin/caths.sql;
This script is located in $ORACLE_HOME/rdbms/admin
2) Install the DataDirect Connect for ODBC Driver.
Some non-Oracle data stores will require that particular database's client library components to be installed. If the database is DB2, Sybase, SQL Server or Informix you should use the Connect
for ODBC Wire Protocol driver for the particular database you are trying to access. These drivers do not require any additional components to be installed to connect to the database.
3) Configure your odbc data source in the .odbc.ini file. The example below is a data source to connect to MS SQL Server 2000
Note: Annotations to following examples file begin with the
symbol and should not be included in the actual file.
symbol and should not be included in the actual file.
[SQLServer Wire Protocol] Driver=/opt/odbc/lib/ivmsssXX.so
4) Make sure the following entries are in the tnsnames.ora and listener.ora.
TNSNAMES.ORA
hsodbc=
(description=
(address=(protocol=tcp)(host=hostname)(port=1521))
(connect_data=(sid=hsmsql)) Needs to match the sid in listener.ora.
(hs=ok) hs clause goes in the description.
)
LISTENER.ORA
listener =
(description_list =
(description =
(address_list =
(address = (protocol = tcp)(host = unixhost)(port = 1521))
)
)
sid_list_listener=
(sid_list=
(sid_desc=
(sid_name=hsmsql) Match the sid in tnsnames.ora.
(oracle_home=/db/oracle/product/8.1.6) Appropriate $ORACLE_HOME
(program= hsodbc) Agent Executable
)
)
5) Before starting the listener, make sure the ODBC lib directory is specified in the shared library environment variable.
LD_LIBRARY_PATH=/db/oracle/product/8.1.6/bin:/opt/odbc/lib/
After the LD_LIBRARY_PATH has been modified, start the listener.
Run "lsnrctl services" to verify that you now have a service handler for the hsmsql sid.
LSNRCTL> services
Connecting to (address=(protocol=tcp)(host=unixhost)(port=1521))
Services Summary...
hsmsql has 1 service handler(s)
DEDICATED SERVER established:0 refused:0
LOCAL SERVER
The command completed successfully
6) Create the Initialization file. You must create and customize an initialization file for your generic connectivity agent. Oracle supplies a sample initialization file named "inithsodbc.ora" which is stored in the $ORACLE_HOME/hs/admin directory.
To create an initialization file, copy the appropriate sample file and rename the file to initHS_SID.ora. In this example the sid noted in the listener and tnsnames files is "hsmsql" so the new initialization file is called inithsmsql.ora.
7) Make sure the following noted entries are in the inithsmsql.ora now located in $ORACLE_HOME/hs/admin
INITMSQL.ORA
# HS init parameters # HS_FDS_CONNECT_INFO = MS_SQLServer7
8) Create a database link to access target database. Be sure to use the appropriate quotes as noted below.
SQL> create database link hsmsql SQL> connect to "user" identified by "password" <= valid user/pwd on target DB SQL> using 'hsodbc';
9) To test, run a simple query of a known table on the target datastore.
SQL> select * from employee@hsmsql;
| empid | firstname | lastname | department | job |
| ---------- | --------------- | --------------- | ---------- | --- |
| 10000 | Joseph | Johnston | Sales | CDW |
| 10001 | John | Ladd | Sales | WNV |
| 10002 | Ronald | Wall | Relations | NPI |
| 10003 | Julie | Reynolds | Relations | NPO |
| 10004 | Bill | Baird | Telemarket | PHN |
| 10005 | Jason | Linde | Sales | WND |
| 10006 | Edward | Lufner | Telemarket | CDG |
| 10007 | Mike | Seibt | Networking | IDW |
8 rows selected.
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:
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-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-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-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-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
(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.
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.
(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'.
(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.
by HuiYi_Love from ITPUB
oracle和sqlserver互訪!
前几天由於工作的原因查找了oracle中查找sqlserver數据的資料,現測試成功,整理一下貼出!
要求:從Oracle中能取SqlServer的數据
環境:
OracleDb: Linux + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.52(TOPPROD)
MSQLDB: Windows2000 + SqlServer2000 IP:192.168.1.50(ERPSQL),測試用戶:sa/pass 測試數据表:EK.ACPTA
网關: WindowsXp + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.221(S0504027),因為暫在測試階段,所以网關裝在我用的机器,网關可以裝在MSQLDB上
1.通過ODBC通用方式聯接
要求:從Oracle中能取SqlServer的數据
環境:
OracleDb: Linux + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.52(TOPPROD)
MSQLDB: Windows2000 + SqlServer2000 IP:192.168.1.50(ERPSQL),測試用戶:sa/pass 測試數据表:EK.ACPTA
网關: WindowsXp + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.221(S0504027),因為暫在測試階段,所以网關裝在我用的机器,网關可以裝在MSQLDB上
1.通過ODBC通用方式聯接
代码:
// A. 安裝HS部件
// 默認情況下HS部件是安裝的,查詢視圖 SYS.HS_BASE_CAPS 可得出有沒有安裝此部件!
// B. 配置ODBC
// 在"系統DNS"中配置"ODBC FOR SQLSERVER",例如:[ERPSQL]
// C. 配置TNSNAMES.ORA,路徑:ORACLE_HOME/NETWORK/ADMIN,這一步應該在ORACLEDB(192.168.1.52)上配置!
Lnk2sql = # tnsName
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521)) # 网關IP
)
(CONNECT_DATA =
(SID = hs4sql) #SID,要和監听器裡的SID一致!
)
(HS=OK)
// D. 配置listener.ora,路徑:ORACLE_HOME/NETWORK/ADMIN
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = # 這一段為加入的
(SID_NAME = hs4sql)
(ORACLE_HOME = D:oracleora9i)
(PROGRAM = hsodbc) # 要使用的HS服務程序.
)
)
// E. 重啟監听器服務
// F. 編輯ORACLE_HOME/HS/ADMIN內init.ora，這裡是iniths4sql.ora,因為上面的SID=hs4sql
HS_FDS_CONNECT_INFO = ERPSQL # B中設置的ODBC名稱
HS_FDS_TRACE_LEVEL = 0
// G. 創建DB LINK,以及測試
SQL> create database link ora2sql connect to "sa" identified by "pass" using 'Lnk2sql';
Database link created
SQL> select ta001,ta002 from acpta@ora2sql where rownum<5;
TA001 TA002
----- -----------
S710 20020306001
S710 20020315001
S710 20020325001
S710 20020326001
------------
2.通過"透明网關"方式聯接
代码:
// A. 安裝透明网關,在安裝時選擇自定義安裝,安裝TRANSPARENT GATEWAY FOR SQLSERVER 組件,安裝成功後會產生oracle_homeora90/tg4msql目錄!
// B. 配置TNSNAMES.ORA,路徑:ORACLE_HOME/NETWORK/ADMIN,這一步應該在ORACLEDB(192.168.1.52)上配置!
TG4MSQL = # tnsName
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521)) # 网關IP
)
(CONNECT_DATA =
(SID = tg4msql ) #SID,要和監听器裡的SID一致!
)
(HS=OK)
)
// C. 配置listener.ora,路徑:ORACLE_HOME/NETWORK/ADMIN
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tg4msql)
(SID_NAME = tg4msql)
(ORACLE_HOME = D:oracleora9i)
(PROGRAM= tg4msql)
)
)
// D. 重啟監听器服務
// E. 編輯ORACLE_HOME/TG4MSQL/ADMIN內init.ora，這裡是inittg4msql.ora,因為上面的SID=tg4msql
#HS_FDS_CONNECT_INFO="SERVER=ERPSQL;DATABASE=EK",好多人說用這行可以,我用這行的時候出現了不能打開鏈接的錯誤,改下面一行就沒問題了!
HS_FDS_CONNECT_INFO=ERPSQL.EK
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
// F. 創建DB LINK,以及測試
SQL> create database link msql2 connect to "sa" identified by "pass" using 'TG4MSQL';
Database link created
SQL> select ta001,ta002 from acpta@msql2 where rownum<5;
TA001 TA002
----- -----------
S710 20020306001
S710 20020315001
S710 20020325001
S710 20020326001
--------
代码:
-- 不知什么原因,感覺"通用方式"比"透明网關速度快一點"
SQL> set timing on
SQL> select ta001,ta002 from acpta@ora2sql where rownum<10;
TA001 TA002
----- -----------
S710 20020306001
S710 20020315001
S710 20020325001
S710 20020326001
S710 20020328001
S710 20020329001
S710 20020419001
S710 20020422001
S710 20020425001
9 rows selected
Executed in 0.047 seconds
SQL> select ta001,ta002 from acpta@msql2 where rownum<10;
TA001 TA002
----- -----------
S710 20020306001
S710 20020315001
S710 20020325001
S710 20020326001
S710 20020328001
S710 20020329001
S710 20020419001
S710 20020422001
S710 20020425001
9 rows selected
Executed in 52.281 seconds
--------
3.SQLSERVER訪問ORACLE
環境:windowsxp + sqlserver2000 + Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production IP:192.168.1.221
代码:
// A. 添加ODBC,OdbcName=DB,OracleSid=DB
// B. 執行
sp_addlinkedserver 'LIORA', 'Oracle', 'MSDAORA', 'DB'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname='LIORA',@useself='false',@locallogin='sa',@rmtuser='SYSTEM',@rmtpassword='MANAGER'
select top 10 topic,info from LIORA..SYSTEM.HELP
topic info
-------------------------------------------------- --------------------------------------------------------------------------------
@ NULL
@ @ ("at" sign)
@ -------------
@ NULL
@ Runs the SQL*Plus statements in the specified command file. The command
@ file can be called from the local file system or from a web server.
@ NULL
@ @ {uri|file_name[.ext]} [arg...]
@ NULL
@ where uri supports HTTP, FTP and gopher protocols in the form:
(影響 10 個資料列)
 |
本人注释：
1、先在OS中创建ODBC数据源，例如windows—>控制面板—>数据源（ODBC）—>系统DSN（Data Service Name）。
2、在$ORACLE_HOME/hs/admin中，创建init
.ora文件，用具体的字符代替
,例如initmyaccess1.ora或initsqlserver001.ora。
3、在$ORACLE_HOME/network/admin/listener.ora中，增加指向前面ODBC数据源的脚本。
4、在$ORACLE_HOME/network/admin/tnsnames.ora中,增加指向前面SID的脚本。
5、将$ORACLE_HOME/network/admin/sqlnet.ora中的SQLNET.AUTHENTICATION_SERVICES = (NTS)注释掉，或将(NTS)改为(NONE)。表示不要有操作系统的验证用户机制。
6、对于ORA-02085的错误，涉及数据库服务名的的识别问题，方法有三个：
1.修改对方的GLOBAL_NAMES参数为FALSE即：
ALTER SYSTEM SET GLOBAL_NAMES=FALSE;
2.将对方的GLOBAL_NAME设置成与数据库连接相同的名称
3.将数据库连接删掉重新创建成与对方实例名相同的数据库连接。下面是测试结果：
SQL> CREATE PUBLIC DATABASE LINK "MASTER.COM" USING 'ORA9I';
2.将对方的GLOBAL_NAME设置成与数据库连接相同的名称
3.将数据库连接删掉重新创建成与对方实例名相同的数据库连接。下面是测试结果：
SQL> CREATE PUBLIC DATABASE LINK "MASTER.COM" USING 'ORA9I';
以下为原文：
====================================
欢迎大家同我交流：小白 enhydra_boy@tom.com
欢迎转载，请保留本声明，谢谢！
====================================
Oracle 8.1.6 中引入普通连接技术的概念(Generic connectivity)，这一连接解决方案满足了对许多异种数据库存储的数据访问需求,同时并不需要安装ORACLE的透明网关(ORACLE Transparent Gateway)。该特性允许使用业界标准 ODBC 和 OLEDB 建立透明连接。
什么是普通连接(Generic connectivity)
普通连接是ORACLE的一个低端数据集成解决方案，目标是提供ORACLE 8i可以连接到非oracle数据库的能力。它的实现，是通过异种服务代理(Heterogeneous Services)来实现的。
异种服务代理的类型：
-
ODBC agent for accessing ODBC data providers
-
OLE DB agent for accessing OLE DB data providers that support SQL processing--sometimes referred to as OLE DB (SQL)
-
ODBC agent for acceng OLE DB data providers without SQL processing support--sometimes referred to as OLE DB (FS)
普通连接的系统结构图
ORACLE客户端程序访问非ORACLE数据库的过程：客户端程序是通过SQL*NET连接ORACLE服务器，关于异种数据库的访问，交给HS代理实现。
HS代理通过下面的功能组件实现：ODBC管理器->ODBC驱动程序->数据库的网络客户端->目标数据库。
如果异种库和ORACLE数据库位于同一台服务器上，一般来说驱动程序是可以直接和本机的数据库通讯，就可以省去数据库的网络客户端这一层。
数据类型的转换
ORACLE自动实现ODBC和OLEDB数据类型到ORACLE数据类型的转换，具体的对应转换可以参考ORACLE的联机帮助
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76960/datatype.htm。
普通连接的限制
1
含有BLOB列的表必须要有一个主键字段；
2
BLOB/CLOB数据不直接通过pass-through的查询方式；
3
在WHERE子句中包括了函数的Updates或deletes语句不被允许；
4
不支持存储过程的调用；
HS代理(odbc/oledb)不支持分布式事务(distributed transactions)，只支持单点的事务(single-site transactions)。
普通连接代理的配置
下面，我介绍一下，利用普通连接的ODBC配置。利用HS odbc代理连接SQL SERVER 2000。
第一步初始化文件的建立
首先，你必须要创建初始化文件。ORACLE提供了样例初始化文件,名字是init
.ora,
可能是hsodbc，hsoledb，hsolefs，分别代表了三种类型的代理，位于$ORACLE_HOME/HS/ADMIN下。
把对应的文件复制一份出来，并且改名为init
.ora,
是你SQL SERVER ODBC代理起的别名。
复制并创建了一个inithssql2k.ora文件，编辑inithssql2k.ora文件。参数有：
|
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
#HS_FDS_CONNECT_INFO =
#HS_FDS_TRACE_LEVEL =
HS_FDS_CONNECT_INFO = SQL2K
HS_FDS_TRACE_LEVEL = ON
HS_AUTOREGISTER = TRUE
#
# Environment variables required for the non-Oracle system
#
#set
=
|
第二步 创建异种服务代理的数据字典
执行$ORACLE_HOME/rdbms/admin/caths.sql，创建相关的系统表和视图。
第三步 创建异种服务代理的工作环境
添加hssql2k的侦听服务，$ORACLE_HOME/NETWORK/ADMIN/listener.ora
|
SID_DESC=
(SID_NAME=hssql2k)
(ORACLE_HOME=d:/oracle/ora81)
(PROGRAM=hsodbc)
)
|
修改$ORACLE_HOME/NETWORK/ADMIN/tnsnames.ora,添加一个网络服务名
sql2k,将在后面创建数据库联接时用到。
|
sql2k =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=hssql2k))
(HS=OK)
)
|
重新启动侦听,并且察看服务状态
|
LSNRCTL for 32-bit Windows: Version 8.1.7.0.0 - Production on 02-JUL-2003 12:49:
30
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SAM)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias
LISTENER
Version
TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 - Produ
tion
Start Date
02-JUL-2003 11:08:31
Uptime
0 days 1 hr. 41 min. 2 sec
Trace Level
off
Security
OFF
SNMP
OFF
Listener Parameter File
d:/oracle/ora81/network/admin/listener.ora
Listener Log File
d:/oracle/ora81/network/log/listener.log
Services Summary...
ORCL
has 1 service handler(s)
ORCL
has 3 service handler(s)
PLSExtProc
has 1 service handler(s)
hssql2k
has 1 service handler(s)
The command completed successfully
LSNRCTL>
|
Hssql2k的侦听服务已经正常运行了。
第四步 修改数据库启动参数文件
修改数据库启动参数文件，设置global_names=false,如果设置为true(缺省),那么数据库连接名和全局数据库名一样，这将会导致ORA-02085的错误。
第五步 创建访问non-oracle数据库的连接(Database Link)
在sqlplus中,执行create database link命令。Connect to 指定了连接到SQL SERVER的用户名和密码。
|
SQL>create public database link sql2k connect to sa identified by manager using ‘sql2k’;
Database link created.
|
第六步 测试
|
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> connect scott/tiger;
Connected.
SQL> select count(*) from sysobjects@sql2k;
COUNT(*)
----------
135
|
现在，oracle已经可以访问sqlserver数据库了。以上都是，笔者参考了oracle的联机帮助，并且实际测试的经过。笔者同时也发现，采用hsodbc其实也并不是很稳定，有的sql语句会引起hsodb agent的致命错误，同时有很多限制，采用透明网关应该就可以获得很好的效果。
但是， oracle 8i的这个特性，可以不安装额外的Transparent Gateway产品。因此，只要能够满足应用的数据访问要求，采用Generic connectivity也不失为一个快速，经济的方法。
笔者的测试环境：Windows2000 Server+ORACLE 8i(8.1.7)
扫一扫
220
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
