oracle mssql交换数据库,Database link 连接oracle MSSql 实现逻辑数据互导

项目的需要，需要将原有mssql里面的数据导入到新的oracle数据库架构下来，因为有数据结构需要通

过逻辑导入，故mssql的导入工具不能满足搞笑数据互导的需要。

网上找了些解决方案，透明网关什么的，配置复杂，此处留下odbc数据源配置的方式。

通过hsodbc连接： 1.

在ODBC中建立SQL Server连接的System DSN，我用名字BIO549。(步骤不详述，请查MSDN)

2.

在Oracle主目录下hs\admin的目录下，拷贝inithsodbc.ora并改名为init.ora。这次，我用的SID是BIO549，所以文件名是BIO549。以我的例子，如下：

*HS_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 = BIO549

HS_FDS_TRACE_LEVEL = OFF

#

# Environment variables required for the non-Oracle system

#

#set

=

****************************** 3.

配置listener.ora，这次加入下面一段：

****************************** (SID_DESC=

(ORACLE_HOME = C:\oracle\ora92)

(SID_NAME=BIO549)

(PROGRAM=hsodbc)

)

****************************** 因此，我的listener.ora全部如下：

****************************** # LISTENER.ORA Network Configuration File:

C:\oracle\ora92\network\admin\listener.ora

# Generated by Oracle configuration tools.

NEWLISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION

=

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.180)(PORT =

1521))

)

)

)

SID_LIST_NEWLISTENER =

(SID_LIST =

(SID_DESC

=

(ORACLE_HOME = C:\oracle\ora92)

(SID_NAME = MSSQL)

(PROGRAM = tg4msql)

)

(SID_DESC=

(ORACLE_HOME = C:\oracle\ora92)

(SID_NAME=BIO549)

(PROGRAM=hsodbc)

)

)

******************************

4. 配置tnsnames.ora，如下：

****************************** BIO549 =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.180)(PORT=1521))

(CONNECT_DATA= (SID=BIO549))

(HS=OK)

)

******************************

5.

重起listener并建立连接。

(三) 注意：

我在测试的时候，起先总是有ORA-28545的错误，经调查含义如下：

ORA-28545 error diagnosed by Net8 when connecting to an agent

Cause: An

attempt to call an external procedure or to issue SQL to a

non-Oracle system on a Heterogeneous Services database link failed

at connection initialization. The error diagnosed by Net8 NCR

software is reported separately.

Action:

Refer to the Net8 NCRO error message. If this isn't clear, check

connection administrative setup in tnsnames.ora and listener.ora

for the service associated with the Heterogeneous Services database

link being used, or with 'extproc_connection_data' for an external

procedure call.

我仔细的查看了所有文件的配置，发现是由于listener的名字导致的错误(SID_LIST_NEWLISTENER)，因此我建议，如果出现这个错误，请仔细检查所有的配置文件。

(四)推荐阅读：

Managing Oracle Heterogeneous Services Using Transparent

Gateways:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76960/hs_admin.htm

ORA-24289 - ORA-29249:

http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96525/e24280.htm

Making a Connection from Oracle to SQL Server(in English):

http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1