Oracle19c 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驅動管理器介質下載地址：






為了方便測試，我這裡直接呼叫作業系統自帶的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

下載地址：

解壓介質並安裝

\# 上傳軟體並解壓


\# 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)