配置Oracle DBlink連線MySQL庫
背景描述
某客戶業務需求，需要在 Oracle 資料庫上透過網路連線獲取 MySQL 資料庫中業務資料。現針對該需求，配置 Oracle 連線至 MySQL 庫的 dblink 。
配置 Oracle DBlink
確認 [Oracle] 和 [DG4ODBC] 位數
|
SQL> select * from v$version where rownum<=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production |
|
$ file $ORACLE_HOME/bin/dg4odbc /oracle/app/product/11.2.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.18, not stripped |
透過檢查確認， [Oracle] 和 [DG4ODBC] 均是 64 位，這就要求 [ODBC Driver Manager] 和 [ODBC Driver] 也是 64 位元件
下載及安裝 ODBC Driver Manager
ODBC 驅動管理器介質下載地址：
為了方便測試，我這裡直接呼叫作業系統自帶的 ODBC 驅動管理器，不難看出 ODBC 驅動管理器 rpm 已經安裝
|
# yum list|grep -i unixodbc unixODBC.x86_64 2.2.14-14.el6 @dvd unixODBC-devel.x86_64 2.2.14-14.el6 @dvd unixODBC.i686 2.2.14-14.el6 dvd unixODBC-devel.i686 2.2.14-14.el6 dvd |
ODBC 驅動管理器 rpm 包安裝後相關檔案
|
# rpm -ql unixODBC.x86_64 /etc/odbc.ini /etc/odbcinst.ini /usr/bin/dltest /usr/bin/isql /usr/bin/iusql /usr/bin/odbc_config /usr/bin/odbcinst /usr/lib64/libboundparam.so.2 /usr/lib64/libboundparam.so.2.0.0 /usr/lib64/libesoobS.so.2 /usr/lib64/libesoobS.so.2.0.0 /usr/lib64/libgtrtst.so.2 /usr/lib64/libgtrtst.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/lib64/libodbc.so /usr/lib64/libodbc.so.2 /usr/lib64/libodbc.so.2.0.0 /usr/lib64/libodbccr.so.2 /usr/lib64/libodbccr.so.2.0.0 /usr/lib64/libodbcdrvcfg1S.so.2 /usr/lib64/libodbcdrvcfg1S.so.2.0.0 /usr/lib64/libodbcdrvcfg2S.so.2 /usr/lib64/libodbcdrvcfg2S.so.2.0.0 /usr/lib64/libodbcinst.so /usr/lib64/libodbcinst.so.2 /usr/lib64/libodbcinst.so.2.0.0 /usr/lib64/libodbcminiS.so.2 /usr/lib64/libodbcminiS.so.2.0.0 /usr/lib64/libodbcmyS.so /usr/lib64/libodbcmyS.so.2 /usr/lib64/libodbcmyS.so.2.0.0 /usr/lib64/libodbcnnS.so.2 /usr/lib64/libodbcnnS.so.2.0.0 /usr/lib64/libodbcpsqlS.so /usr/lib64/libodbcpsqlS.so.2 /usr/lib64/libodbcpsqlS.so.2.0.0 /usr/lib64/libodbctxtS.so.2 /usr/lib64/libodbctxtS.so.2.0.0 /usr/lib64/liboplodbcS.so.2 /usr/lib64/liboplodbcS.so.2.0.0 /usr/lib64/liboraodbcS.so.2 /usr/lib64/liboraodbcS.so.2.0.0 /usr/lib64/libsapdbS.so.2 /usr/lib64/libsapdbS.so.2.0.0 /usr/lib64/libtdsS.so.2 /usr/lib64/libtdsS.so.2.0.0 /usr/lib64/libtemplate.so.2 /usr/lib64/libtemplate.so.2.0.0 /usr/share/doc/unixODBC-2.2.14 /usr/share/doc/unixODBC-2.2.14/AUTHORS /usr/share/doc/unixODBC-2.2.14/COPYING /usr/share/doc/unixODBC-2.2.14/ChangeLog /usr/share/doc/unixODBC-2.2.14/NEWS /usr/share/doc/unixODBC-2.2.14/README /usr/share/doc/unixODBC-2.2.14/doc /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/index.html /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/odbcinst.html /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/php3.html /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/unixODBC.gif /usr/share/doc/unixODBC-2.2.14/doc/AdministratorManual/unixODBCsetup.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/close.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/conne.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/dsn.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/gloss.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/index.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/intro.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/navi.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/odbc.css /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/query.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/Tutorial/resul.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/index.html /usr/share/doc/unixODBC-2.2.14/doc/ProgrammerManual/unixODBC.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure1.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure2.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure3.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure4.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/Figure6.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/My.sql /usr/share/doc/unixODBC-2.2.14/doc/UserManual/StarOfficeDataGrid.gif /usr/share/doc/unixODBC-2.2.14/doc/UserManual/index.html /usr/share/doc/unixODBC-2.2.14/doc/UserManual/unixODBC.gif /usr/share/doc/unixODBC-2.2.14/doc/index.html /usr/share/doc/unixODBC-2.2.14/doc/lst /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure.vsd /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure2.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure2.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure3.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure3.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure4.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure4.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure5.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure5.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure6.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure6.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure7.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure7.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure8.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure8.html /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure9.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/InternalStructure9.html /usr/share/doc/unixODBC-2.2.14/doc/lst/back.gif /usr/share/doc/unixODBC-2.2.14/doc/lst/next.gif /usr/share/doc/unixODBC-2.2.14/doc/smallbook.gif /usr/share/doc/unixODBC-2.2.14/doc/unixODBC.gif |
下載及安裝 ODBC Driver
下載地址：
解壓介質並安裝
|
sftp> put -r "C:\Users\xh\Desktop\mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit.tar.gz" # mkdir -p /soft # tar zxvf /soft/mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit.tar.gz # mv /soft/mysql-connector-odbc-5.1.12-linux-glibc2.5-x86-64bit/* /usr/local/mysql-connector-odbc/ |
配置 ODBC data source
|
# vi /etc/odbc.ini [myodbc5] Driver = /usr/local/mysql-connector-odbc/lib/libmyodbc5.so Description = Connector/ODBC 5.1 Driver DSN SERVER = 192.168.210.125 PORT = 3306 USER = backup PASSWORD = mysql DATABASE = zj20_sunft OPTION = 0 TRACE = OFF |
建立 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 |
驗證 ODBC 至 MySQL Server 端的連線
|
# isql myodbc5 -v +------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +-------------------------+ SQL> |
配置 listener.ora
編輯監聽配置檔案，建立 LISTENER2 並對例項 myodbc5 進行靜態註冊
|
LISTENER2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1522)) ) )
SID_LIST_LISTENER2= (SID_LIST= (SID_DESC= (SID_NAME=myodbc5) (ORACLE_HOME=/oracle/app/product/11.2.0/db_1) (PROGRAM=dg4odbc) (ENV="LD_LIBRARY_PATH=/usr/lib64:/oracle/app/product/11.2.0/db_1/lib") ) ) |
啟動監聽 LISTENER2 並檢視監聽狀態
|
$ lsnrctl start LISTENER2 $ lsnrctl status LISTENER2 |
配置 tnsnames.ora
|
$ vi tnsnames.ora myodbc5 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc5) ) (HS = OK) ) |
驗證 myodbc5 連線串配置
|
$ tnsping myodbc5 TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 03-SEP-2018 18:54:56 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc5)) (HS = OK)) OK (10 msec) |
配置閘道器引數檔案 initmyodbc5.ora
|
$ cd $ORACLE_HOME/hs/admin $ vi initmyodbc5.ora HS_FDS_CONNECT_INFO=myodbc5 # 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 |
建立 Oracle DBlink
|
SQL> create public database link myodbc5 connect to "backup" identified by "mysql" using 'myodbc5'; |
驗證 Oracle DBlink
|
SQL> select count(*) from "test"@myodbc5; COUNT(*) ---------- 1835008 |
參考文件
配置 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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23732248/viewspace-2285234/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7OracleMySql
- Oracle19c dblink連結mysql8.0OracleMySql
- ORACLE 配置連線遠端資料庫Oracle資料庫
- mysql資料庫連線池配置教程MySql資料庫
- Oracle DBLink連線數過多的問題(Ora-02020)Oracle
- 配置ORACLE資料庫到達夢資料庫的異構DBLINKOracle資料庫
- JPA配置mysql連線MySql
- PL/Plus本地連線oracle配置Oracle
- Loopback 4配置連線MySQLOOPMySql
- MySQL 5.7配置SSL連線MySql
- linux配置mysql資料庫遠端連線失敗LinuxMySql資料庫
- Python連線三大資料庫MS Sql Server、Oracle、MySQLPython大資料資料庫ServerOracleMySql
- Python 連線 Oracle資料庫PythonOracle資料庫
- 使用mysqlclient庫連線mysqlMySqlclient
- 連線資料庫-mysql資料庫MySql
- 使用 Spring Boot 2.4配置Oracle的UCP資料庫連線池Spring BootOracle資料庫
- MySQL下載安裝配置及JDBC連線資料庫MySqlJDBC資料庫
- powerbuildr中如何使用dblink連線的表UI
- ORACLE通過透明閘道器建dblink連線Postgresql的幾個問題OracleSQL
- 用Navicat連線資料庫-資料庫連線(MySQL演示)資料庫MySql
- 配置達夢資料庫同構DBLINK資料庫
- 使用instantclient連線oracle資料庫clientOracle資料庫
- [Navicate]Navicate連線Oracle資料庫Oracle資料庫
- Mybatis配置資料庫連線MyBatis資料庫
- 如何連線MySQL資料庫MySql資料庫
- django | 連線mysql資料庫DjangoMySql資料庫
- Python連線MySQL資料庫PythonMySql資料庫
- pycharm連線MySQL資料庫PyCharmMySql資料庫
- Oracle資料庫連結（DBLink）中如何訪問包含BLOB欄位的資料Oracle資料庫
- 成為MySQL DBA後，再看ORACLE資料庫（二、監聽與連線）MySqlOracle資料庫
- Oracle dos連線資料庫基本操作Oracle資料庫
- 如何配置MTS以穿越防火牆連線oracle防火牆Oracle
- 遠端連線mysql資料庫MySql資料庫
- Django 2連線MySQL資料庫DjangoMySql資料庫
- 使用PETAPOCO連線MYSQL資料庫MySql資料庫
- 使用cmd連線mysql資料庫MySql資料庫
- Spring JPA資料庫連線MySQLSpring資料庫MySql
- mysql資料庫怎麼連線MySql資料庫