重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛»首页 › 国外商业数据库 › Oracle › ORACLE 19c 通过DBLINK 访问MYSQL 8.0

最新公告：

帐号无法登录解决办法，必看!（2023-04-14）

返回列表

查看: 17907|回复: 1

[安装] ORACLE 19c 通过DBLINK 访问MYSQL 8.0

[复制链接]

郑全

郑全 当前离线

积分: 59990

电梯直达

楼主

发表于 2023-3-27 20:45:43 | 只看该作者回帖奖励

|正序浏览 |阅读模式

ORACLE 19c 通过DBLINK 访问MYSQL 8.0

Oracle数据库ODBC网关概述

Oracle-MySQL透明网关配置方法

odb.png (68.25 KB, 下载次数: 627)

下载附件

2023-3-27 20:45 上传

一、 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

www.unixodbc.org

可以下载最新的版本:2.3.11 ,

#cd /root

Wget https://www.unixodbc.org/unixODBC-2.3.11.tar.gz

# 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源

yum install https://dev.mysql.com/get/mysql80-community-release-el8-4.noarch.rpm

再安装 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

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

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

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

-> ;

+-----------+---------------+------+-----+---------+-------+

+-----------+---------------+------+-----+---------+-------+

+-----------+---------------+------+-----+---------+-------+

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)

分享到: QQ好友和群 QQ空间 腾讯微博 腾讯朋友

收藏0 支持0 反对0

使用道具举报

郑全

郑全 当前离线

积分: 59990

沙发

楼主| 发表于 2023-3-27 20:48:37 | 只看该作者

编译unixODBC时，如果报没有编译器，需要安装GCC
# yum -y install gcc

即可

回复支持反对

使用道具举报

返回列表

高级模式

B Color Image Link Quote Code Smilies

您需要登录后才可以回帖登录 | 注册

本版积分规则

浏览过的版块

Linux
MySQL
华为
windows
Postgre SQL

帐号		自动登录	找回密码
密码			注册