Oracle 异构数据库访问

🦴前言

从 Oracle 连接 SQL Server 数据库, 需要用透明网关 (Transparent Gateway), 通过它, 我们可以访问并操作 SQL Server 数据库, 当前也可以连接其他数据库, 实现数据库的异构服务.
目前是SQL SERVER2005异构连接.

🌴声明

  • 本文写于 2020 年 07 月 29 号, 请注意文章时效性!

  • 如果你要配置的环境不是 Oracle Database 和 Oracle Gateways 安装在一台电脑上 这种, 请出门左转 Google, 本文仅在这种环境下验证通过, 不保证 100% 适用于其他环境. 另外, 经测试, Microsoft SQL Server 无论是否安装和 Database 在同一台电脑上本文均有效!

  • 在 Oracle 8i 中没有透明网关, Oracle 10.2 的透明网关是单独的一个插件 10201_gateways_win32.zip, 需要另外安装. 因此本文只适用于 Oracle 11 版本, 其他版本没有进行单独测试, 但相近的版本应该也是适应的. (不保证)

🐌Oracle Database 使用 Oracle Gateways 访问 Microsoft SQL Server

公司中一个项目要用 Oracle 访问 SQL Server, 于是我先在自己电脑上做一下配置实验, 本次实验中 Oracle Database 和 Oracle Gateways 安装在自己电脑上, SQL Server 数据库安装在另一台电脑上.

环境信息

Oracle Database

  • 版本: Oracle Database 11.2.0.1.0 - Production (32 bit)
  • 服务器名称(IP): 192.168.11.95
  • 数据库名: orcl
  • 账号密码: scott/a
  • 本地监听: 1521
  • 对外监听: 1522

Oracle Gateways

  • 版本: Oracle Gateways 11.2.0.1.0 (32 bit)
  • 服务器名称(IP): 192.168.11.95
  • 数据库名: dg4msql
  • 本地监听: 1521
  • 对外监听: 1522

[] Oracle Database 和 Oracle Gateways 的版本和位数都必须保持一致. (不要问我怎么知道的, 问就是不知道! 🙄)

Microsoft SQL Server

  • 版本: Microsoft SQL Server 2008 R2 (64 bit)
  • 服务器名称(IP): 192.168.10.9
  • 实例名: (null)
  • 数据库名: htgl20180809
  • 账号密码: sa/sa2008

查看 Microsoft SQL Server 数据库的信息

  1. 在 Microsoft SQL Server 的对象资源管理器中找到要查看的数据库, 右键属性

    SQL_Server_1

  2. 在弹出的窗口中点击左下方的 "查看连接属性".

    SQL_Server_2

  3. 在弹出的窗口中就可以看到需要的信息了, 其中 服务器主机名, 实例名数据库名 在安装 Gateways 时会用到.

    SQL_Server_3

下载 Oracle Database 和 Oracle Gateways

目前 Oracle 官网把所有信息都放置到一个菜单中, 导致很多平时不怎么访问这种官网的人根本找不到 Oracle 家的软件在哪里下载...还是说一下如何下载 Oracle 软件吧...

  1. 登录 Oracle 官网, 展开上方的 Products 菜单, 点击其中的 Oracle Database 选项.

    Oracle_Download_1

  2. 点击下载最新版本的 Oracle Database 软件: Download Database 19c.

    Oracle_Download_2

  3. 随后会跳转到一个很长很长的页面, 这个页面包含了几乎所有 Oracle 的数据库产品软件, 不再受到 Oracle 支持的版本这里不会显示, 比如 10g 系列就已经不再受 Oracle 支持了, 所以这里也就找不到 10g 系列的下载地址了. 一直往下滑, 找到需要的 11.2.0.1.0 版本的位置, 由于我需要 32 位的数据库软件, 所以这里点击 Microsoft Windows (32-bit) 右侧的 See All, 进入相应的详细信息页面.

    Oracle_Download_3

  4. 详细信息页面的第一条就是 Oracle Database 的下载地址, Oracle 官方将软件打包成了两个压缩包, 全部下载即可. 第二条便是客户端的下载地址, 如果需要使用 PL/SQL 等软件操作数据库, 就需要下载安装 Oracle Database Client 了.

    Oracle_Download_4

  5. 详细信息页面中, 紧接着客户端的第三条信息就是 Oracle Gateways 的下载地址了, 点击下载即可.

    Oracle_Download_5

  6. [] 全部下载之后先不要着急解压, 先看一看下图, 下图是一个 分卷压缩包, 可以看到这个压缩包除了第一卷以外, 从第二卷开始, 后缀名使用了序号进行命名, 这种压缩包只要解压以 zip 为后缀名的第一卷即可, 以 序号 为后缀名并且文件名称和以 zip 为后缀名的文件 同名 的分卷压缩包都会自动解压.

    Oracle_Download_6

    但是, 从 Oracle 官网下载的压缩包中, Database 的包被分成了两个:

    win32_11gR2_database_1of2.zip

    win32_11gR2_database_2of2.zip

    需要注意的是这俩货可不是分卷压缩包, 必须两个都解压, 不要只解压了第一个就不管了, 不然后面会报错的. (不要问我怎么知道的, 问就是不知道! 🙄) 解压后应该是这个样子:

    win32_11gR2_database_1of2.zipwin32_11gR2_database_2of2.zip 都会解压到 database 文件夹中.

    Oracle_Download_7

安装 Oracle Gateways

  1. 启动 gateways 文件夹中的安装程序.

    Oracle_Gateways_Install_1

  2. 在这个页面可以查看已经安装的产品或者卸载产品, 点击下一步进行安装配置.

    Oracle_Gateways_Install_2

  3. 在选择产品名称和路径时, 下面是默认配置.

    [] 由于 Database 和 Gateways 安装在同一台电脑上, 为了避免两者的监听出现问题, 要将 Gateways 安装到 Database 的路径中, 所以不要使用下面的默认配置.

    Oracle_Gateways_Install_3

    而是从下拉框中选择 Oracle Database 的产品名称和路径: OraDb11g_home1, 如图.

    Oracle_Gateways_Install_4

    选择后的配置是这个样子的:

    Oracle_Gateways_Install_5

  4. 选择安装产品组件, 安装用于访问 Microsoft SQL Server 的组件. 由于我之前已经安装过了, 所以截图中显示的是 "重新安装", 如果你电脑上没还有安装这个组件, 应该会显示 "未安装".

    Oracle_Gateways_Install_6

  5. 输入要连接的 Microsoft SQL Server 的信息, 包括服务器主机名, 实例名, 数据库名, 由于我之前查看 SQL Server 信息的时候发现我要连接的 SQL Server 数据库实例名为空, 所以图中填写实例名的地方空着.

    Oracle_Gateways_Install_7

  6. 最后点击安装.

    Oracle_Gateways_Install_8

配置 Oracle Database 和 Oracle Gateways

  1. 关闭数据库监听程序.

    Windows 服务中带有 TNSListener 的服务就是监听程序, 找到与待配置数据库对应的监听程序, 右键, 停止.

    [] 修改 监听程序服务命名 的配置文件之前, 一定要先关闭监听程序, 否则很容易导致 PL/SQL 连接数据库时报错: ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务, 甚至会导致数据库无法访问, 监听程序也无法启动, 启动监听程序时报错: 某些服务在未由其他服务或程序使用时将自动停止, 这时就只能重启数据库了, (不说了, 说多了都是泪啊~ 😫), 为了避免这些乱七八糟的麻烦, 一开始就先把监听程序停止掉就好了.

    Oracle_Configure_1

  2. 修改 Gateways 的数据库连接配置, 配置文件路径:

    ...\product\11.2.0\dbhome_1\dg4msql\admin\initdg4msql.ora

    这个文件告诉了 Gateways 具体要连接到哪个 SQL Server 数据库, 这个文件会使用在安装 Gateways 的时候填写的信息进行默认设置, 如果只需要连接一个 SQL Server 数据库, 默认设置就可以了, 不需要做任何改动, 当然也可以进行自定义, 下面是这个文件的默认设置.

    1
    2
    3
    4
    HS_FDS_CONNECT_INFO=[192.168.10.9]//htgl20180809
    HS_FDS_TRACE_LEVEL=OFF
    HS_FDS_RECOVERY_ACCOUNT=RECOVER
    HS_FDS_RECOVERY_PWD=RECOVER

    这个文件重要配置的内容很少, 就只有异构数据库的连接信息.

    • initdg4msql.ora 文件的命名规则是: initSID.ora, Gateways 安装时默认的 SID 为 dg4msql, 所以文件的名字就是 initdg4msql.ora, 当需要连接多个 SQL Server 数据库的时候, 这里的 SID 自然就需要自定义了. 每一个要连接的 SQL Server 数据库都对应一个 initSID.ora 文件, SID 写一个通俗易懂的, 唯一的名称即可, 这个 SID 得记好, 后面还需要用到. 比如要连接两个 SQL Server 数据库 htglcggl, 那么就可以复制一份 initdg4msql.ora 文件, 并重命名为 inithtgl.orainitcggl.ora, 分别进行配置即可.

    • HS_FDS_CONNECT_INFO 的填写规则: [SQL Server 服务器主机名]/SQL Server 实例名/SQL Server 数据库名. 这个就是具体的 SQL Server 连接信息了.

    • 这个文件夹中还有两个配置示例文件: listener.ora.sampletnsnames.ora.sample, 这是用于 Database 配置时的示例文件, 用意是教会我们怎么修改 监听程序服务命名 的配置, 不需要修改, 即使你修改了也不会生效, 因为文件的后缀是 .sample.

  3. 修改 Oracle Database 的 监听程序 配置, 配置文件路径:

    ...\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

    在监听程序配置文件的 SID_LIST_LISTENER 中添加之前配置好的 Gateways SID, 下图中高亮的这段配置文本就是之前配置的 SID 为 dg4msql 的 Gateways, 其实这一段文本是从之前提到的模板文件 listener.ora.sample 中复制过来的.

    Oracle_Configure_2

    • SID_NAME 填写之前配置的 initSID.ora 文件使用的 SID 名.
    • PROGRAM 是 Gateways 所在的文件夹的名字, 可以看到目前 Gateways 就是安装到了 dg4msql 文件夹中, 所以不要修改这个值.
    • ORACLE_HOME 数据库软件的安装位置.
  4. 修改 Oracle Database 的 服务命名 配置, 配置文件路径:

    ...\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

    在服务命名配置文件中添加之前创建的 dg4msql 服务. 高亮的这段配置文本就是从之前提到的模板文件 tnsnames.ora.sample 中复制过来的.

    Oracle_Configure_3

  5. 重启 Oracle 监听程序.

    此时再将之前关闭的监听程序启动.

使用下面的格式写一条语句创建 DBLink, 也可以使用 PL/SQL 进行可视化创建.

1
2
3
4
5
6
7
8
/*
create database link 变量名(随意)
connect to 用户名 identified by 用户密码
using '透明网关标识';
*/
create database link htgl
connect to sa identified by sa2008
using 'dg4msql';

连接测试

1
2
3
4
5
6
7
8
--select "字段名" from "SQL Server 中的表名"@(DBLink变量名)
select "rybh" from "ryb"@htgl;
--当需要多表连接查询时, 可以这样写
select "zt" as id,
(select "xm" from "ryb"@htgl where "rybh" = "htqxb"."rybh"@htgl) as name
from "htqxb"@htgl
where "rybh" = '150007';
--另外异构数据库不支持 join 类查询.

[] 使用 Oracle 访问 SQL Server 时, 你会发现总是会报这样一个错误: ***字段名无效!. 这个错误是因为 Oracle 访问 SQL Server 时, 字段名要使用 双引号 括起来. (不要问我怎么知道的, 问就是不知道! 🙄) 表名最好也使用双引号括起来.

🦄多异构数据库的配置

公司的项目现在又要求 Oracle 数据库必须能够访问 SQL Server 中的 htgl20180809cggl20180808 这两个数据库, 之前提到过, 每一个要连接的异构数据库都对应一个 initSID.ora 文件, 唯一标识就是 SID.

修改 Gateways 的数据库连接配置

建立两个 initSID.ora 文件, 起名为: inithtgl.orainitcggl.ora, 对应的 SID 就是 htglcggl.

Gateways_Multiple_1

这个是文件 inithtgl.ora 的内容, 连接 192.168.10.9 上的 htgl20180809 数据库.

1
2
3
4
HS_FDS_CONNECT_INFO=[192.168.10.9]//htgl20180809
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

这个是文件 initcggl.ora 的内容, 连接 192.168.10.9 上的 cggl20180808 数据库.

1
2
3
4
HS_FDS_CONNECT_INFO=[192.168.10.9]//cggl20180808
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

修改 Oracle Database 监听程序配置

监听程序的配置, 其实就是把之前配置好的 Gateways 的 SID 放到监听程序的 LIST 中, 反映到文件中就是在 SID_LIST_LISTENER 中添加 SID 设置.

Gateways_Multiple_2

  • PROGRAM 是 Gateways 的安装路径, Gateways 就是安装在 dg4msql 文件夹下的, 所以这个值不能变.

  • SID_NAME 是 Gateways 的 SID 值.

修改 Oracle Database 服务命名配置

服务命名的配置顾名思义, 就是将配置好的 Gateways 服务在服务配置文件中列出来, 告诉 Oracle 数据库我们配置了 Gateways 服务, 不然 Oracle 数据库是不知道的.

Gateways_Multiple_3

  • = 号前面的是服务的名称, 一般保持和 SID 的名称一致, dg4msql/admin 文件夹中的 tnsnames.sample 文件也是这样教我们的.

  • SID 改成之前配置 Gateways 时使用的 SID.

连接测试

配置都做好之后, 建立 DBLink, 就可以访问 SQL Server 了.

1
2
3
4
5
6
7
8
9
10
create database link htgl connect to sa identified by sa2008
using 'htgl';
create database link cggl connect to sa identified by sa2008
using 'cggl';

--drop database link htgl;
--drop database link cggl;

select "khyh", "htbh", "htmc", "htje" from "cghtb"@htgl where "wpbh" like '2018%';
select "wpbh", "wpmc", "shl", "dw" from "cggl_sqmxb"@cggl;

这是 htgl20180809 数据库的查询结果:

Gateways_Multiple_4

这是 cggl20180808 数据库的查询结果:

Gateways_Multiple_5

Oracle 连接 Oracle

这个就比较简单了, 既然不需要使用虚拟网关, 那么直接建立 database link 即可.

[] 两台电脑必须在用一个局域网中!

语法

1
2
3
create database link DBLinkName
connect to DatabaseUser identified by DatabasePassword
using 'DatabaseSID';
字段 含义
DBLinkName 创建的 DBLink 的名字
DatabaseUser 待连接数据库的登录用户名
DatabasePassword 待连接数据库的登录密码
DatabaseSID 待链接数据库的服务名, 同时要写明 IP 地址. 如果是本地数据库, 可以不加 IP 地址

举例

1
2
3
4
5
6
7
8
9
--连接远程计算机数据库
create database link DB_DZYH
connect to dzyh identified by dzyh123
using '192.168.10.136/dzyhcs';

--连接本地计算机数据库
create database link DB_DZYH
connect to dzyh identified by dzyh123
using 'dzyhcs';

🐬参考链接

  • Oracle连接Sql Server (win32_11gR2_gateways)

  • Oracle11g+ 配置多个gateway 连接sqlserver