一、前言
在Centos6.4系统下oracle11gR2版本下连接mysql5.x版本,这边采用ODBC+DBLINK的方式进行配置访问,DG4ODBC数据网关连接其它非Oracle数据库,原理图如下所示，从图中可以看需要准备DG4ODBC, ODBC Driver Manager, Mysql ODBC Driver
二、操作步骤
1. 判断系统环境是32位 or 64位,如下，可以判断是64位系统
[oracle@lx16 ~]$ file $ORACLE_HOME/bin/dg4odbc @b@/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), not stripped
2. 下载安装mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz，本站下载，移动到解压后的文件到合适的路径，在下面odbc.ini需要配置到该路径下面
[oracle@xoms soft]$ wget http://dev.mysql.com/get/Downloads/Connector-ODBC/5.2/mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz/from/http://cdn.mysql.com/ @b@[oracle@xoms soft]$ tar -zxvf mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz @b@[oracle@xoms soft]$ mv mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit /home/oracle/app/my-odbc-driver-5.3.6
3. 下载unixODBC-2.2.14-linux-x86-64.tar.gz，本地下载，解压安装，具体脚本如下所示
[oracle@xoms soft]$ wget http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download @b@[oracle@xoms soft]$ ls -l unixODBC-2.3.4.tar.gz@b@-rwxrwxrwx. 1 root root 1830660 Jan 16 11:25 unixODBC-2.3.4.tar.gz@b@[oracle@xoms soft]$ tar -zxvf unixODBC-2.3.4.tar.gz@b@...@b@[oracle@xoms /]$ ./configure --prefix=/usr/local/unixODBC@b@...@b@[oracle@xoms /]$ make & make install
4. 在安装的/usr/local/unixODBC/etc/路径下配置odbc.ini配置文件
[oracle@xoms /]$ vi /usr/local/unixODBC/etc/odbc.ini @b@[myodbc5]@b@Driver=/home/oracle/app/my-odbc-driver-5.3.6/lib/libmyodbc5w.so@b@Description=Connector/ODBC 5.3.6 Driver DSN@b@SERVER=192.168.1.222@b@PORT=3306@b@USER=test@b@PASSWORD=123456@b@DATABASE=dbtest@b@OPTION=0@b@TRACE=OFF
5. 在.bash_profile配置相关环节变量LD_LIBRARY_PATH、UNIXODBC_DIR、ODBCINI及ODBCSYSINI
[oracle@xoms ~]$ vi .bash_profile @b@# .bash_profile@b@@b@# Get the aliases and functions@b@if [ -f ~/.bashrc ]; then@b@ . ~/.bashrc@b@fi@b@@b@# User specific environment and startup programs@b@@b@PATH=$PATH:$HOME/bin@b@@b@export PATH@b@@b@umask 022@b@@b@ORACLE_BASE=/home/oracle/app@b@ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1@b@ORACLE_SID=chlitina@b@PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin@b@LD_LIBRARY_PATH=/usr/local/unixODBC/lib:$ORACLE_HOME/lib:/usr/lib@b@UNIXODBC_DIR=/usr/local/unixODBC@b@ODBCINI=$UNIXODBC_DIR/etc/odbc.ini@b@ODBCSYSINI=$UNIXODBC_DIR/etc@b@export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH UNIXODBC_DIR ODBCINI ODBCSYSINI
6. 验证ODBC连接
[oracle@xoms bin]$ cd /usr/local/unixODBC/bin@b@[oracle@xoms bin]$ ./isql myodbc5 -v@b@+---------------------------------------+@b@| Connected! |@b@| |@b@| sql-statement |@b@| help [tablename] |@b@| quit |@b@| |@b@+---------------------------------------+@b@SQL>
7. 配置tnsnames.ora
[oracle@xoms admin]$ vi tnsnames.ora@b@# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora@b@# Generated by Oracle configuration tools.@b@@b@ # ...@b@@b@myodbc5 =@b@ (DESCRIPTION =@b@ (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))@b@ (CONNECT_DATA =@b@ (SID = myodbc5)@b@ )@b@ (HS=OK)@b@ )
8. 配置listener.ora
[oracle@xoms admin]$ vi listener.ora@b@# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora@b@# Generated by Oracle configuration tools.@b@@b@LISTENER =@b@ (DESCRIPTION_LIST =@b@ (DESCRIPTION =@b@ (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))@b@ (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))@b@ )@b@ )@b@@b@ADR_BASE_LISTENER = /home/oracle/app@b@@b@@b@SID_LIST_LISTENER=@b@ (SID_LIST=@b@ (SID_DESC=@b@ (SID_NAME=myodbc5)@b@ (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)@b@ (PROGRAM=dg4odbc)@b@ (ENVS=LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib)@b@ )@b@ )@b@ @b@ @b@"listener.ora" 23L, 695C written
9. 创建oracle相关init<sid>.ora文件
[oracle@xoms admin]$ cd /home/oracle/app/oracle/product/11.2.0/dbhome_1/hs/admin@b@[oracle@xoms admin]$ touch initmyodbc5.ora@b@[oracle@xoms admin]$ vi initmyodbc5.ora @b@HS_FDS_CONNECT_INFO = myodbc5@b@HS_FDS_TRACE_LEVEL = off@b@HS_FDS_TRACE_FILE_NAME = myodbc5.trc@b@HS_LANGUAGE = AMERICAN_AMERICA.UTF8@b@HS_NLS_NCHAR = UCS2@b@HS_FDS_SHAREABLE_NAME =/usr/local/unixODBC/lib/libodbc.so@b@set LD_LIBRARY_PATH = $LD_LIBRARY_PATH:/usr/lib:/usr/lib64@b@set ODBCINI=/usr/local/unixODBC/etc/odbc.ini
10. 使上述配置文件生效
[oracle@xoms admin]$ lsnrctl reload @b@[oracle@xoms admin]$ lsnrctl status @b@LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-JAN-2017 21:35:20@b@@b@Copyright (c) 1991, 2009, Oracle. All rights reserved.@b@@b@Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))@b@The command completed successfully@b@[oracle@xoms admin]$ lsnrctl status@b@@b@LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-JAN-2017 21:35:40@b@@b@Copyright (c) 1991, 2009, Oracle. All rights reserved.@b@@b@Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))@b@STATUS of the LISTENER@b@------------------------@b@Alias LISTENER@b@Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production@b@Start Date 19-JAN-2017 21:08:08@b@Uptime 1 days 0 hr. 27 min. 31 sec@b@Trace Level off@b@Security ON: Local OS Authentication@b@SNMP OFF@b@Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora@b@Listener Log File /home/oracle/app/diag/tnslsnr/xoms/listener/alert/log.xml@b@Listening Endpoints Summary...@b@ (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))@b@ (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xoms)(PORT=1521)))@b@Services Summary...@b@Service "chlitina" has 1 instance(s).@b@ Instance "chlitina", status READY, has 1 handler(s) for this service...@b@Service "chlitinaXDB" has 1 instance(s).@b@ Instance "chlitina", status READY, has 1 handler(s) for this service...@b@Service "myodbc5" has 1 instance(s).@b@ Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...@b@The command completed successfully
11. 验证配置是否正确
[oracle@xoms admin]$ tnsping myodbc5@b@@b@TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 20-JAN-2017 21:39:07@b@@b@Copyright (c) 1997, 2009, Oracle. All rights reserved.@b@@b@Used parameter files:@b@/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora@b@@b@@b@Used TNSNAMES adapter to resolve the alias@b@Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = myodbc5)) (HS=OK))@b@OK (0 msec)
12. 创建dblink名称mysqltest并查询mysql数据库dbtest表adminusers记录总数
[oracle@xoms admin]$ sqlplus / as sysdba@b@@b@SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 20 21:41:03 2017@b@@b@Copyright (c) 1982, 2009, Oracle. All rights reserved.@b@@b@@b@Connected to:@b@Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production@b@With the Partitioning, OLAP, Data Mining and Real Application Testing options@b@@b@SQL> create public database link mysqltest connect to "test" identified by "123456" using 'myodbc5';@b@@b@Database link created. @b@ @b@SQL> select count(*) from adminusers@mysqltest;@b@@b@ COUNT(*)@b@----------@b@ 35