借助 Oracle Database Gateway 实现 Amazon RDS Oracle 透明访问异构数据库

背景介绍

很多客户都会把不同的应用程序部署在不同引擎的数据库中。比如 Amazon RDS Oracle（以下简称 Oracle），Amazon RDS MySQL/MariaDB（以下简称 MySQL），Amazon RDS PostgreSQL（以下简称 PostgreSQL），Amazon RDS SQL Server（以下简称 SQL Server），Amazon Aurora（以下简称 Aurora）及 Amazon Redshift（以下简称 Redshift）。一些客户时常会遇到这样的问题：

我的 Oracle 数据库需要引用 Redshift 报表的数据。可以从 Oracle 直接访问 Redshift 吗?
我的应用部署在 Oracle 上，但是随着业务发展需要将部分数据写入现有的其它数据库，如 MySQL，PostgreSQL 或者 SQL Server。在不修改应用程序的情况下，我可以直接从 Oracle 把数据写入到异构数据库吗?

本篇文章将介绍如何配置 Oracle Database Gateway，借助 ODBC 实现从 Oracle 访问（读取或者写入）其他引擎的数据库。本文假设了一个最复杂的场景：即 Oracle 同时访问 Redshift，MySQL，PostgreSQL，SQL Server 四种数据库，且对 Redshift 设置只读访问，其它三个则为读写访问。以便于展示这种情况下如何配置 Oracle Database Gateway 的监听器及 ODBC。

根据[Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations (Doc ID 232482.1)]，使用该产品的 ODBC 组件不需要额外的 Oracle License。

架构设计

前提条件

在与 Oracle 数据库实例同一 VPC 中启动一台 Windows x64 操作系统的 EC2 实例作为 Oracle Database Gateway 服务器，并关闭 Windows 防火墙。
源 Oracle 与各目标数据库在同一 VPC 时需设置安全组保证 Gateway 服务器与各 RDS 实例互通；以本文涉及的数据库引擎默认端口为例，RDS 安全组需要对 EC2 所在子网开放以下端口 1521, 3306, 5432, 5439；而 EC2 的安全组需对 RDS 所在的子网开放 1521 端口。如果涉及跨 VPC 乃至目标数据库在自建环境，则还需正确设置网络。如：路由表添加目标数据库的子网，NACL 入站及出站规则添加相应的子网段及端口。
各参数文件中的 HOST 可直接使用 Gateway 服务器的私有 IP。为避免 EC2 私有 IP 改变对 Listener 的影响，方便后续管理，可以给 Gateway 服务器的静态 IP 添加 Amazon Route 53 A 记录。本文中的示例配置均默认已经将私有 IP 映射为 rds.oragw.cn。

方案部署

ODBC 驱动安装及 DSN 配置

ODBC DSN – MySQL（兼容 Aurora MySQL）

从官网下载 MySQL ODBC 驱动。本文使用 mysql-connector-odbc-8.2.0-winx64.msi

安装 MySQL ODBC 驱动。除以下步骤外，其它均使用默认即可。

配置 MySQL DSN：搜索 “odbcad” 点击 ODBC 数据源（64 位）启动 ODBC 管理器并进行以下设置。

系统 DSN -> 添加 -> MySQL ODBC 8.2 Unicode Driver

Data Source Name：mysql（DSN 名称）
TCP/IP Server：填写 MySQL endpoint 如 aurora-mysql.xxx.rds.cn-north-1.amazonaws.com.cn
Port：3306
User：admin（本文使用 master 用户，也可以填写预创建且分配权限的其它用户）
Database：mydb（默认数据库）

点击 Test 返回 Connection successful（注：必须测试成功才能进行后续步骤）

ODBC DSN – PostgreSQL（兼容 Aurora PostgreSQL）

从官网下载 PostgreSQL 驱动并安装。本文使用 psqlodbc_16_00_0000-x64.zip

配置 PostgreSQL DSN：

系统 DSN -> 添加 -> PostgreSQL Unicode(x64)

Data Source：postgresql（DSN 名称）
Server：填写 PostgreSQL endpoint 如 pg.xxx.rds.cn-north-1.amazonaws.com.cn
Database：postgres（默认数据库）
Port：3306
User：admin（本文使用 master 用户，也可以填写预创建且分配权限的其它用户）

点击 Test 返回 Connection successful（必须测试成功才能进行后续步骤）

ODBC DSN – SQL Server

Windows 操作系统已经包含 SQL Server 驱动。可直接配置 DSN。

系统 DSN -> 添加 -> SQL Server

名称：sqlserver（DSN 名称）
服务器：填写 SQLServer endpoint 如 mssqlserver.xxx.rds.cn-north-1.amazonaws.com.cn
使用用户输入登录 ID 和密码的 SQL Server 验证（s）
更改默认的数据库为：mydb（目标数据库）

点击 Test 返回 Connection successful（必须测试成功才能进行后续步骤）

ODBC DSN – Redshift

从官网下载 Redshift ODBC 驱动并安装 e.g. AmazonRedshiftODBC64-1.5.7.1007.msi

配置 Redshift DSN：

系统 DSN -> 添加 -> Amazon Redshift (x64)

Data Source Name：redshift（DSN 名称）
Server：填写 Redshift endpoint 如 rs.xxx.cn-north-1.redshift.amazonaws.com.cn
Port：5439
Database：dev（默认数据库）
Auth Type：Standard
User Name：awsuser（本文使用 master 用户，也可以填写预创建且分配权限的其它用户）

点击 Test 返回 SUCCESS!（必须测试成功才能进行后续步骤）

以上步骤完成之后 , 四个目标数据库的 DSN 配置如下

Oracle Database Gateway 安装配置

软件安装

下载安装介质[How to Download Oracle Database Gateway Products (Doc ID 1270959.1)]
本文使用 Oracle Database Gateways 21.3.0.0.0 for Microsoft Windows x64 (64-bit) – V1017694-01.zip

安装 Gateway。只安装 Oracle Database Gateway for ODBC 组件即可
本文 ORACLE_BASE 路径为 C:\app\tg\Administrator
ORACLE_HOME/GW_HOME 路径为 C:\app\tg\Administrator\product\21.0.0\tghome_1
–

Gateway 初始化参数配置

Gateway 初始化参数的路径为 $ORACLE_HOME\hs\admin
即 C:\app\tg\Administrator\product\21.0.0\tghome_1\hs\admin

参数文件命名规则: init{sid}.ora
其中 init 及后缀 .ora 为固定值. sid 对应后续 Listener 中的 SID_NAME

本文中 Listener 参数文件中的 SID_NAME，Gateway 参数文件中的 HS_FDS_CONNECT_INFO，初始化参数文件名 sid 以及以上各 ODBC DSN 名称四者保持一致。

用于连接 MySQL 的参数文件：initmysql.ora

HS_FDS_CONNECT_INFO=mysql
HS_FDS_TRACE_LEVEL=OFF
HS_TRANSACTION_MODEL=SINGLE_SITE_AUTOCOMMIT

用于连接 PostgreSQL 的参数文件：initpostgresql.ora

HS_FDS_CONNECT_INFO=postgresql
HS_FDS_TRACE_LEVEL=OFF
HS_TRANSACTION_MODEL=SINGLE_SITE_AUTOCOMMIT

用于连接 SQL Server 的参数文件：initsqlserver.ora

HS_FDS_CONNECT_INFO=sqlserver
HS_FDS_TRACE_LEVEL=OFF
HS_TRANSACTION_MODEL=SINGLE_SITE_AUTOCOMMIT

用于连接 Redshift 的参数文件：initredshift.ora
请注意 HS_TRANSACTION_MODEL 参数这里设置成了 READ_ONLY

HS_FDS_CONNECT_INFO=redshift
HS_FDS_TRACE_LEVEL=OFF
HS_TRANSACTION_MODEL=READ_ONLY

Gateway Listener 配置

Listener 参数文件的路径为 $ORACLE_HOME\network\admin
即 C:\app\tg\Administrator\product\21.0.0\tghome_1\network\admin

参数文件 listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rds.oragw.cn)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC=
         (SID_NAME=mysql)
         (ORACLE_HOME=C:\app\tg\Administrator\product\21.0.0\tghome_1)
         (PROGRAM=dg4odbc)
      )
      (SID_DESC=
         (SID_NAME=postgresql)
         (ORACLE_HOME=C:\app\tg\Administrator\product\21.0.0\tghome_1)
         (PROGRAM=dg4odbc)
      )
      (SID_DESC=
         (SID_NAME=sqlserver)
         (ORACLE_HOME=C:\app\tg\Administrator\product\21.0.0\tghome_1)
         (PROGRAM=dg4odbc)
      )
      (SID_DESC=
         (SID_NAME=redshift)
         (ORACLE_HOME=C:\app\tg\Administrator\product\21.0.0\tghome_1)
         (PROGRAM=dg4odbc)
      )
  )

从命令提示符启动 Listener：lsnrctl start
确保各实例均在被监听状态。如下图所示：

Gateway TNS 配置

TNS 参数文件的路径为 $ORACLE_HOME\network\admin
即 C:\app\tg\Administrator\product\21.0.0\tghome_1\network\admin

参数文件 tnsnames.ora

mysql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=rds.oragw.cn)(PORT=1521))
    (CONNECT_DATA=(SID=mysql))
    (HS=OK)
  ) 

postgresql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=rds.oragw.cn)(PORT=1521))
    (CONNECT_DATA=(SID=postgresql))
    (HS=OK)
  ) 

sqlserver =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=rds.oragw.cn)(PORT=1521))
    (CONNECT_DATA=(SID=sqlserver))
    (HS=OK)
  ) 

redshift  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=rds.oragw.cn)(PORT=1521))
    (CONNECT_DATA=(SID=redshift))
    (HS=OK)
  )

从命令提示符验证 TNS，如: tnsping redshift
返回 OK（xx 毫秒）表示对 redshift 的监听和 TNS 配置正确。请逐一验证其它数据库的 TNS（示例略）。

方案测试

从 Oracle 创建指向 MySQL 的 DBLINK

SQL> CREATE PUBLIC DATABASE LINK dg4mysql CONNECT TO "admin" IDENTIFIED BY "xxx" USING '(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=rds.oragw.cn)(PORT=1521)) (CONNECT_DATA=(SID=mysql))(HS=OK))';

Database link created.

–SELECT 目标数据库任一表验证可读取。请注意表名大小写。以下示例表名为“tbs”。

SQL> select * from "tbs"@dg4mysql;
        id name
---------- ------------------------------
         1 this is MySQL

–向目标数据库的表 INSERT 一条记录验证可写入。

SQL> insert into "tbs"@dg4mysql values (2,'from oracle');

1 row created.

SQL> select * from "tbs"@dg4mysql;
        id name
---------- ------------------------------
         1 this is MySQL
         2 from oracle

从 Oracle 创建指向 PostgreSQL 的 DBLINK

SQL> CREATE PUBLIC DATABASE LINK dg4postgresql CONNECT TO "postgres" IDENTIFIED BY "xxx" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL = tcp)(HOST=rds.oragw.cn)(PORT=1521))(CONNECT_DATA=(SID=postgresql))(HS = OK))';

Database link created.

–SELECT 目标数据库任一表验证可读取。请注意表名大小写。以下示例表名为“tbs”。

SQL> select * from "tbs"@dg4postgresql;
        id name
---------- ------------------------------
         1 this is pg

–向目标数据库的表 INSERT 一条记录验证可写入。

SQL> insert into "tbs"@dg4postgresql values (2,'from oracle');

1 row created.

SQL> select * from "tbs"@dg4postgresql;

        id name
---------- ------------------------------
         1 this is pg
2	from oracle

从 Oracle 创建指向 MS SQLServer 的 DBLINK

SQL> CREATE PUBLIC DATABASE LINK dg4sqlserver CONNECT TO "admin" IDENTIFIED BY "xxx" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL = tcp)(HOST=rds.oragw.cn)(PORT=1521))(CONNECT_DATA=(SID=sqlserver))(HS = OK))';

Database link created.

–SELECT 目标数据库任一表验证可读取。请注意表名大小写。以下示例表名为“tbs”。

SQL> select * from "tbs"@dg4sqlserver;

        id name
---------- ------------------------------
         1 this is pg

–向目标数据库的表 INSERT 一条记录验证可写入。

SQL> insert into "tbs"@dg4sqlserver values(2,'from oracle');

1 row created.

SQL> select * from "tbs"@dg4sqlserver;

        id name
---------- ------------------------------
         1 this is pg
2	from oracle

从 Oracle 创建指向 Redshift 的 DBLINK

SQL> CREATE PUBLIC DATABASE LINK dg4redshift CONNECT TO "awsuser" IDENTIFIED BY "xxx" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL = tcp)(HOST=rds.oragw.cn)(PORT=1521))(CONNECT_DATA=(SID=redshift))(HS = OK))';

Database link created.

–SELECT 目标数据库任一表验证可读。请注意表名大小写。以下示例表名为“tbs”。

SQL> select * from "tbs"@dg4redshift;
        id name
---------- ------------------------------
         1 this is redshift

–由于我们设置 Redshift 的访问为只读，因此以下 INSERT 被拒绝了，此为预期现象。

SQL> insert into "tbs"@dg4redshift values (2,'from oracle');
insert into "tbs"@dg4redshift values (2,'from oracle')
*
ERROR at line 1:
ORA-02070: database DG4REDSHIFT does not support insert values in this context

清除资源

从 Oracle 中删除 DBLINK

SQL> DROP PUBLIC DATABASE LINK dg4mysql;
Database link dropped.
SQL> DROP PUBLIC DATABASE LINK dg4postgresql;
Database link dropped.
SQL> DROP PUBLIC DATABASE LINK dg4sqlserver;
Database link dropped.
SQL> DROP PUBLIC DATABASE LINK dg4redshift;
Database link dropped.

终止 Gateway EC2 实例

小结

本方案可以实现从 Oracle 向目前各主流关系数据库的透明访问，不需要修改应用程序就可以完成 Oracle 与其它异构数据库的分布式事务部署。相比通过 DMS 或者其它工具在数据库之间同步数据的方案，本方案运行效率更高，成本更低，且完全可以避免数据不一致、同步延迟等问题。

需要注意的是，如果结果集返回的数据量较大，该架构对网络依赖会很高。若目标数据库不在亚马逊云上，则需充分评估带宽的影响因素。也可以尝试调整 HS_RPC_FETCH_SIZE 参数优化性能。相关参数及使用 Oracle Database Gateway for ODBC 的注意事项可以参考官方文档。

亚马逊AWS官方博客

借助 Oracle Database Gateway 实现 Amazon RDS Oracle 透明访问异构数据库

背景介绍

架构设计

前提条件

方案部署

ODBC 驱动安装及 DSN 配置

Oracle Database Gateway 安装配置

方案测试

清除资源

小结

本篇作者