说明：

某客户业务需求，需要在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)