通过数据链接(Database Link)，从Oracle 10g 连接 SQL Server 2000(实例)

 通过数据链接(Database Link)，从Oracle 10g 连接 SQL Server 2000(实例)
从Oracle 10g连接SQL Server2000，需要用透明网关(Transparent Gateway)，通过它，我们可以 sqlplus 操纵其他数据库，
如 ms sqlserver 、 sybase 、 infomix 等，实现数据库的异构服务。
在Oracle 8i中没有透明网关，oracle 10.2的透明网关在另外的安装盘上（gateways），需要另外安装，
10g需要下载10201_gateways_win32.zip插件

(一)安装Transparent Gateway for Windows SQL Server：
注意：安装的之前必须要将原数据库服务器启动起来，不然会重复安装OraTg10g_home1数据库软件，这样就会有两个数据库软件，
      导致listener混乱
1. 下载10201_gateways_win32.zip插件,解压10201_gateways_win32.zip
2. 进入10201_gateways_win32文件夹，点击setup.exe
3. 按Next,选择Oracle Transparent Gateway for Microsoft SQL Server 10.2.0.1.0 ，按Next
4、有一步需要填写SQL 服务器名和SQL的数据库名
5. 接着安装。
6. 安装完毕之后，在Oracle的D:/oracle/product/10.2.0/db_1主目录下，会有tg4msql文件夹。

注：设置GATEWAY机器（Gateway可以和Oracle Database在同一台机器上，也可以分开）
(二)配置：
我的环境设置是将Gateway和Oracle Database在设置在同一台机器
我的环境：
Oracle 服务器：
    OS: Windows Server 2000(SP2)
    IP: 10.10.3.88
    PORT: 1521
SQL 服务器：
    OS: Windows Server 2000
    IP: 10.10.3.88
    Database Server Name: DELLF98P-10QC
    Database Name: stapleslink2

(a)通过tg4msql连接：
1. 在Oracle的D:/oracle/product/10.2.0/db_1/tg4msql/admin目录下，拷贝inittg4msql.ora并改名为init<SID>.ora。
   例如，我采用默认的SID是tg4msql，那么我的文件名是inittg4msql.ora。

2. 配置init<SID>.ora，以我的例子，如下：
******************************
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=DELLF98P-10QC;DATABASE=stapleslink2"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
*******************************

3、配置Oracle的D:/oracle/product/10.2.0/db_1/network/admin目录下的listener.ora。以我的例子，如下：
原来的listener.ora文件内容如下：
# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL88.test.com.cn)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME = ORCL88)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = DELLF98P-10QC.staples.sh.cn)(PORT = 1521))
    )
  )

修改为：
# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL88.test.com.cn)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME = ORCL88)
    )
    (SID_DESC=
      (SID_NAME=tg4msql)
      (ORACLE_HOME=D:/oracle/product/10.2.0/db_1)
      (PROGRAM=tg4msql)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.3.88)(PORT = 1521))
    )
  )

4.配置Oracle的D:/oracle/product/10.2.0/db_1/network/admin目录下的tnsnames.ora，以我的例子，如下：
MSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.3.88)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = tg4msql)
    )
    (HS = OK)
  )
解释如下：
HOST：指Gateway所在的机器。
PORT：在GATEWAY机器中listener.ora文件里指定的监听gateway的端口
SID：必须和GATEWAY机器中listener.ora文件里指定的SID_NAME相同
HS = OK：指定该连接将使用Oracle的异构服务

5、检查ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS,NONE)
names.directory_path = (TNSNAMES, HOSTNAME)

注意下面的一点：
如果文件中包含：
names.directory_path = (TNSNAMES, HOSTNAME)
names.default_domain = world
name.default_zone = world
这些行，那么4中的connect_descriptor必须添加.world后缀(如上例中MSQL必须为MSQL.world)
参考文档中的：
connect_descriptor=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=host_name)
         (PORT=port_number)
      )
      (CONNECT_DATA=
         (SID=gateway_sid))
      (HS=OK))
connect_descriptor is the description of the object to connect to as specified when creating the database link, such as
tg4msql. Check the sqlnet.ora file in the Oracle database server's ORACLE_HOME for the following lines:
names.directory_path = (TNSNAMES, HOSTNAME)
names.default_domain = world
name.default_zone = world
Note: If the Oracle database server is on Microsoft Windows, the file is ORACLE_HOME/network/admin/sqlnet.ora.

6、动态修改global_names参数值：
alter system set global_names = false;
否则会在执行sql时报错：ORA-02085: 数据库链接MSQL与HO.WORLD相连结
原因如下：The GLOBAL_NAMES parameter when set to TRUE implies that database link name should be
similar to the Global database name to which you are trying to connect.

6. 重起listener。
lnsrctl stop
lsnrctl start

7. 用sqlplus建立并测试Database Link。在命令行中，用以下命令：
sqlplus /nolog
SQL> connect sys/test@ORCL88 as sysdba      ---->用sysdba的身份登陆
--建立Database Link
CREATE PUBLIC DATABASE LINK MSQL CONNECT TO  sa IDENTIFIED BY sa USING 'MSQL';
注：connect to后面是用户名，identified by后面是密码，using后面是SID。

select * from ttemp4@MSQL;
查询成功！

注：如果global_names不是false的话在执行select * from ttemp4@MSQL会报下面的错误：
ORA-02085: 数据库链接 MSQL.TEST.COM.CN 连接到 HO.WORLD
可以通过修改alter system set global_names = false; 来避免该错误

7.连接成功

参考文档：
http://blog.csdn.net/Kamus/archive/2004/08/02/58238.aspx
http://download-west.oracle.com/docs/cd/B19306_01/gateways.102/b14270/ch2.htm#sthref38
http://www.oracle.com.cn/viewthread.php?tid=36715

(b)通过hsodbc连接：
1. 在ODBC中建立SQL Server连接的System DSN，我用名字DELLF98P-10QC。(步骤不详述，请查MSDN)
可参考http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1/Making-a-Connection-from-Oracle-to-SQL-Server.htm

2. 在Oracle主目录下D:/oracle/product/10.2.0/db_1/hs/admin的目录下，拷贝inithsodbc.ora并改名为initstapleslink2.ora。这次，我用的SID是BIO549，所以文件名是BIO549。以我的例子，如下：
zHS_FDS_CONNECT_INFO后面是数据源名称。
******************************
# 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 = DELLF98P-10QC
HS_FDS_TRACE_LEVEL = OFF
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
******************************
注意：HS_FDS_CONNECT_INFO后面是数据源名称

3. 配置listener.ora，这次加入下面一段：
******************************
   (SID_DESC=
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME=stapleslink2)
      (PROGRAM=hsodbc)
    )
******************************
修改为：
# listener.ora Network Configuration File: D:/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL88.test.com.cn)
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME = ORCL88)
    )
    (SID_DESC=
      (SID_NAME=tg4msql)
      (ORACLE_HOME=D:/oracle/product/10.2.0/db_1)
      (PROGRAM=tg4msql)
    )
   (SID_DESC=
      (ORACLE_HOME = D:/oracle/product/10.2.0/db_1)
      (SID_NAME=stapleslink2)
      (PROGRAM=hsodbc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.3.88)(PORT = 1521))
    )
  )

4. 配置tnsnames.ora，如下：
******************************
stapleslink2  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.3.88)(PORT=1521))
    (CONNECT_DATA= (SID=stapleslink2))
    (HS=OK)
  )

此处的HOST是指MS SQL数据库所在的IP
******************************
5、重新启动下lsnrctl
6、sqlplus测试:
创建数据库连接:
drop  database link sqlLink ;
sql>create database link sqlLink connect to sa identified by "sa" using 'stapleslink2';
sql>select * from ttemp4@sqlLink;

 如果报link错误，则把SID stapleslink2 做替换

create public database link ELE_OA.REGRESS.RDBMS.DEV.US.ORACLE.COM

  connect to sa

  using '(DESCRIPTION =

(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.16.82)(PORT=1522)) (CONNECT_DATA=(SID=ELE_OA)

) (HS = ok))'; 

 

SQL> select * from ttemp4@sqlLink;
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Generic Connectivity Using ODBC][H006] The init parameter <HS_FDS_CONNECT_INFO> is not set. Please set it in init<orasid>.ora file.
ORA-02063: 紧接着 2 lines (起自 SQLLINK)
解决：init<orasid>.ora中的orasid是MS SQL数据库中的指定的数据库名称

ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Generic Connectivity Using ODBC][Microsoft][ODBC 驱动程序管理器] 未发现数据源名称并且未指定默认驱动程序 (SQL State: IM002; SQL Code: 0)
ORA-02063: 紧接着 2 lines (起自 SQLLINK)
解决：HS_FDS_CONNECT_INFO = DELLF98P-10QC，此时的HS_FDS_CONNECT_INFO的应该赋予配置的ODBC数据源的名称

ORA-02085: 数据库链接 SQLLINK.TEST.COM.CN 连接到 HO.WORLD
解决：执行alter system set global_names = false;语句

解决上面的问题之后查询成功！！

参考网页：
http://www.cnblogs.com/lcruiser/archive/2005/04/01/130324.html
http://hansly.itpub.net/post/32273/447697