July 13 2014

oracle连接sqlserver 和db2

首先环境全是windows(我知道在window下搞很垃圾。。。又被朝阳GG给鄙视了。。。。等我明天再把linux下的搞出来,写出来。。。。不要再鄙视我了。。。自尊心都没了)

Sqlserver所在机器 window7  sqlserver 2008   192.168.88.1

Oracle所在机器  server 2008   oracle11g     192.168.88.5

 

透明网关和oracle在一个机器

 

Oracle_home: C:\app\Administrator\product\11.2.0\dbhome_1

透明网关目录:C:\app\Administrator\product\11.2.0\tg_1

 

端口:1521

 

文件如下(C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora如果没有 请手动创建 或者netca 肯定会有的):

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

(PROGRAM = extproc)

(ENVS = “EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll”)

)

(SID_DESC =

(SID_NAME = dg4msql)

(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

(PROGRAM = dg4msql)

)

(SID_DESC =

(SID_NAME = dg4db2)

(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)

(PROGRAM = dg4odbc)

)

)

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.5)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

 

ADR_BASE_LISTENER = C:\app\Administrator

 

 

 

文件(C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

):

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

 

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

 

dg4msql =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.5)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dg4msql)

)

(HS = OK)

)

 

dg4db2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.5)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dg4db2)

)

(HS = OK)

)

文件(C:\app\Administrator\product\11.2.0\tg_1\hs\admin\initdg4odbc.ora):

 

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#file:C:\app\Administrator\product\11.2.0\tg_1\hs\admin\initdg4odbc.ora

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = <odbc data_source_name>

HS_FDS_TRACE_LEVEL = <trace_level>

 

 

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

 

 

文件:(C:\product\11.2.0\tg_1\network\admin\listener.ora)

# listener.ora Network Configuration File: C:\product\11.2.0\tg_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = dg4msql)

(ORACLE_HOME = C:\app\Administrator\product\11.2.0\tg_1)

(PROGRAM = dg4msql)

)

(SID_DESC =

(SID_NAME = dg4db2)

(ORACLE_HOME = C:\app\Administrator\product\11.2.0\tg_1)

(PROGRAM = dg4odbc)

)

)

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.5)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

 

ADR_BASE_LISTENER = C:\app\Administrator\product\11.2.0\tg_1

 

 

 

 

文件:(C:\app\Administrator\product\11.2.0\tg_1\network\admin\tnsnames.ora)

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\tg_1\network\admin\tnsnames.ora

dg4msql =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.5)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dg4msql)

)

(HS = OK)

)

 

dg4db2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.88.5)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dg4db2 )

)

(HS = OK)

)

 

 

Oracle Gateway在此文件中默认将sqlserver 2008的IP,实例名,数据库名以 / 分隔,这种设置后续会报错,改为以 . 分隔则OK

 

 

 

DROPPUBLIC  databaselink SQLSERVERLINK;

CREATEpublicdatabaselink SQLSERVERLINK

connectto “sa” identifiedby “sa”

using’dg4msql’;

SELECT*FROM sfky@sqlserverlink;

 

DROPPUBLIC  databaselink db2;

CREATEpublicdatabaselink db2

connectto “Administrator” identifiedby “ucjmh”

using’dg4db2′;

SELECT*FROM SYSTOOLS.”ID_TABLE”@db2;

 

SELECT*FROM All_Db_Links

 

 

 

 

 

 

 

 

 我太大意了。。。sqlserver所在机器的1433端口没可以被别的机器访问。。。。在这里耽误了一天没想出来原因本来早就可以睡觉了。。。。搞到现在  凌晨1:40了。。。。。

 telnet了才知道 修改的地方是 控制面板\所有控制面板项\管理工具\计算机管理\服务和应用程序\sqlserver配置管理器\sqlserver 网络配置\mssqlserver的协议。把TCP/IP启用就可以了。

好了,很简单的。  如果还有小朋友搞不定呢 可以联系我  windows下装一次50块 linux下装一次100块  正愁没钱考ocm呢。。。等我有钱考ocm了 就不给你们装了哈。。。。

 

 

 

 

 

 

 

遇到的错误如下:

 

 

ORA-28545: 连接代理时 Net8 诊断到错误
Unable to retrieve text of NETWORK/NCR message 65535

解决

检查上面文件  看有没有不一样的  PROGRAM = 的值必须一样

 

另外db2的odbc建好之后 测试一下 是否成功、

Ora-28500 连接Oracle遇到非oracle系统时返回此信息

请确保sqlserver的1433  db2的50000是否能telnet正确

 

 

网上有人说机器名不能为- 这是没道理的。我的两台都有- 没有问题的

然后db2所在机器已经装好db2之后不建议去修改机器名和密码之类的  会有问题的

 



Copyright 2019. All rights reserved.

Posted 2014年7月13日 by ucjmh in category "db2", "oracle", "sqlserver

Leave a Reply

Your email address will not be published. Required fields are marked *