Oracle 19c使用DBlink连接Mysql8.0

说明：

某客户业务需求，需要在Oracle19c数据库上通过网络连接获取MySQL8.0数据库中业务数据。现针对该需求，配置Oracle19c连接至MySQL8.0数据库的dblink。

流程：

1.确认[Oracle]和[DG4ODBC]位数

通过检查确认，[Oracle]和[DG4ODBC]均是64位，这就要求[ODBC Driver Manager]和[ODBC Driver]也是64位组件

--DG4ODBC确认方式:
$ file $ORACLE_HOME/bin/dg4odbc
/oracle/app/product/19.3.0/db_1/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=b3f8329ffac4e745b3d8c96f64bfcd3a69d1ed6b, not stripped

2.下载及安装ODBC Driver Manager

ODBC驱动管理器介质下载地址：www.unixodbc.org

为了方便测试，我这里直接调用操作系统自带的ODBC驱动管理器，ODBC驱动管理器rpm已经安装
# rpm -ql unixODBC.x86_64
/etc/odbcinst.ini
/usr/bin/dltest
/usr/bin/isql
/usr/bin/iusql
/usr/bin/odbc_config
/usr/bin/odbcinst
/usr/lib64/libesoobS.so.2
/usr/lib64/libesoobS.so.2.0.0
/usr/lib64/libmimerS.so.2
/usr/lib64/libmimerS.so.2.0.0
/usr/lib64/libnn.so.2
/usr/lib64/libnn.so.2.0.0
...
...
/usr/share/man/man1/dltest.1.gz
/usr/share/man/man1/isql.1.gz
/usr/share/man/man1/iusql.1.gz
/usr/share/man/man1/odbc_config.1.gz
/usr/share/man/man1/odbcinst.1.gz
/usr/share/man/man5/odbc.ini.5.gz
/usr/share/man/man5/odbcinst.ini.5.gz

3.下载及安装ODBC Driver

下载地址：

https://downloads.mysql.com/archives/c-odbc/

解压介质并安装

\# 上传软件并解压
\# tar zxvf /soft/mysql-connector-odbc-8.0.12-linux-glibc2.12-x86-64bit.tar.gz
\# mkdir /usr/local/mysql-connector-odbc/
\# mv /soft/mysql-connector-odbc-8.0.12-linux-glibc2.12-x86-64bit/* /usr/local/mysql-connector-odbc/

4.mysql8.0建立测试数据

建立测试用户:
create user root@'%' identified by '123456';
grant all  on *.* to 'root'@'%'
--注意8.0无法直接通过授权来创建用户

mysql>create database test
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.04 sec)

mysql> use test;
Database changed

mysql> create table byh(id int);
Query OK, 0 rows affected (0.73 sec)

mysql> insert into byh values(1);
Query OK, 1 row affected (0.47 sec)

mysql> select count(*) from byh;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

5.配置ODBC data source

# vi /etc/odbc.ini
[myodbc8]
Driver = /usr/local/mysql-connector-odbc/lib/libmyodbc8a.so
Description = Connector/ODBC 8 Driver DSN
SERVER = 192.168.98.153       --mysql服务端IP
PORT = 3306                   --mysql服务端端口  
USER = root                   --mysql连接用户
PASSWORD = 123456             --mysql连接密码
DATABASE = test               --连接的数据库
OPTION = 0
TRACE = OFF

6.创建libodbcinst.so.2.0.0、libodbc.so.2.0.0文件软链接

\# cd /usr/lib64/
\# ln -s libodbcinst.so.2.0.0 libodbcinst.so.1
\# ln -s libodbc.so.2.0.0 libodbc.so.1

7.验证ODBC至MySQL Server端的连接

# isql myodbc8 -v
+------------------------+
| Connected!       |
|                 |
| sql-statement      |
| help [tablename]   |
| quit             |
|                 |
+-------------------------+
SQL>

8.Oracle配置listener.ora

编辑监听配置文件，创建LISTENER并对实例myodbc8进行静态注册

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.21)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
     (SID_LIST=
        (SID_DESC=
             (SID_NAME=myodbc8)
             (ORACLE_HOME=/oracle/app/product/19.3.0/db_1)
             (PROGRAM=dg4odbc)
             (ENV="LD_LIBRARY_PATH=/usr/lib64:/oracle/app/product/19.3.0/db_1/lib")
       )
   )

9.启动监听LISTENER并查看监听状态

$ lsnrctl start LISTENER
$ lsnrctl status LISTENER
Service "myodbc8" has 1 instance(s).
  Instance "myodbc8", status UNKNOWN, has 1 handler(s) for this service...

10.配置tnsnames.ora

$ vi tnsnames.ora
myodbc8 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.21)(PORT = 1521))
    (CONNECT_DATA =
      (SID = myodbc8)
    )
      (HS = OK)
  )

11.验证myodbc8连接串配置

$ tnsping myodbc8 
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-OCT-2020 23:30:28
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
/oracle/app/product/19.3.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.21)(PORT = 1521)) (CONNECT_DATA = (SID = myodbc8)) (HS = OK))
OK (10 msec)

12.配置网关参数文件initmyodbc5.ora

$ cd  $ORACLE_HOME/hs/admin
$ vi initmyodbc8.ora 
HS_FDS_CONNECT_INFO=myodbc8
# Data source name in odbc.ini
HS_FDS_TRACE_LEVEL=ON
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
#
# ODBC env variables
set ODBCINI=/etc/odbc.ini

13.创建Oracle DBlink

SQL> create public database link myodbc8 connect to "root" identified by "123456" using 'myodbc8';

14.验证Oracle DBlink

SQL> select count(*) from "byh"@myodbc8;
  COUNT(*)
----------
   1

15.MOS参考文档:

配置Oracle至MySQL DBlink：
Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (文档 ID 1320645.1)
Odbc Connection From Oracle To SQL*Server Fails With Errors Ora-28546 and Ora-2063 When Using Connection via Database Link. (文档 ID 1389492.1)

附录：连接图