oracle 透明网关 MSSQL（oracle database gateway）

jnrjian

已于 2022-07-04 10:07:13 修改

阅读量1k

点赞数

文章标签：数据库 oracle

于 2022-07-01 17:27:30 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/125562345

版权

-----------透明网关可以tnsping 不能像oracle一样sqlplus

[oracle@test-mesdb-193-45 admin]$ tnsping "(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.193.45)(PORT=1522))(CONNECT_DATA=(SID=dg4msql))(HS = ok))"

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-JUL-2022 17:25:57

Attempting to contact (DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.193.45)(PORT=1522))(CONNECT_DATA=(SID=dg4msql))(HS = ok))
OK (0 msec)

[oracle@test-mesdb-193-45 admin]$ sqlplus mesau/mesconnection@"(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.193.45)(PORT=1522))(CONNECT_DATA=(SID=dg4msql))(HS = ok))"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 1 17:26:00 2022
Version 19.15.0.0.0

ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error

Enter user-name:

--------------------正文

一、实施步骤

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

数据库动态监听1521 listener，配一个listener1的就好了

[oracle@test-mesdb admin]$ more /u01/app/oracle/product/19.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test-mesdb-193-45)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER1=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=/u01/app/oracle/product/19.2.0/gateway)
(PROGRAM=dg4msql)
)
(SID_DESC=
(SID_NAME=dg4msql31)
(ORACLE_HOME=/u01/app/oracle/product/19.2.0/gateway)
(PROGRAM=dg4msql)
)

)

[oracle@test-mesdb-1 admin]$

用的是数据库的监听所以gateway下network为空

[oracle@test-mesdb-193-45 admin]$ pwd
/u01/app/oracle/product/19.2.0/gateway/network/admin
[oracle@test-mesdb-193-45 admin]$ ls
samples shrept.lst
[oracle@test-mesdb-193-45 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-JUL-2022 17:13:31

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 10-JUN-2022 10:03:24
Uptime 21 days 7 hr. 10 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/test-mesdb-193-45/listener/alert/log.xml

dg4msql/admin 只需要配置init--------

[oracle@test-mesdb-19 admin]$ ls -ltr
总用量 32
-rw-rw-r-- 1 oracle oinstall 746 6月 8 2007 dg4msql_tx.sql
-rw-rw-r-- 1 oracle oinstall 11120 12月 17 2013 dg4msql_cvw.sql
-rw-rw-r-- 1 oracle oinstall 410 7月 1 15:54 listener.ora.sample
-rw-rw-r-- 1 oracle oinstall 244 7月 1 15:54 tnsnames.ora.sample
-rw-rw-r-- 1 oracle oinstall 428 7月 1 16:52 initdg4msql.ora.bak
-rw-r--r-- 1 oracle oinstall 429 7月 1 17:06 initdg4msql.ora
[oracle@test-mesdb-19 admin]$ pwd
/u01/app/oracle/product/19.2.0/gateway/dg4msql/admin
[oracle@test-mesdb-19 admin]$

DBLINK 建法：

create public database link xxST123.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to M identified by me
using '(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=10.xx5)(PORT=1522))(CONNECT_DATA=(SID=dg4msql))(HS = ok))';

1.错误

ORA-28510: 多机种数据库链接初始化错误
ORA-02063: 紧接着 line (起自 ERPTEST123.REGRESS.RDBMS.DEV.US.ORACLE.COM)

-----

# 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.18.5.2]:1433/AIS200XX00 一个/后面跟的是数据库名字，两个//后面是实例修改为HS_FDS_CONNECT_INFO=[10.18.5.2]:1433//AIS2008XX00
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
HS_NLS_NCHAR = UCS2