一、 Oracle数据库ODBC网关概述
透明网关技术由异构服务和Oracle数据库ODBC网关两部分组成，二者共同支持从Oracle环境对非Oracle系统的透明访问。异构服务是数据库的集成组件，提供了连接到非Oracle系统的通用技术。Oracle ODBC网关包括SQL映射、数据类型转换等能力，通过动态查询功能访问与ODBC标准兼容的数据源。
Oracle Gateway for ODBC架构（Oracle与异构数据库部署在不同主机上）：
连接过程：
客户端通过Oracle Net连接到Oracle数据库；
异构服务（Heterogeneous Services）组件通过Oracle Net连接到Oracle ODBC 网关；
网关与ODBC driver manager和ODBC driver通信；
当用户会话第一次使用dblink连接到非Oracle系统时会产生一个专用的代理进程，每个用户会话接收自己的代理进程。当用户会话终止代理进程也终止。
二、Oracle-MySQL透明网关配置方法
2.1、环境和版本
本文主要介绍Oracle-MySQL透明网管的配置，涉及的服务器环境及操作系统数据库版本如下：
服务器1- dbserver （ORACLE LINUX 8.7）：Oracle 19.16.0 IP:192.168.133.120
服务器2- server1 （CENTOS 7.9）： MySQL 8.0.32 IP:192.168.133.220
2.2、配置步骤
Oracle-MySQL透明网关的工作方式可简述为：
Oracle数据库 --> DBLINK --> TNS别名--> Listener监听 --> ODBC --> MySQL connector--> MySQL监听 --> MySQL数据库。
Gateway配置主要包括以下步骤（在Oracle服务器上进行）：
1）验证Oracle透明网关已安装
Oracle 19.16.0默认安装了odbc透明网关dg4odbc，验证：
[oracle@dbserver ~]$ dg4odbc
Oracle Corporation --- 2023-03-27 19:20:42.805852000
Heterogeneous Agent Release 19.0.0.0.0 -Production Built with
OracleDatabase Gateway for ODBC
2）安装Driver Manager
Driver Manager负责管理应用程序和驱动程序间的通信，包括：解析DSN (数据源名称,ODBC的数据源名称在ODBC.INI文件中配置)，加载和卸载驱动程序，处理ODBC调用，将其传递给驱动程序。
root用户安装Driver Manager ,本案例使用unixODBC：
下载UNIXODBC
可以下载最新的版本:2.3.11 ,
#cd /root
# cp unixODBC-2.3.11.tar.gz /u01
# cd /u01
# tar xvf unixODBC-2.3.11.tar.gz
# cd /u01/unixODBC-2.3.11
# ./configure --prefix=/usr/local/unixODBC-2.3.11--includedir=/usr/include --libdir=/usr/lib64 --bindir=/usr/bin--sysconfdir=/etc
# make && make install
核查安装情况：
[root@dbserver etc]# odbcinst -j
unixODBC 2.3.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
3）安装mysql odbc驱动
Connector/ODBC(MyODBC驱动程序)实现ODBC API所提供的功能，它负责处理ODBC函数调用，将SQL请求提交给MySQL服务器，并将结果返回给应用程序。
先配置MYSQL的YUM源
再安装 MYSQL Connector/ODBC
[root@dbserver ~]# yum install mysql-connector-odbc.x86_64
Last metadata expiration check: 0:54:51 ago on Mon 27 Mar 2023 12:19:32PM CST.
Dependencies resolved.
=======================================================================================================================================================================================================
Package Architecture Version Repository Size
=======================================================================================================================================================================================================
Installing:
mysql-connector-odbc x86_64 8.0.32-1.el8 mysql-connectors-community 4.3 M
Installing dependencies:
unixODBC x86_64 2.3.7-1.el8 ol8_appstream 458 k
Transaction Summary
=======================================================================================================================================================================================================
Install 2 Packages
Total download size: 4.8 M
Installed size: 24 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): unixODBC-2.3.7-1.el8.x86_64.rpm 108 kB/s | 458 kB 00:04
(2/2): mysql-connector-odbc-8.0.32-1.el8.x86_64.rpm 600kB/s | 4.3 MB 00:07
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 661 kB/s | 4.8 MB 00:07
MySQL Connectors Community 3.0 MB/s | 3.1 kB 00:00
Importing GPG key 0x3A79BD29:
Userid : "MySQL Release Engineering<mysql-build@oss.oracle.com>"
Fingerprint: 859B E8D7 C586 F538430B 19C2 467B 942D 3A79 BD29
From :/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
Is this ok [y/N]: y
Key imported successfully
MySQL Connectors Community 1.9 MB/s | 1.9 kB 00:00
Importing GPG key 0x5072E1F5:
Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
Fingerprint: A4A9 4068 76FC BD3C4567 70C8 8C71 8D3B 5072 E1F5
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : unixODBC-2.3.7-1.el8.x86_64 1/2
Running scriptlet:unixODBC-2.3.7-1.el8.x86_64 1/2
Installing :mysql-connector-odbc-8.0.32-1.el8.x86_64 2/2
Running scriptlet:mysql-connector-odbc-8.0.32-1.el8.x86_64 2/2
Success: Usage count is 1
Success: Usage count is 1
Verifying :mysql-connector-odbc-8.0.32-1.el8.x86_64 1/2
Verifying : unixODBC-2.3.7-1.el8.x86_64 2/2
Installed:
mysql-connector-odbc-8.0.32-1.el8.x86_64 unixODBC-2.3.7-1.el8.x86_64
Complete!
[root@dbserver ~]# rpm -ql mysql-connector-odbc
/usr/bin/myodbc-installer
/usr/lib/.build-id
/usr/lib/.build-id/ad
/usr/lib/.build-id/ad/00a437d2a372da2667666e1a0c49f432daabb9
/usr/lib/.build-id/c2
/usr/lib/.build-id/c2/e0d95c3c049ee5a5a6810db4422cdb2dacda85
/usr/lib/.build-id/e9
/usr/lib/.build-id/e9/92270c8a869859a71c1ffb4abc3c89d2c3d295
/usr/lib64/libmyodbc8a.so
/usr/lib64/libmyodbc8w.so
/usr/share/doc/mysql-connector-odbc
/usr/share/doc/mysql-connector-odbc/ChangeLog
/usr/share/doc/mysql-connector-odbc/INFO_BIN
/usr/share/doc/mysql-connector-odbc/INFO_SRC
/usr/share/doc/mysql-connector-odbc/LICENSE.txt
/usr/share/doc/mysql-connector-odbc/README.txt
[root@dbserver ~]#
[root@dbserver ~]# rpm -qa | grep mysql
mysql80-community-release-el8-4.noarch
mysql-connector-odbc-8.0.32-1.el8.x86_64
4）odbc.ini配置
ODBC.INI是ODBC配置文件，记录了连接到服务器所需的驱动信息和数据库信息。Driver Manager将使用它来确定加载哪个驱动程序（使用数据源名DSN）。驱动程序将根据指定的DSN来读取连接参数。
在/etc/odbc.ini文件中定义DSN（数据源名称，如mysql_link），配置数据源信息（驱动、IP、端口、用户名、口令、库名等）
vi /etc/odbc.ini
[mysql_link]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc8a.so --mysql odbc lib
Server = 192.168.133.220
Port = 3306
User = emp
Password = Emp1_123
Database = employees
CHARSET = utf8mb4
5）测试mysql odbc是否配置成功
用isql测试mysqlodbc是否配置成功，isql是unixODBC带的一个ODBC客户端访问工具，可使用isql +数据源名来访问目标数据库。
[root@dbserver ~]# isql -v mysql_link
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_employees |
+-----------------------------------------------------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| jobs |
| salaries |
| titles |
+-----------------------------------------------------------------+
SQLRowCount returns 7
7 rows fetched
SQL>
6）hs透明网关配置
在"ORACLE_HOME\hs\admin"目录下，默认存在名为"initdg4odbc.ora"的文件，每个使用DG4ODBC的实例，都必须对应一个"init*.ora"文件，文件命名规则：init+<网关sid>+.ora，文件主要参数：
HS_FDS_CONNECT_INFO：ODBC数据源管理中的DSN名称
HS_FDS_TRACE_LEVEL：追踪级别参数，出于性能影响一般不配置或者配置为"OFF"，若遇到网关问题需要跟踪日志，则配置为"Debug"
[oracle@dbserver admin]$ cat initmysql_link.ora
##HS Configuration
HS_FDS_CONNECT_INFO = mysql_link
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so -这个对应unixODBC DRIVER MANAGER 的库，如果对应到MYSQLCONNECT /usr/lib64/libmyodbc8a.so上面去，后面DBLINK提取数据会报错:ORA-02063错误
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_NLS_DATE_FORMAT=YYYY-MM-DD
HS_NLS_NCHAR = UCS2
HS_FDS_FETCH_ROWS=1000
HS_RPC_FETCH_REBLOCKING=OFF
##ODBC Configuration
set ODBCINI=/etc/odbc.ini
7）网关监听配置
在listener.ora中增加如下配置，sid_name要与上面hs网关配置的网关sid一致，
我这里有GI，所以，在$GRID_HOME/network/admin/下面
[grid@dbserver admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS= (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(PROGRAM = dg4odbc)
(SID_NAME= mysql_link)
(ORACLE_HOME=/u01/app/oracle/product/19.16.0/db_1)
)
)
8）tns配置
ADDRESS：填写透明网关的IP地址和端口
SID：指定连接网关的SID
HS：指定连接的是非ORACLE数据库
vi $ORACLE_HOME/network/admin/tnsname.ora (注意：HS=OK,在CONNECT右括号后面)
mysql_link=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521))
(CONNECT_DATA=(SID=mysql_link))
(HS=OK)
)
重启监听并测试
$GRID_HOME/bin/lsnrctl reload
[grid@dbserver admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Productionon 27-MAR-2023 20:08:10
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version19.0.0.0.0 - Production
Start Date 27-MAR-2023 18:53:18
Uptime 0 days 1 hr. 14 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.16.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/dbserver/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver.sztech.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance"+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance"+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
Instance"+ASM", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance"orcl", status READY, has 1 handler(s) for this service...
Service "mysql_link" has 1 instance(s).
Instance"mysql_link", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@dbserver ~]$ tnsping mysql_link
TNS Ping Utility for Linux: Version 19.0.0.0.0 -Production on 27-MAR-2023 20:10:22
Copyright (c) 1997, 2022, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.16.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS =(PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521)) (CONNECT_DATA = (SID = mysql_link))(HS = OK))
OK (0 msec)
成功则说明没问题了. oracle 19c连DG4ODBC这段成功了.
9）dblink创建以及透明网关测试
SQL> create DATABASE LINK ora2mysql connect to"emp" identified by "Emp1_123" using 'mysql_link';
注意：用户名和密码一定的加双引号“”，申明为小写（Oracle默认大写，Mysql默认小写），不然不匹配。
SQL> select * from "jobs"@ora2mysql
job_idjob_title
---------- ------------------------------
100SA_REP
101IT_PROG
102 DBA
SQL>
三、附录：
1. 参考文档：
Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC DatabaseLink (Doc ID 1320645.1)
2.问题排查：hs/log
测试在Oracle端通过透明网关访问MySQL库数据并建表（8千万+记录，2.5G数据量，约4分钟，等待事件多为：“HS message to agent”）：
中文乱码问题：initmysql_link.ora 加入
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR = UCS2
时间格式乱码问题：
select to_char(to_timestamp("hire_date"),'yyyy-mm-ddhh24:mi:ss'),
to_char(to_timestamp("hire_date"),'yyyy-mm-dd hh24:mi:ss')
from employees@ora2mysql;
SQL>select * from dual@ora2mysql;
select * from dual@ora2mysql
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oraclesystem returned this message:
ORA-02063: preceding line from ora2mysql
这个是HS_FDS_SHAREABLE_NAME =/usr/lib64/libmyodbc8a.so （MYSQL CONNECTOR/ODBC）导致。
3.MYSQL建立测试数据
MYSQL> create user 'emp'@'%' identified by 'Emp1_123';
MYSQL>ALTER USER 'emp'@'%' IDENTIFIED WITH mysql_native_password BY 'Emp1_123';
MYSQL>grant all privileges on employees.* to 'emp'@'%';
mysql>show grants for 'emp'@'%';
+----------------------------------------------------+
|Grants for emp@% |
+----------------------------------------------------+
|GRANT USAGE ON *.* TO `emp`@`%` |
|GRANT ALL PRIVILEGES ON `employees`.* TO `emp`@`%` |
+----------------------------------------------------+
2 rowsin set (0.00 sec)
MYSQL>FLUSH PRIVILEGES;
mysql> select user,host,plugin from mysql.user whereuser='emp';
+------+------+-----------------------+
| user| host | plugin |
+------+------+-----------------------+
|emp | % | mysql_native_password |
+------+------+-----------------------+
1 rowin set (0.00 sec)
$ mysql -uemp -P3306 -p -h192.168.133.220
Mysql>create table jobs (job_id numeric,job_title varchar(20));
QueryOK, 0 rows affected (0.01 sec)
mysql>desc jobs
-> ;
+-----------+---------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
|job_id | decimal(10,0) | YES | |NULL | |
|job_title | varchar(20) | YES | |NULL | |
+-----------+---------------+------+-----+---------+-------+
2 rowsin set (0.00 sec)
mysql>insert into jobs values(100,'SA_REP');
QueryOK, 1 row affected (0.01 sec)
mysql>insert into jobs values(101,'IT_PROG'),(102,'DBA');
QueryOK, 2 rows affected (0.00 sec)
Records:2 Duplicates: 0 Warnings: 0
mysql>select * from jobs;
+--------+-----------+
|job_id | job_title |
+--------+-----------+
| 100 | SA_REP |
| 101 | IT_PROG |
| 102 | DBA |
+--------+-----------+
3 rowsin set (0.00 sec)
mysql>select user();
+-------------+
|user() |
+-------------+
|emp@server1 |
+-------------+
1 rowin set (0.00 sec)
mysql>select database();
+------------+
|database() |
+------------+
|employees |
+------------+
1 rowin set (0.00 sec)
作者: 郑全 时间: 2023-3-27 20:48
编译unixODBC时，如果报没有编译器，需要安装GCC
# yum -y install gcc
即可
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |