快捷搜索:  as  2018  FtCWSyGV  С˵  test  xxx  Ψһ  w3viyKQx

澳门新葡亰app在线下载:Oracle通过dblink连接MySQL示例



营业上有这么一个需求,必要把Oracle的一些数据同步到MySQL,假如每次都是手动同步的话,其实太麻烦,是以花了点光阴钻研了下Oracle直连MySQL的要领。

参考文档:Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)

版本信息:

Oracle: 11.2.0.1.0OS: CentOS 5.9

MySQL: 5.5.27OS: CentOS 5.8

道理:

Oracle应用DG4ODBC数据网干系接其它非Oracle数据库,其道理图如下:

从上图可知,澳门新葡亰app在线下载Oracle连接MySQL必要涉及到如下组件:DG4ODBC, ODBC Driver Manager, ODBC Driver,本文将逐一解说它们的设置设置设备摆设摆设。

1)判断32位照样64位

由于32位和64位的设置设置设备摆设摆设不一样,64位更繁杂一些,是以我们首先得确定Oracle和DG4ODBC是32位照样64位:

[oracle@lx16 ~]$ file $ORACLE_HOME/bin/dg4odbc

/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

从上面的输出可知是64位。

2)下载并安装ODBC Driver Manager

到这个澳门新葡亰app在线下载页面(http://www.unixodbc.org/download.html)根据你的OS下载unixodbc(留意:版本不能低于2.2.14)

$ wget http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download

解压缩:

$ tar -zxvf unixODBC-2.2.14-linux-x86-64.tar.gz

解压缩后会在当前目录下自动创建usr的目录,我们创建一个目录(~/app/unixodbc-2.2.14)用于放置unixodbc,然后把usr 迁移到该目录下:

$ mkdir ~/app/unixodbc-2.2.14

$ mv usr ~/app/unixodbc-2.2.14

3)下载并按照ODBC Driver for MySQL

到这个页面(http://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads)根据你的OS下载ODBC-5.2.5,本例选择64位tar版本:

$ 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/

$ tar -zxvf mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz

解压缩成功后是一个文件夹,把该文件夹迁移至~/app目录下,并给它创建一个软链接:

$ mv mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit ~/app

$ cd ~/app

$ ln -s mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit myodbc-5.2.5

4)设置设置设备摆设摆设ODBC Driver

在~/etc目录下创建odbc.ini如下:

[myodbc5]

Driver = /home/oracle/app/myodbc-5.2.5/lib/libmyodbc5w.so

Description = Connector/ODBC 5.2 Driver DSN

SERVER = 192.168.1.15

PORT = 3306

USER = mysql_user

PASSWORD = mysql_pwd

DATABASE = mysql_db

OPTION = 0

TRACE = OFF

此中,Driver指向第3步上按照的ODBC Driver,这里要分外留意:MySQL的Datbase是大年夜小写敏感的。

5)验证ODBC连接

$ export ODBCINI=/home/oracle/etc/odbc.ini

$ export LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:$LD_LIBRARY_PATH

$ cd ~/app/unixodbc-2.2.14/usr/local/bin

$ ./isql myodbc5 -v

+---------------------------------------+

| Connected!|

||

| sql-statement|

| help [tablename]|

| quit|

||

+---------------------------------------+

上面显示连接成功。

6)设置设置设备摆设摆设tnsnames.ora

myodbc5 =

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP) (HOST=localhost) (PORT=1521)

)

(CONNECT_DATA=

(SID=myodbc5)

)

(HS=OK)

)

7)设置设置设备摆设摆设listener.ora

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=myodbc5)

(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM=dg4odbc)

(ENVS=LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib)

)

)

如上所示,为了避免和其它已存在的ODBC Driver Manager冲突,强烈设置LD_LIBRARY_PATH在listener.o澳门新葡亰app在线下载ra

8)创建init.ora文件

创建文件$ORACLE_HOME/hs/admin/initmyodbc5.ora,内容如下:

HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini

HS_FDS_SHAREABLE_NAME=/home/oracle/app/unixodbc-2.2.14/usr/local/lib/libodbc.so

HS_FDS_SUPPORT_STATISTICS=FALSE

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

# ODBC env variables

set ODBCINI=/home/oracle/etc/odbc.ini

9)使上述设置设置设备摆设摆设文件生效

$ lsnrctl reload

$ lsnrctl status

Service "myodbc5" has 1 instance(s).

Instance "myodbc5", status UNKNOWN, 澳门新葡亰app在线下载has 1 handler(s) for this service...

10)验证设置设置设备摆设摆设是否精确

$ tnsping myodbc5

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-AUG-2013 10:54:46

Copyright (c) 1997, 2009, Oracle.All rights reserved.

Used parameter files:

/home/oracle/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=localhost) (PORT=1521))澳门新葡亰app在线下载 (CONNECT_DATA= (SID=myodbc5)) (HS=OK))

OK (0 msec)

11)创建dblink

SQL> create public database link mysqltest connect to "mysql_user" identified by "mysql_pwd" using 'myodbc5';

SQL> select count(*) from trans_expert_map@mysqltest;

COUNT(*)

----------

371

您可能还会对下面的文章感兴趣: