SqlServer教程

Oracle访问SQLServer透明网关配置笔记

本文主要是介绍Oracle访问SQLServer透明网关配置笔记,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

 

参考文章:How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install (文档 ID 562509.1)

 

ORA-28500 SQLSTATE 8001 When I Select Via DG4MSQL (文档 ID 868672.1)

 

 

 

一、实施步骤

1、安装gateway

p13390677_112040_Linux-x86-64_5of7.zip

 

ORAGTW_HOME

/u01/app/oracle/gateways

填写正确的sqlserver  IP、端口、数据库名

 

2、配置透明网关(oracle用户执行)

:使用默认的dg4msql 参数文件即可,不需更改实例,否则可能会出现ORA-28545报错

 

[oracle@hs01dba01 admin]$ pwd

/u01/app/oracle/gateways/dg4msql/admin

[oracle@hs01dba01 admin]$

[oracle@hs01dba01 admin]$ cat initdg4msql.ora

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

# that are needed for the Database Gateway for Microsoft SQL Server

 

#

# HS init parameters

#

HS_FDS_CONNECT_INFO=10.11.4.52:1433//anyimage

# alternate connect format is hostname/serverinstance/databasename

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

 

#HS_FDS_RECOVERY_PWD=R3cOVER!

HS_FDS_TRANSACTION_LOG=HS_TRANSACTION_LOG

HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM

HS_FDS_DELAYED_OPEN=FALSE

HS_FDS_WORKAROUNDS=16

 

HS_NLS_NCHAR = UCS2

HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK

[oracle@hs01dba01 admin]$

 

 

3、配置透明网关监听(oracle用户执行)

:透明网关配置文件、透明网关监听配置文件都需在透明网关目录下配置

 

[oracle@hs01dba01 admin]$ pwd

/u01/app/oracle/gateways/network/admin

[oracle@hs01dba01 admin]$ cat listener.ora

SID_LIST_LISTENER_SQL =

   (SID_LIST =

     (SID_DESC =

     (PROGRAM = dg4msql)

     (SID_NAME = dg4msql)

     (ORACLE_HOME = /u01/app/oracle/gateways)

    )

  )

 

    

LISTENER_SQL =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.10.10)(PORT = 1522))

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

    )

  )

 

ADR_BASE_LISTENER = /u01/app/oracle/gateways

[oracle@hs01dba01 admin]$

 

4、配置tnsnames.ora(oracle用户执行)

 

$ cd $ORACLE_HOME/network/admin/

$ cat tnsnames.ora

添加以下内容:

MSSQLSERVER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =172.16.10.10)(PORT = 1522))

    (CONNECT_DATA=(SID=dg4msql))

    (HS=OK)

  )

 

 

 

 

5、测试

:访问sqlserver的dblink中,用户名和密码必须全部小写,并且使用双引号

 

create public database link sqltest connect to "mdc" identified by "mdc" using 'mssqlserver';




select 1 from dual@sqltest;

 

 

附录:错误信息及处理方法

1、ORA-28500

 

错误原因以及处理方法:

Dblink中用户名、密码必须小写并且使用双引号

initgg4msql.ora文件中的HS_FDS_CONNECT_INFO配置信息必须正确,必须使用sqlserver的数据库名,而非实例名。

 

 

 

2、ORA-28545

 

错误原因以及处理方法:使用了错误的透明网关配置文件,透明网关配置文件和SID不一致

 

 

 

3、ORA-28513

 

这篇关于Oracle访问SQLServer透明网关配置笔记的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!