oracle9i连接sql server 2008

今天终于把ORACLE9I链接到SQL SERVER 2008了。用SQL SERVER 2008连接ORACLE9I非常简单，但是反过来，却花费了不少功夫。

慢慢来讲，作为一个笔记，方便以后查询。

ORACLE9i链接HETEROGENEOUS DATABASE(原汁原味的ORACLE9I文档描述异构数据库),有两种方法：Transparent Gateway和Generic Connectivity.两种方法大同小异，用的都是ORACLE9I的HETEROGENEOUS SERVICE，大致架构图我画下来了： 

 

 大步骤：

1.安装DATA DICTIONARY TABLE

2.为GATEWAY(AGENT)配置INITSID.ORA和LISTENER.ORA

3.为ORACLE9I SERVER配置TNSNAME.ORA

4.创建数据库链接，测试成功！

 

详细步骤：（以下步骤是参考网上的）

Step1. Create a System DSN

 

Set up a System Data Source Name (DSN) using the Microsoft ODBC Administrator.

1. Select Start, followed by Programs, OWB_ORACLE_HOME, Network

Administration, and then Microsoft ODBC Administrator.

2. Navigate to the System DSN tab and click Add to create a System DSN.

3. Select Microsoft SQL Server as the driver for which you want to set up the data source.

4. Specify the name of the DSN as sqlsource

5. Test the connection

Step2. Create the Heterogeneous Services Initialization File

             

Create the initsqlserver.ora file in the $ORACLE_HOME/hs/admin directory

as follows:

              HS_FDS_CONNECT_INFO = sqlsource

HS_FDS_TRACE_LEVEL = 0

HS_AUTOREGISTER = TRUE

HS_DB_NAME = hsodbc

Step3. Modify the listener.ora file

To set up the listener, modify the entries in the listener.ora file located in the $ORACLE_HOME/network/admin directory as follows:

SID_LIST_LISTENER =

 (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = E:"oracle"product"10.2.0"db_1)

      (PROGRAM = extproc)

    )   

     (SID_DESC =

      (SID_NAME = sqlserver)

      (ORACLE_HOME = E:"oracle"product"10.2.0"db_1)

      (PROGRAM = hsodbc)

    )

 )

LISTENER =

 (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))    

      (ADDRESS = (PROTOCOL = TCP)(HOST = CNDAL426VWIN.intl.intlroot.fid-intl.com)(PORT = 1521))     

    )

 )

1. For the SID_NAME parameter, use the SID that you specified in Step 2 when

creating the initialization parameter file for the Heterogeneous Services.

2. Ensure that the ORACLE_HOME parameter value is the path to your Oracle home

directory.

3. The value associated with the PROGRAM keyword defines the name of the agent

executable.

Remember to restart the listener after making these modifications.

Step4. Create a Database link

 

              1. Login AsiaDB database as DBA

              2. Running script: alter system set global_names = false;

3. Running script:

CREATE DATABASE LINK "ASIAUAT.REGRESS.RDBMS.DEV.US.ORACLE.COM"

 CONNECT TO ASIADB

 IDENTIFIED BY ASIADB

 USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)

 (HOST=LOCALHOST)(PORT=1521)))

 (CONNECT_DATA=(SID=SQLSERVER))

 (HS=OK)

)';

              4. Test

Select * from “action_logs”@ASIAUAT.REGRESS.RDBMS.DEV.US.ORACLE.COM

              Notes: using “” and same case with SQL Server object name.