说明：
某客户业务需求，需要在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驱动管理器介质下载地址： 为了方便测试，我这里直接调用操作系统自带的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.21-linux-glibc2.12-x86-64bit.tar.gz \# mkdir /usr/local/mysql-connector-odbc/ \# mv /soft/mysql-connector-odbc-8.0.21-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)
