Oracle 异构数据库访问

发表于 更新于 分类于 本文字数： 7.1k 阅读时长 ≈ 6 分钟
公司中一个项目要用 Oracle 访问 SQL Server, 于是我先在自己电脑上做一下配置实验.

🦴前言

从 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.zip 和 win32_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 数据库 htgl 和 cggl, 那么就可以复制一份 initdg4msql.ora 文件, 并重命名为 inithtgl.ora 和 initcggl.ora, 分别进行配置即可.

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

    • 这个文件夹中还有两个配置示例文件: listener.ora.sample 和 tnsnames.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 中的 htgl20180809 和 cggl20180808 这两个数据库, 之前提到过, 每一个要连接的异构数据库都对应一个 initSID.ora 文件, 唯一标识就是 SID.

修改 Gateways 的数据库连接配置

建立两个 initSID.ora 文件, 起名为: inithtgl.ora 和 initcggl.ora, 对应的 SID 就是 htgl 和 cggl.

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