今天小麦苗给大家分享的是[Oracle-> MySQL] Oracle通过dblink连接MySQL--Oracle 19c pdb连接到MySQL 5.7。
更多理论请参考： http://blog.itpub.net/26736162/viewspace-2144661/
[Oracle-> MySQL] Oracle通过dblink连接MySQL--Oracle 19c连接到MySQL 5.7
Oracle使用DG4ODBC数据网关连接其它非Oracle数据库，其原理图如下：
从上图可知，Oracle连接MySQL需要涉及到如下组件：DG4ODBC, ODBC Driver Manager, ODBC Driver，本文将一一讲解它们的配置。
版本信息：
Oracle: 19.2.0.0.0 OS: CentOS 7.3
MySQL: 5.7.19 OS: CentOS 6.5
1）判断32位还是64位
代码语言：javascript复制[oracle@raclhr-18c-n1 ~]$ file $ORACLE_HOME/bin/dg4odbc
/u01/app/oracle/product/19.2.0/dbhome_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]=4675eeb874fe889b960a29d3d084f9c1f4c471d0, not stripped
[oracle@raclhr-18c-n1 ~]$ ll
2）下载并安装ODBC Driver Manager
下载地址： http://www.unixodbc.org/download.html
解压并进行编译安装：
代码语言：javascript复制tar -zxvf unixODBC-2.3.7.tar.gz
cd /home/oracle/unixODBC-2.3.7
./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc
make && make install
安装成功后，unixODBC所需的头文件都被安装到了/usr/inlucde下，编译好的库文件安装到了/usr/lib下，与unixODBC相关的可执行文件安装到了/usr/bin下，配置文件放到了/etc下。
3）下载并按照ODBC Driver for MySQL
下载地址 ： http://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads
进行安装：
代码语言：javascript复制[root@raclhr-18c-n1 oracle]# rpm -ivh mysql-connector-odbc-5.3.13-1.el7.x86_64.rpm
warning: mysql-connector-odbc-5.3.13-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-connector-odbc-5.3.13-1.el7################################# [100%]
Success: Usage count is 1
Success: Usage count is 1
4）配置ODBC Driver
vi /etc/odbc.ini
代码语言：javascript复制[myodbc5]
Driver = /usr/lib64/libmyodbc5w.so
Description = Connector/ODBC 5.2 Driver DSN
SERVER = 192.168.59.159
PORT = 3306
USER = root
PASSWORD = lhr
DATABASE = LHRDB
OPTION = 0
TRACE = OFF
5）验证ODBC连接
代码语言：javascript复制export ODBCINI=/etc/odbc.ini
isql myodbc5 -v
[root@raclhr-18c-n1 unixODBC-2.3.7]# which isql
/usr/bin/isql
[root@raclhr-18c-n1 unixODBC-2.3.7]# isql myodbc5 -v
---------------------------------------
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
---------------------------------------
SQL> show databases;
-----------------------------------------------------------------
| Database |
-----------------------------------------------------------------
| information_schema |
| LHRDB |
| db_name |
| db_name2 |
| lhrdb |
| lhrdb2 |
| mysql |
| performance_schema |
| sakila |
| sys |
| test |
-----------------------------------------------------------------
SQLRowCount returns 11
11 rows fetchedmysql准备数据：
代码语言：javascript复制[root@LHRDB ~]# mysql -h192.168.59.159 -uroot -plhr
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14
Server version: 5.7.19 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases
-> ;
--------------------
| Database |
--------------------
| information_schema |
| LHRDB |
| db_name |
| db_name2 |
| lhrdb |
| lhrdb2 |
| mysql |
| performance_schema |
| sakila |
| sys |
| test |
--------------------
11 rows in set (0.00 sec)
mysql> use LHRDB;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table lhrtest(id int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into lhrtest values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select count(1) from lhrtest;
----------
| count(1) |
----------
| 1 |
----------
1 row in set (0.00 sec)
6）配置tnsnames.ora
代码语言：javascript复制myodbc5 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP) (HOST=192.168.59.52) (PORT=1521)
)
(CONNECT_DATA=
(SID=myodbc5)
)
(HS=OK)
)
7）配置listener.ora
代码语言：javascript复制SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=myodbc5)
(ORACLE_HOME=/u01/app/oracle/product/19.2.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/19.2.0/dbhome_1/lib)
)
)
8）创建 init.ora文件 创建文件$ORACLE_HOME/hs/admin/initmyodbc5.ora，内容如下：
代码语言：javascript复制HS_FDS_CONNECT_INFO=myodbc5
HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
9）使上述配置文件生效， 验证配置是否正确
代码语言：javascript复制lsnrctl reload
lsnrctl status
tnsping myodbc5
[oracle@raclhr-18c-n1 unixODBC-2.3.7]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
10）创建dblink并验证
代码语言：javascript复制[oracle@raclhr-18c-n1 admin]$ sas
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 11 22:13:20 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 LHRPDB2 READ WRITE NO
SQL> alter session set container=lhrpdb2;
Session altered.
SQL> create public database link mysqltest connect to "root" identified by "lhr" using 'myodbc5' ;
Database link created.
SQL> select count(*) from "lhrtest"@mysqltest;
COUNT(*)
----------
1
SQL> select * from "lhrtest"@mysqltest;
id
----------
1
SQL> insert into "lhrtest"@mysqltest values(2) ;
1 row created.
SQL> commit;
Commit complete.
mysql中查询：
代码语言：javascript复制mysql> select * from LHRDB.lhrtest;
------
| id |
------
| 1 |
| 2 |
------
2 rows in set (0.00 sec)
注意：本文内容太多，公众号有字数限制，全文可点击文末的阅读原文，谢谢大家的理解。Oracle培训和认证记得找小麦苗哟。
更多理论参考： http://blog.itpub.net/26736162/viewspace-2144661/
