首先，从官方网站下载GATEWAY的介质：
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html
解压，并安装：
[root@mysql-me software]# ls gateways Percona-Server-client-57-5.7.18-14.1.el6.x86_64.rpm Percona-Server-test-57-5.7.18-14.1.el6.x86_64.rpm linux.x64_11gR2_gateways.zip Percona-Server-devel-57-5.7.18-14.1.el6.x86_64.rpm Percona-Server-tokudb-57-5.7.18-14.1.el6.x86_64.rpm Percona-Server-5.7.18-14-r2c06f4d-el6-x86_64-bundle.tar Percona-Server-server-57-5.7.18-14.1.el6.x86_64.rpm repodata Percona-Server-57-debuginfo-5.7.18-14.1.el6.x86_64.rpm Percona-Server-shared-57-5.7.18-14.1.el6.x86_64.rpm [root@mysql-me software]# [root@mysql-me software]# cd gateways/ [root@mysql-me gateways]# [root@mysql-me gateways]# ls doc install legacy response runInstaller stage welcome.html [root@mysql-me gateways]#
安装过程的图形化，跟之前的文档是差不多的：
需要注意的是，选择产品的时候选择Database Gateway for ODBC，就可以了。
本文的重点在配置部分。
MySQL的准备：
分配权限：
[root@mysql-me ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.18-14 Percona Server (GPL), Release 14, Revision 2c06f4d Copyright (c) 2009-2017 Percona LLC and/or its affiliates 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> grant all privileges on *.* to 'root'@'192.168.92.%' identified by 'XXXXXX' with grant option; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> exit Bye [root@mysql-me ~]#
安装：unixODBC
[root@mysql-me ~]# yum list | grep unixODBC freeradius-unixODBC.x86_64 2.2.6-6.el6_7 base unixODBC.i686 2.2.14-14.el6 base unixODBC.x86_64 2.2.14-14.el6 base unixODBC-devel.i686 2.2.14-14.el6 base unixODBC-devel.x86_64 2.2.14-14.el6 base unixODBC-kde.i686 2.2.14-14.el6 base unixODBC-kde.x86_64 2.2.14-14.el6 base [root@mysql-me ~]# [root@mysql-me ~]# yum install -y unixODBC* Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Install Process Loading mirror speeds from cached hostfile * base: mirrors.zju.edu.cn * extras: mirrors.zju.edu.cn * updates: mirrors.zju.edu.cn Resolving Dependencies --> Running transaction check ---> Package unixODBC.x86_64 0:2.2.14-14.el6 will be installed ---> Package unixODBC-devel.x86_64 0:2.2.14-14.el6 will be installed ---> Package unixODBC-kde.x86_64 0:2.2.14-14.el6 will be installed --> Processing Dependency: libQtNetwork.so.4()(64bit) for package: unixODBC-kde-2.2.14-14.el6.x86_64 --> Processing Dependency: libQtGui.so.4()(64bit) for package: unixODBC-kde-2.2.14-14.el6.x86_64 --> Processing Dependency: libQtCore.so.4()(64bit) for package: unixODBC-kde-2.2.14-14.el6.x86_64 --> Processing Dependency: libQtAssistantClient.so.4()(64bit) for package: unixODBC-kde-2.2.14-14.el6.x86_64 --> Running transaction check ---> Package qt.x86_64 1:4.6.2-28.el6_5 will be installed ---> Package qt-x11.x86_64 1:4.6.2-28.el6_5 will be installed --> Processing Dependency: qt-sqlite(x86-64) = 1:4.6.2-28.el6_5 for package: 1:qt-x11-4.6.2-28.el6_5.x86_64 --> Processing Dependency: phonon-backend(x86-64) >= 4.3 for package: 1:qt-x11-4.6.2-28.el6_5.x86_64 --> Processing Dependency: libmng.so.1()(64bit) for package: 1:qt-x11-4.6.2-28.el6_5.x86_64 --> Running transaction check ---> Package libmng.x86_64 0:1.0.10-4.1.el6 will be installed ---> Package phonon-backend-gstreamer.x86_64 1:4.6.2-28.el6_5 will be installed ---> Package qt-sqlite.x86_64 1:4.6.2-28.el6_5 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================================================ Package Arch Version Repository Size ================================================================================================================================================================================ Installing: unixODBC x86_64 2.2.14-14.el6 base 378 k unixODBC-devel x86_64 2.2.14-14.el6 base 53 k unixODBC-kde x86_64 2.2.14-14.el6 base 220 k Installing for dependencies: libmng x86_64 1.0.10-4.1.el6 base 165 k phonon-backend-gstreamer x86_64 1:4.6.2-28.el6_5 base 127 k qt x86_64 1:4.6.2-28.el6_5 base 3.9 M qt-sqlite x86_64 1:4.6.2-28.el6_5 base 51 k qt-x11 x86_64 1:4.6.2-28.el6_5 base 12 M Transaction Summary ================================================================================================================================================================================ Install 8 Package(s) Total download size: 17 M Installed size: 48 M Downloading Packages: (1/8): libmng-1.0.10-4.1.el6.x86_64.rpm | 165 kB 00:00 (2/8): phonon-backend-gstreamer-4.6.2-28.el6_5.x86_64.rpm | 127 kB 00:00 (3/8): qt-4.6.2-28.el6_5.x86_64.rpm | 3.9 MB 00:02 (4/8): qt-sqlite-4.6.2-28.el6_5.x86_64.rpm | 51 kB 00:00 (5/8): qt-x11-4.6.2-28.el6_5.x86_64.rpm | 12 MB 00:15 (6/8): unixODBC-2.2.14-14.el6.x86_64.rpm | 378 kB 00:00 (7/8): unixODBC-devel-2.2.14-14.el6.x86_64.rpm | 53 kB 00:00 (8/8): unixODBC-kde-2.2.14-14.el6.x86_64.rpm | 220 kB 00:00 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 832 kB/s | 17 MB 00:20 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : 1:qt-4.6.2-28.el6_5.x86_64 1/8 Installing : unixODBC-2.2.14-14.el6.x86_64 2/8 Installing : 1:qt-sqlite-4.6.2-28.el6_5.x86_64 3/8 Installing : libmng-1.0.10-4.1.el6.x86_64 4/8 Installing : 1:phonon-backend-gstreamer-4.6.2-28.el6_5.x86_64 5/8 Installing : 1:qt-x11-4.6.2-28.el6_5.x86_64 6/8 Installing : unixODBC-kde-2.2.14-14.el6.x86_64 7/8 Installing : unixODBC-devel-2.2.14-14.el6.x86_64 8/8 Verifying : unixODBC-kde-2.2.14-14.el6.x86_64 1/8 Verifying : 1:qt-sqlite-4.6.2-28.el6_5.x86_64 2/8 Verifying : 1:phonon-backend-gstreamer-4.6.2-28.el6_5.x86_64 3/8 Verifying : unixODBC-devel-2.2.14-14.el6.x86_64 4/8 Verifying : unixODBC-2.2.14-14.el6.x86_64 5/8 Verifying : libmng-1.0.10-4.1.el6.x86_64 6/8 Verifying : 1:qt-x11-4.6.2-28.el6_5.x86_64 7/8 Verifying : 1:qt-4.6.2-28.el6_5.x86_64 8/8 Installed: unixODBC.x86_64 0:2.2.14-14.el6 unixODBC-devel.x86_64 0:2.2.14-14.el6 unixODBC-kde.x86_64 0:2.2.14-14.el6 Dependency Installed: libmng.x86_64 0:1.0.10-4.1.el6 phonon-backend-gstreamer.x86_64 1:4.6.2-28.el6_5 qt.x86_64 1:4.6.2-28.el6_5 qt-sqlite.x86_64 1:4.6.2-28.el6_5 qt-x11.x86_64 1:4.6.2-28.el6_5 Complete! [root@mysql-me ~]# [root@mysql-me mysql-connector]# yum list | grep qt3 avahi-qt3.i686 0.6.25-17.el6 base avahi-qt3.x86_64 0.6.25-17.el6 base avahi-qt3-devel.i686 0.6.25-17.el6 base avahi-qt3-devel.x86_64 0.6.25-17.el6 base qt3.i686 3.3.8b-30.el6 base qt3.x86_64 3.3.8b-30.el6 base qt3-MySQL.i686 3.3.8b-30.el6 base qt3-MySQL.x86_64 3.3.8b-30.el6 base qt3-ODBC.i686 3.3.8b-30.el6 base qt3-ODBC.x86_64 3.3.8b-30.el6 base qt3-PostgreSQL.i686 3.3.8b-30.el6 base qt3-PostgreSQL.x86_64 3.3.8b-30.el6 base qt3-config.x86_64 3.3.8b-30.el6 base qt3-designer.x86_64 3.3.8b-30.el6 base qt3-devel.i686 3.3.8b-30.el6 base qt3-devel.x86_64 3.3.8b-30.el6 base qt3-devel-docs.x86_64 3.3.8b-30.el6 base qt3-sqlite.i686 3.3.8b-30.el6 base qt3-sqlite.x86_64 3.3.8b-30.el6 base [root@mysql-me mysql-connector]# yum install -y qt3-ODBC* qt3-MySQL* Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Install Process Loading mirror speeds from cached hostfile * base: mirrors.zju.edu.cn * extras: mirrors.zju.edu.cn * updates: mirrors.zju.edu.cn Resolving Dependencies --> Running transaction check ---> Package qt3-MySQL.x86_64 0:3.3.8b-30.el6 will be installed --> Processing Dependency: qt3 = 3.3.8b-30.el6 for package: qt3-MySQL-3.3.8b-30.el6.x86_64 --> Processing Dependency: libqt-mt.so.3()(64bit) for package: qt3-MySQL-3.3.8b-30.el6.x86_64 ---> Package qt3-ODBC.x86_64 0:3.3.8b-30.el6 will be installed --> Running transaction check ---> Package qt3.x86_64 0:3.3.8b-30.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================================================ Package Arch Version Repository Size ================================================================================================================================================================================ Installing: qt3-MySQL x86_64 3.3.8b-30.el6 base 58 k qt3-ODBC x86_64 3.3.8b-30.el6 base 76 k Installing for dependencies: qt3 x86_64 3.3.8b-30.el6 base 3.5 M Transaction Summary ================================================================================================================================================================================ Install 3 Package(s) Total download size: 3.6 M Installed size: 11 M Downloading Packages: (1/3): qt3-3.3.8b-30.el6.x86_64.rpm | 3.5 MB 00:00 (2/3): qt3-MySQL-3.3.8b-30.el6.x86_64.rpm | 58 kB 00:00 (3/3): qt3-ODBC-3.3.8b-30.el6.x86_64.rpm | 76 kB 00:00 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 5.9 MB/s | 3.6 MB 00:00 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : qt3-3.3.8b-30.el6.x86_64 1/3 Installing : qt3-ODBC-3.3.8b-30.el6.x86_64 2/3 Installing : qt3-MySQL-3.3.8b-30.el6.x86_64 3/3 Verifying : qt3-3.3.8b-30.el6.x86_64 1/3 Verifying : qt3-ODBC-3.3.8b-30.el6.x86_64 2/3 Verifying : qt3-MySQL-3.3.8b-30.el6.x86_64 3/3 Installed: qt3-MySQL.x86_64 0:3.3.8b-30.el6 qt3-ODBC.x86_64 0:3.3.8b-30.el6 Dependency Installed: qt3.x86_64 0:3.3.8b-30.el6 Complete! [root@mysql-me mysql-connector]#
安装并配置：MySQL Connector ODBC
可以从Oracle官方获取相关介质：
https://dev.mysql.com/downloads/connector/odbc/#downloads
下载后，上传服务器，解压，并放到指定的目录中：
[oracle@mysql-me ~]$ cd /software [oracle@mysql-me software]$ ls gateways Percona-Server-57-debuginfo-5.7.18-14.1.el6.x86_64.rpm Percona-Server-shared-57-5.7.18-14.1.el6.x86_64.rpm linux.x64_11gR2_gateways.zip Percona-Server-client-57-5.7.18-14.1.el6.x86_64.rpm Percona-Server-test-57-5.7.18-14.1.el6.x86_64.rpm mysql-connector-odbc-5.3.8-linux-el6-x86-64bit.tar.gz Percona-Server-devel-57-5.7.18-14.1.el6.x86_64.rpm Percona-Server-tokudb-57-5.7.18-14.1.el6.x86_64.rpm Percona-Server-5.7.18-14-r2c06f4d-el6-x86_64-bundle.tar Percona-Server-server-57-5.7.18-14.1.el6.x86_64.rpm repodata [oracle@mysql-me software]$ [oracle@mysql-me software]$ tar -xzf mysql-connector-odbc-5.3.8-linux-el6-x86-64bit.tar.gz [oracle@mysql-me software]$ [oracle@mysql-me software]$ ls gateways Percona-Server-5.7.18-14-r2c06f4d-el6-x86_64-bundle.tar Percona-Server-server-57-5.7.18-14.1.el6.x86_64.rpm repodata linux.x64_11gR2_gateways.zip Percona-Server-57-debuginfo-5.7.18-14.1.el6.x86_64.rpm Percona-Server-shared-57-5.7.18-14.1.el6.x86_64.rpm mysql-connector-odbc-5.3.8-linux-el6-x86-64bit Percona-Server-client-57-5.7.18-14.1.el6.x86_64.rpm Percona-Server-test-57-5.7.18-14.1.el6.x86_64.rpm mysql-connector-odbc-5.3.8-linux-el6-x86-64bit.tar.gz Percona-Server-devel-57-5.7.18-14.1.el6.x86_64.rpm Percona-Server-tokudb-57-5.7.18-14.1.el6.x86_64.rpm [oracle@mysql-me software]$ [oracle@mysql-me software]$ cd mysql-connector-odbc-5.3.8-linux-el6-x86-64bit [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ ls bin ChangeLog COPYING INSTALL lib Licenses_for_Third-Party_Components.txt README README.debug test [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ ls -ltra /u01/app/ total 16 drwxrwxrwx 3 oracle oinstall 4096 May 24 01:07 .. drwxrwxrwx 4 oracle oinstall 4096 May 24 01:12 . drwxrwx--- 5 oracle oinstall 4096 May 24 01:17 oraInventory drwxrwxrwx 5 oracle oinstall 4096 May 24 01:17 oracle [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ mkdir /u01/app/mysql-connector [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ cp -rf * /u01/app/mysql-connector/ [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$ ls -ltra /u01/app/mysql-connector/ total 224 drwxrwxrwx 5 oracle oinstall 4096 May 24 01:31 .. drwxr-xr-x 2 oracle oinstall 4096 May 24 01:31 bin -rw-r--r-- 1 oracle oinstall 5313 May 24 01:31 INSTALL -rw-r--r-- 1 oracle oinstall 18122 May 24 01:31 COPYING -rw-r--r-- 1 oracle oinstall 60925 May 24 01:31 ChangeLog drwxr-xr-x 2 oracle oinstall 4096 May 24 01:31 lib -rw-r--r-- 1 oracle oinstall 2996 May 24 01:31 README.debug -rw-r--r-- 1 oracle oinstall 5690 May 24 01:31 README -rw-r--r-- 1 oracle oinstall 103161 May 24 01:31 Licenses_for_Third-Party_Components.txt drwxr-xr-x 5 oracle oinstall 4096 May 24 01:31 . drwxr-xr-x 2 oracle oinstall 4096 May 24 01:31 test [oracle@mysql-me mysql-connector-odbc-5.3.8-linux-el6-x86-64bit]$
配置ODBC配置文件：
[oracle@mysql-me mysql-connector]$ pwd /u01/app/mysql-connector [oracle@mysql-me mysql-connector]$ [oracle@mysql-me mysql-connector]$ cat odbc.ini DATABASE=THEME SERVER=192.168.92.168 [mysql5718] Driver=/u01/app/mysql-connector/lib/libmyodbc5a.so DESCRIPTION=Connector/ODBC SERVER=192.168.92.168 PORT=3306 USER=root PASSWORD=XXXXXXX DATABASE=theme OPTION=0 TRACE=on [oracle@mysql-me mysql-connector]$ [root@mysql-me gateways]# cat /etc/odbc.ini [mysql5718] Driver=/u01/app/mysql-connector/lib/libmyodbc5a.so SERVER=192.168.92.168 PORT=3306 USER=root PASSWORD=Abcd1@34 DATABASE=theme OPTION=0 TRACE=on [root@mysql-me gateways]# [root@mysql-me gateways]#
加入环境变量：
[oracle@mysql-me mysql-connector]$ cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Database 11g RAC
# Oracle Node1
ORACLE_SID=edendb1; export ORACLE_SID
ORACLE_UNQNAME=edendb; export ORACLE_UNQNAME
JAVA_HOME=/usr/local/java; export JAVA_HOME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_PATH=/u01/app/common/oracle/sql; export ORACLE_PATH
ORACLE_TERM=xterm; export ORACLE_TERM
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:/u01/app/mysql-connector/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/mysql-connector/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
#export ODBCINI=/u01/app/mysql-connector/odbc.ini
export ODBCINI=/etc/odbc.ini
[oracle@mysql-me mysql-connector]$
[oracle@mysql-me mysql-connector]$ cat ~/.bash_profile | grep --color mysql-connector
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:/u01/app/mysql-connector/bin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/mysql-connector/lib
export ODBCINI=/u01/app/mysql-connector/odbc.ini
[oracle@mysql-me mysql-connector]$
[oracle@mysql-me mysql-connector]$ source ~/.bash_profile
[oracle@mysql-me mysql-connector]$
[oracle@mysql-me mysql-connector]$ env | grep --color mysql-connector
OLDPWD=/software/mysql-connector-odbc-5.3.8-linux-el6-x86-64bit
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/u01/app/mysql-connector/lib:/u01/app/oracle/product/11.2.0/dbhome_1/oracm/lib:/lib:/usr/lib:/usr/local/lib
PATH=.:/usr/local/java/bin:.:/usr/local/java/bin:.:/usr/local/java/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/u01/app/common/oracle/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/u01/app/mysql-connector/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/u01/app/common/oracle/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin:/u01/app/mysql-connector/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:/u01/app/common/oracle/bin
PWD=/u01/app/mysql-connector
ODBCINI=/u01/app/mysql-connector/odbc.ini
[oracle@mysql-me mysql-connector]$
测试ODBC：
[root@mysql-me gateways]# isql --verbose unixODBC 2.2.14 [root@mysql-me gateways]# [oracle@mysql-me ~]$ isql mysql5718 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases; +-----------------------------------------------------------------+ | Database | +-----------------------------------------------------------------+ | information_schema | | mysql | | performance_schema | | sys | | theme | +-----------------------------------------------------------------+ SQLRowCount returns 5 5 rows fetched SQL> show tables; +-----------------------------------------------------------------+ | Tables_in_theme | +-----------------------------------------------------------------+ | people | +-----------------------------------------------------------------+ SQLRowCount returns 1 1 rows fetched SQL> [oracle@mysql-me ~]$ [root@mysql-me gateways]#
配置Oracle GATEWAY：
[oracle@mysql-me admin]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/hs/admin [oracle@mysql-me admin]$ [oracle@mysql-me admin]$ ls -ltr total 16 -rwxr-xr-x 1 oracle oinstall 1109 Aug 14 2009 extproc.ora -rwxr-xr-x 1 oracle oinstall 411 May 24 01:16 listener.ora.sample -rwxr-xr-x 1 oracle oinstall 244 May 24 01:16 tnsnames.ora.sample -rwxr-xr-x 1 oracle oinstall 760 May 24 02:52 initdg4odbc.ora [oracle@mysql-me admin]$ [oracle@mysql-me admin]$ cat initdg4odbc.ora # This is a sample agent init file that contains the HS parameters that are # needed for the Database Gateway for ODBC # # HS init parameters # #HS_FDS_CONNECT_INFO = #HS_FDS_TRACE_LEVEL = #HS_FDS_SHAREABLE_NAME = # # ODBC specific environment variables # #set ODBCINI= # # Environment variables required for the non-Oracle system # #set = HS_FDS_CONNECT_INFO = mysql5718 HS_FDS_TRACE_LEVEL = debug HS_FDS_SHAREABLE_NAME = /u01/app/mysql-connector/lib/libmyodbc5a.so HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 HS_FDS_SQLLEN_INTERPRETATION=32 HS_LONG_PIECE_TRANSFER_SIZE=1258291 set ODBCINI=/etc/odbc.ini [oracle@mysql-me admin]$ [oracle@mysql-me admin]$ [oracle@mysql-me admin]$ cp initdg4odbc.ora initmysql5718.ora [oracle@mysql-me admin]$
修改Oracle的监听器：
[oracle@mysql-me admin]$ cd $ORACLE_HOME/network/admin
[oracle@mysql-me admin]$ ls -ltr
total 16
-rwxr-xr-x 1 oracle oinstall 187 May 7 2007 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 May 24 01:15 samples
-rwxr-xr-x 1 oracle oinstall 223 May 24 01:17 sqlnet.ora
-rwxr-xr-x 1 oracle oinstall 671 May 24 03:02 listener.ora
[oracle@mysql-me admin]$
[oracle@mysql-me admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
( SID_LIST =
( SID_DESC =
(SID_NAME=mysql5718)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/u01/app/oracle/product/11.2.0/dbhome_1/hs/lib)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = mysql-me)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@mysql-me admin]$
[oracle@mysql-me admin]$注意：
上面监听器中的【SID_NAME=mysql5718】要和【initmysql5718.ora】名字一致。
重启或重新加载监听器：
[oracle@mysql-me admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-MAY-2017 03:03:09 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 24-MAY-2017 01:17:55 Uptime 0 days 1 hr. 45 min. 15 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/mysql-me/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mysql-me)(PORT=1521))) Services Summary... Service "mysql5718" has 1 instance(s). Instance "mysql5718", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@mysql-me admin]$ [oracle@mysql-me admin]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-MAY-2017 03:03:13 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) The command completed successfully [oracle@mysql-me admin]$ [oracle@mysql-me admin]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-MAY-2017 03:03:24 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/mysql-me/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mysql-me)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 24-MAY-2017 03:03:24 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/mysql-me/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mysql-me)(PORT=1521))) Services Summary... Service "mysql5718" has 1 instance(s). Instance "mysql5718", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@mysql-me admin]$ [oracle@mysql-me admin]$
在另一台Oracle服务器上，创建DBLINK，并连接：
创建TNSNAME：
[oracle@oracle-me admin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@oracle-me admin]$ ls -ltr
total 20
-rw-r--r-- 1 oracle oinstall 381 Dec 17 2012 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 May 23 23:40 samples
-rw-r--r-- 1 oracle oinstall 223 May 23 23:44 sqlnet.ora
-rw-r--r-- 1 oracle oinstall 374 May 23 23:44 listener.ora
-rw-r----- 1 oracle oinstall 673 May 24 03:05 tnsnames.ora
[oracle@oracle-me admin]$
[oracle@oracle-me admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LENKA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle-me)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lenka)
)
)
dg4msql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.170)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = dg4msql)
)
(HS=OK)
)
mysql5718 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.168)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = mysql5718)
)
(HS=OK)
)
[oracle@oracle-me admin]$
[oracle@oracle-me admin]$ tnsping mysql5718
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-MAY-2017 03:05:41
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.168)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = mysql5718)) (HS=OK))
OK (0 msec)
[oracle@oracle-me admin]$
创建DBLINK：
[oracle@oracle-me admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed May 24 03:08:52 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create database link mysql_me connect to "root" identified by "Abcd1@34" using 'mysql5718'; Database link created. SQL>
MySQL创建样例数据：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | theme | +--------------------+ 5 rows in set (0.00 sec) mysql> use theme; Database changed mysql> show tables; Empty set (0.00 sec) mysql> create table people(p_id int,p_name char(30)); Query OK, 0 rows affected (0.04 sec) mysql> insert into people values(0,"Lenka"); Query OK, 1 row affected (0.05 sec) mysql> insert into people values(1,"mumu"); Query OK, 1 row affected (0.00 sec) mysql> insert into people values(2,"Ruoer"); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from people; +------+--------+ | p_id | p_name | +------+--------+ | 0 | Lenka | | 1 | mumu | | 2 | Ruoer | +------+--------+ 3 rows in set (0.00 sec) mysql>
然后用Oracle查询MySQL：
SQL> select * from "theme"."people"@mysql_me;
p_id
----------
p_name
--------------------------------------------------------------------------------
0
Lenka
1
mumu
2
p_id
----------
p_name
--------------------------------------------------------------------------------
Ruoer
SQL>
SQL> set pagesize 23
SQL> select * from "theme"."people"@mysql_me;
p_id p_name
---------- -------------
0 Lenka
p_id p_name
---------- -------------
1 mumu
p_id p_name
---------- -------------
2 Ruoer
p_id p_name
---------- -------------
SQL>
SQL>如上，就通过DBLINK访问到了MySQL的数据了。
Question：这里的格式，好像总是不太好看？
——————————————————
Done。