oracle19c连接MySQL,[Oracle-> MySQL] Oracle通过dblink连接MySQL--Oracle 19c连接到MySQL 5.7

[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位1[oracle@raclhr-18c-n1 ~]$ file $ORACLE_HOME/bin/dg4odbc

2/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

3[oracle@raclhr-18c-n1 ~]$ ll

2)下载并安装ODBC Driver Manager下载地址： http://www.unixodbc.org/download.html

解压并进行编译安装：1tar -zxvf unixODBC-2.3.7.tar.gz

2cd /home/oracle/unixODBC-2.3.7

3./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc

4make && 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

进行安装：1[root@raclhr-18c-n1 oracle]# rpm -ivh mysql-connector-odbc-5.3.13-1.el7.x86_64.rpm

2warning: mysql-connector-odbc-5.3.13-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

3Preparing...                          ################################# [100%]

4Updating / installing...

5   1:mysql-connector-odbc-5.3.13-1.el7################################# [100%]

6Success: Usage count is 1

7Success: Usage count is 1

4)配置ODBC Driver

vi /etc/odbc.ini1[myodbc5]

2Driver = /usr/lib64/libmyodbc5w.so

3Description = Connector/ODBC 5.2 Driver DSN

4SERVER = 192.168.59.159

5PORT = 3306

6USER = root

7PASSWORD = lhr

8DATABASE = LHRDB

9OPTION = 0

10TRACE = OFF

5)验证ODBC连接1export ODBCINI=/etc/odbc.ini

2isql myodbc5 -v

3[root@raclhr-18c-n1 unixODBC-2.3.7]# which isql

4/usr/bin/isql

5[root@raclhr-18c-n1 unixODBC-2.3.7]# isql myodbc5 -v

6+---------------------------------------+

7| Connected!                            |

8|                                       |

9| sql-statement                         |

10| help [tablename]                      |

11| quit                                  |

12|                                       |

13+---------------------------------------+

14SQL> show databases;

15+-----------------------------------------------------------------+

16| Database                                                        |

17+-----------------------------------------------------------------+

18| information_schema                                              |

19| LHRDB                                                           |

20| db_name                                                         |

21| db_name2                                                        |

22| lhrdb                                                           |

23| lhrdb2                                                          |

24| mysql                                                           |

25| performance_schema                                              |

26| sakila                                                          |

27| sys                                                             |

28| test                                                            |

29+-----------------------------------------------------------------+

30SQLRowCount returns 11

3111 rows fetched

mysql准备数据：1[root@LHRDB ~]# mysql -h192.168.59.159 -uroot -plhr

2mysql: [Warning] Using a password on the command line interface can be insecure.

3Welcome to the MySQL monitor.  Commands end with ; or \g.

4Your MySQL connection id is 14

5Server version: 5.7.19 MySQL Community Server (GPL)

6Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

7Oracle is a registered trademark of Oracle Corporation and/or its

8affiliates. Other names may be trademarks of their respective

9owners.

10Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

11mysql> show databases

12    -> ;

13+--------------------+

14| Database           |

15+--------------------+

16| information_schema |

17| LHRDB              |

18| db_name            |

19| db_name2           |

20| lhrdb              |

21| lhrdb2             |

22| mysql              |

23| performance_schema |

24| sakila             |

25| sys                |

26| test               |

27+--------------------+

2811 rows in set (0.00 sec)

29mysql> use LHRDB;

30Database changed

31mysql> show tables;

32Empty set (0.00 sec)

33mysql> create table lhrtest(id int);

34Query OK, 0 rows affected (0.09 sec)

35mysql> insert into lhrtest values(1);

36Query OK, 1 row affected (0.00 sec)

37mysql> select count(1) from lhrtest;

38+----------+

39| count(1) |

40+----------+

41|        1 |

42+----------+

431 row in set (0.00 sec)

6)配置tnsnames.ora1myodbc5 =

2  (DESCRIPTION=

3    (ADDRESS=

4        (PROTOCOL=TCP) (HOST=192.168.59.52) (PORT=1521)

5    )

6    (CONNECT_DATA=

7      (SID=myodbc5)

8    )

9    (HS=OK)

10)

7)配置listener.ora1SID_LIST_LISTENER=

2  (SID_LIST=

3    (SID_DESC=

4      (SID_NAME=myodbc5)

5      (ORACLE_HOME=/u01/app/oracle/product/19.2.0/dbhome_1)

6      (PROGRAM=dg4odbc)

7      (ENVS=LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/19.2.0/dbhome_1/lib)

8    )

9  )

8)创建  init.ora文件

创建文件$ORACLE_HOME/hs/admin/initmyodbc5.ora，内容如下：1HS_FDS_CONNECT_INFO=myodbc5

2HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so

3HS_FDS_SUPPORT_STATISTICS=FALSE

4HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

9)使上述配置文件生效， 验证配置是否正确1lsnrctl reload

2lsnrctl status

3tnsping myodbc5

4

5

6[oracle@raclhr-18c-n1 unixODBC-2.3.7]$ odbcinst -j

7unixODBC 2.3.1

8DRIVERS............: /etc/odbcinst.ini

9SYSTEM DATA SOURCES: /etc/odbc.ini

10FILE DATA SOURCES..: /etc/ODBCDataSources

11USER DATA SOURCES..: /home/oracle/.odbc.ini

12SQLULEN Size.......: 8

13SQLLEN Size........: 8

14SQLSETPOSIROW Size.: 8

10)创建dblink并验证1[oracle@raclhr-18c-n1 admin]$ sas

2SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 11 22:13:20 2019

3Version 19.2.0.0.0

4Copyright (c) 1982, 2018, Oracle.  All rights reserved.

5Connected to:

6Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

7Version 19.2.0.0.0

8SQL> show pdbs

9    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

10---------- ------------------------------ ---------- ----------

11         2 PDB$SEED                       READ ONLY  NO

12         3 LHRPDB2                        READ WRITE NO

13SQL> alter session set container=lhrpdb2;

14Session altered.

15SQL> create   public   database  link mysqltest  connect   to   "root"  identified  by   "lhr"  using  'myodbc5' ;

16Database link created.

17SQL>  select   count(*)  from  "lhrtest"@mysqltest;

18  COUNT(*)

19----------

20         1

21SQL>  select   *  from  "lhrtest"@mysqltest;

22        id

23----------

24         1

25

26SQL> insert into "lhrtest"@mysqltest values(2) ;

271 row created.

28SQL> commit;

29Commit complete.

mysql中查询：1mysql> select  *  from  LHRDB.lhrtest;

2+------+

3| id   |

4+------+

5|    1 |

6|    2 |

7+------+

82 rows in set (0.00 sec)

注意：本文内容太多，公众号有字数限制，全文可点击文末的阅读原文，谢谢大家的理解。Oracle培训和认证记得找小麦苗哟。

更多理论参考： http://blog.itpub.net/26736162/viewspace-2144661/

1  小麦苗现有课程