Oracle 11gR2 访问 MySql 5.6_综合

一、环境信息

操作系统

Red Hat Enterprise Linux Server release 6.4 (Santiago)

Oracle版本信息

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

MySQL版本信息

mysql-community-server-5.6.27-2.el6.x86_64

主机信息

hostname	Ip-address	database
bakstore	10.9.9.x	oracle
tsl-ic-stg-db	10.9.5.x	mysql

二、程序包信息

mysql-connector-odbc-5.3.9-linux-el6-x86-64bit.tar.gz

unixODBC-2.3.4.tar.gz

三、配置过程

1.登录oracle用户：su – ora11

2.查看ODBC信息

file $ORACLE_HOME/bin/dg4odbc

3.安装ODBC Driver Manager

tar -zxvfunixODBC-2.3.4.tar.gzcd unixODBC-2.3.4./configure --prefix=/opt/odb/uodbc --disable-gui --enable-threads --disable-driversmakemake install

4.安装ODBC Driver for MySQL

tar -zxvf mysql-connector-odbc-5.3.9-linux-el6-x86-64bit.tar.gzmv mysql-connector-odbc-5.3.9-linux-el6-x86-64bit ~/oraclecd ~/oracleln -s mysql-connector-odbc-5.3.9-linux-el6-x86-64bit/ myodbc-5.3.9

5.配置ODBC Driver

cd /opt/odb/uodbc/etcvi odbc.ini[ODBC Data Sources]
data_source_name = myodbc5w
data_source_name = myodbc5a[myodbc5w]
Driver          = /home/ora11/oracle/myodbc-5.3.9/lib/libmyodbc5w.so
DATABASE        = tasly-ic
DESCRIPTION     = MySQL ODBC 5.3 Unicode Driver test
SERVER          = 10.9.5.138
UID             = test
PASSWORD        = 123456
PORT            = 3306[myodbc5a]
Driver          = /home/ora11/oracle/myodbc-5.3.9/lib/libmyodbc5a.so
DATABASE        = tasly-ic
DESCRIPTION     = MySQL ODBC 5.3 Unicode Driver test
SERVER          = 10.9.5.138
UID             = test
PASSWORD        = 123456
PORT            = 3306

注：Driver指第三步ODBC Driver for MySQL

6.oracle用户环境变量

vi ~/.bash_profileexport ODBCHOME=/opt/odb/uodbc
export ODBCSYSINI=${ODBCHOME}/etc
export ODBCINI=${ODBCSYSINI}/odbc.ini
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/share/lib:${ODBCHOME}/lib:/home/ora11/oracle/myodbc-5.3.9/lib
export LD_LIBRARY_PATH

7.配置initmyodbc5w.ora

vi $ORACLE_HOME/hs/admin/initmyodbc5w.oraHS_FDS_CONNECT_INFO=myodbc5w # Data source name in odbc.ini  
HS_FDS_SHAREABLE_NAME=/opt/odb/uodbc/lib/libodbc.so  
HS_FDS_SUPPORT_STATISTICS=FALSE  
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15  # ODBC env variables  
set ODBCINI=/opt/odb/uodbc/etc/odbc.ini

8.测试ODBC连接

cd /opt/odb/uodbc/bin./isql -v myodbc5w+---------------------------------------+  
| Connected!                            |  
|                                       |  
| sql-statement                         |  
| help [tablename]                      |  
| quit                                  |  
|                                       |  
+---------------------------------------+  上面显示连接成功。

9.查看odbc配置

cd /opt/odb/uodbc/bin./odbcinst -junixODBC 2.2.14
DRIVERS............: /opt/odb/uodbc/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/odb/uodbc/etc/odbc.ini
FILE DATA SOURCES..: /opt/odb/uodbc/etc/ODBCDataSources
USER DATA SOURCES..: /opt/odb/uodbc/etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

10.配置tnsnames.ora

cd $ORACLE_HOME/network/adminvi tnsnames.oramyodbc5w =  (DESCRIPTION=  (ADDRESS=  (PROTOCOL=TCP) (HOST = bakstore) (PORT=1520)  )  (CONNECT_DATA=  (SID=myodbc5w)  )  (HS=OK)  
)

11.配置listener.ora

cd $ORACLE_HOME/network/adminvi listener.oraSID_LIST_LISTENER2=(SID_LIST=(SID_DESC=(SID_NAME = myodbc5w)(ORACLE_HOME = /home/ora11/oracle/product/11.2.0/dbhome_1)(PROGRAM = dg4odbc)(ENVS = LD_LIBRARY_PATH=/opt/odb/uodbc/lib:/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib)))
LISTENER2 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = bakstore)(PORT = 1520))))

启动listener2

lsnrctlstart listener2lsnrctl status listener2LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-NOV-2017 15:42:45Copyright (c) 1991, 2013, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bakstore)(PORT=1520)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                02-NOV-2017 10:14:10
Uptime                    0 days 5 hr. 28 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/ora11/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/ora11/oracle/diag/tnslsnr/bakstore/listener2/alert/log.xml
Listening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bakstore)(PORT=1520)))
Services Summary...
Service "myodbc5w" has 1 instance(s).Instance "myodbc5w", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

验证myodbc5w

tnsping myodbc5w

12.创建database link

create public database link mysql1 connect to "test" identified by "123456" using 'myodbc5w';
test --- mysql用户
123456 --- mysql用户密码
myodbc5w --- tnsnames名

13.登录oracle查询mysql数据

select * from "ic_user"@mysql1;

至此oracle可以访问mysql数据