Oracle通过dblink连接mysql数据库实时数据同步

1.检查oracle数据库所在的主机上是否已经装了mysql odbc客户端

rpm -qa |grep mysql-connect

2.如果没有则下载mysql-connector-odbc

wget https://cdn.mysql.com//Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.7-1.el7.x86_64.rpm

3.安装mysql-connector-odbc

rpm -ivh mysql-connector-odbc-5.3.7-1.el7.x86_64.rpm

#4.若出现libltdl.so is neeed by mysql-connector-odbc错误，则需要安装下依赖libtool

#yum list *.i386|grep libtool

#yum install libtool-ltdl.i386

#

# 若出现libc.so依赖

# 则需要安装glibc

######查看系统支持的版本

# strings /lib64/libc.so.6 |grep GLIBC_

######

#对于xz格式的解压缩

# tar.xz解压

# xz -d ***.tar.xz 之后 tar xvf ***.tar

# tar.xz压缩

# tar cvf ***.tar good/ 之后 xz -z ***.tar

# wget http://mirrors.ustc.edu.cn/gnu/libc/glibc-2.14.tar.gz

# tar zxvf glibc-2.14.tar.gz

# cd glibc-2.14

# mkdir build

# cd build

# ../configure --prefix=/usr/local/glibc-2.14

# make -j4

# make install

#

5.编辑oracle所在的计算机上的odbc.ini文件

vi /etc/odbc.ini

在其中输入以下内容:

[test]

#对应mysql-connector-odbc安装下的so文件

Driver=/usr/lib64/libmyodbc3.so

Description=MySQL

#对应的mysql连接ip

Server=192.168.1.1(MySQL Server IP)

#对应mysql的端口号

Port=3306

#对应的mysql用户名

User= (MySQL Username)

UID= (MySQL Username）

#对应的mysql密码

Password= (MySQL PWD)

#对应的mysql数据库名

Database= (MySQL Database Name)

Option=3

Socket=

##############mysql-connector文档参考配置################

#####

[myodbc3]

Driver = /usr/local/lib/libmyodbc3.so

Description = Connector/ODBC 3.51 Driver DSN

SERVER = localhost

PORT =

USER = root

Password =

Database = test

OPTION = 3

SOCKET =

[Default]

Driver = /usr/local/lib/libmyodbc3.so

Description = Connector/ODBC 3.51 Driver DSN

SERVER = localhost

PORT =

USER = root

Password =

Database = test

OPTION = 3

SOCKET =

##########################################################

使用下列命令进行测试，如果可以进入Mysql Client，则证明odbc功能配置正常了。

[root@iZbp1hzuom8ghisagm03qfZ zip]# odbcinst -j

unixODBC 2.2.14

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

isql -v test

#6.编辑hs配置文件 vi /ora10g/hs/admin/inittest.ora(注意文件名中蓝色部分为odbc.ini中蓝色名称)

#HS_FDS_CONNECT_INFO = test

#HS_FDS_TRACE_LEVEL = on（正式使用后，不需要排错的时候应设为off）

#HS_FDS_TRACE_FILE_NAME = test.trc

#HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so

#set ODBCINI=/etc/odbc.ini

7.编辑oracle所在计算机上的oracle listener配置文件

vi /oracle/network/admin/listener.ora

加入如下语句:

(SID_DESC =

(SID_NAME = test)

(ORACLE_HOME = /ora10g)

(PROGRAM = hsodbc)

(ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib)

)

listener.ora文件现在的内容变成：

SID_LIST_LISTENER = (

SID_LIST =

(SID_DESC =

(ORACLE_HOME = /ora10g)

(PROGRAM = extproc)

(GLOBAL_DBNAME=prod)

(SID_NAME=prod)

)

(SID_DESC =

(SID_NAME = test)

(ORACLE_HOME = /ora10g)

(PROGRAM = hsodbc)

(ENVS=LD_LIBRARY_PATH=/ora10g/lib32:/usr/lib64:/ora10g/lib)

)

)

LISTENER = (

DESCRIPTION_LIST =

(DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) )

)

执行lsnrctl reload使配置生效.

su – oracle

lsnrctl reload

8.编辑oracle所在计算机上的tnsnames.ora文件

vi /ora10g/network/admin/tnsnames.ora

test =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SID = test)

)

(HS = OK)

)

9.在oracle database建立dblink

create public database link MYSQL

connect to "MYSQL Username" identified by "MySQL PWD"

using '(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT =1521) )

(CONNECT_DATA = (SID= test) )

(HS=OK)

)';

这里的“MySQL username”为mysql用户名，“MySQL PWD”为mysql密码

9.查询mysql中的表名

select * from "your tableName"@test

注意:

your tableName 为mysql表名

test 为oracle配置的SID

1、oracle 10g 64位的hsodbc应该有问题，文件大小为0，执行hsodbc没有任何信息；

解决：拷贝32机器下的hsodbc到$ORACLE_HOME/bin目录下，替换原hsodbc，注意oracle要有权限，下载：http://space.itpub.net/?uid-28321441-action-viewspace-itemid-750733

2、rac监听不能监听到hsodbc服务；

解决：netca选择single node configuration创建监听来监听hsodbc服务，监听端口选择1522；

3、连接mysql查询数据乱码。

解决：编辑/etc/odbc.ini，增加

charset =gbk

STMT =SET NAMES 'GBK'

其中gbk是MYSQL字符集。

具体实施步骤如下：

--oracle服务器：

-- 操作系统：Red Hat Enterprise Linux 5.3

-- 数据库： Oracle RDBMS 10.2.0.3.0

--mysql服务器：

-- 操作系统：windows server 2003

-- 数据库： MySQL 5.5

单实例操作如下，操作都在oracle服务器上完成：

--检查需要的rpm包，需要的包如下，由于hsodbc应该是只支持32位，所以32位包是必须的：

libtool-ltdl-1.5.22-6.1.i386.rpm

libtool-ltdl-1.5.22-6.1.x86_64.rpm

mysql-5.0.77-3.el5.i386.rpm

mysql-5.0.77-3.el5.x86_64.rpm

mysql-connector-odbc-3.51.26r1127-1.el5.i386.rpm

mysql-connector-odbc-3.51.26r1127-1.el5.x86_64.rpm

perl-DBI-1.52-2.el5.x86_64.rpm

unixODBC-2.2.11-7.1.i386.rpm

unixODBC-2.2.11-7.1.x86_64.rpm

上面unixODBC-2.2.11-7.1、mysql-5.0.77-3.el5、mysql-connector-odbc是需要的包，其他是安装这些包的前提。

安装完后检查

# rpm -qa |grep unixODBC

unixODBC-2.2.11-7.1

unixODBC-2.2.11-7.1

# rpm -qa |grep mysql

mysql-5.0.45-7.el5

mysql-5.0.45-7.el5

mysql-connector-odbc-3.51.26r1127-1.el5

mysql-connector-odbc-3.51.26r1127-1.el5

--编辑/etc/odbc.ini

vi /etc/odbc.ini

#[ODBC Data Sources]

#myodbc3 = MySQL ODBC 3.51 Driver DSN

[test]

Driver = /usr/lib/libmyodbc3.so

Description = MySQL ODBC 3.51 Driver DSN

SERVER = 24.1.20.149

PORT = 3306

USER = root

Password = laopo

Database = teng_push

OPTION = 3

SOCKET =

charset =gbk

STMT =SET NAMES 'GBK'

--最后两个是解决中文乱码问题，gbk是MYSQL字符集

--切换到oracle用户

# su - oracle

--编辑环境变量配置文件，主要是LD_LIBRARY_PATH和最后面两项

$ vi ~/.bash_profile

export ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=orcl

export ORACLE_TERM=xterm

export PATH=/usr/sbin:$PATH

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

#export LANG=zh_CN.GB18030

export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/bin:/usr/bin:/usr/sbin

:/usr/local/bin:/usr/X11R6/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

#ulimit -u 16384 -n 65536

umask 022

export EDITOR=vi

ODBCINI=/etc/odbc.ini; export ODBCINI

ODBCSYSINI=/etc; export ODBCSYSINI

--使配置生效

source ~/.bash_profile

--查看odbc版本及参数文件路径

$odbcinst -j

unixODBC 2.2.11

DRIVERS............: /usr/local/etc/odbcinst.ini

SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini

USER DATA SOURCES..: /usr/local/etc/odbc.ini

--测试 My SQL ODBC 驱动

$isql test -v(测试前需要将/etc/odbc.ini中的Driver= /usr/lib/libmyodbc3.so 修改成/usr/lib64/libmyodbc3.so，测试完后还原)

isql test oracle manager -v

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

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

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

SQL> quit

--配置 HSODBC 程序

vi $ORACLE_HOME/hs/admin/inittest.ora

HS_FDS_CONNECT_INFO = test

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so

set DBCINI=/etc/odbc.ini

--确认 hsodbc 的配置是否正确，如果正确可以看到版本号

--注意：如果是64位oracle,由于hsodbc应该是只支持32位，这里需要拷贝32位机器上的hsodbc到$ORACLE_HOME/bin下替换原有的hsodbc

$ hsodbc

Oracle Corporation --- FRIDAY DEC 06 2012 12:20:44.240

Heterogeneous Agent Release 10.2.0.3.0 - Production Built with

Driver for ODBC

--修改监听文件，增加下面服务：

(SID_DESC =

(PROGRAM = hsodbc)

(ORACLE_HOME = /oracle/product/10.2.0/db_1)

(SID_NAME = test)

(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle

/product/10.2.0/db_1/lib)

)

$ vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(PROGRAM = hsodbc)

(ORACLE_HOME = /oracle/product/10.2.0/db_1)

(SID_NAME = test)

(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle

/product/10.2.0/db_1/lib)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

--修改tnsnames.ora

$ vi $ORACLE_HOME/network/admin/tnsnames.ora

test =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

)

(CONNECT_DATA =

(SID= test)

)

(HS=OK)

)

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

--重启监听，要有test服务

$ lsnrctl stop

$ lsnrctl start

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "orcl" has 1 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

Service "test" has 1 instance(s).

Instance "test", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

--tnsping测试服务

$ tnsping test

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)(HOST = 24.1.20.30)(PORT = 1521))) (CONNECT_DATA = (SID= test)) (HS=OK))

OK (0 msec)

--创建dblink

CREATE PUBLIC DATABASE LINK mysql CONNECT TO "root" IDENTIFIED BY "laopo" USING 'test';

--测试

select * from "emp"@mysql where "id"=1;

至此，单节点配置完成。

rac环境下每个节点都需要配置，和单实例唯一的区别是监听的配置，我试着多种方式去配置监听，但都没有监听到hsodbc的test服务，最后通过netca选择single node configuration创建监听来监听test服务才成功，注意监听端口改成1522

rac环境节点1的监听文件如下：

$vi $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER_RAC01 =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)

(PROGRAM = extproc)

)

)

LISTENER_RAC01 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1521)(IP = FIRST))

)

)

SID_LIST_LISTENER1 =

(SID_LIST =

(SID_DESC =

(PROGRAM=hsodbc)

(ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1)

(SID_NAME=test)

(ENVS=LD_LIBRARY_PATH=/opt/ora10g/product/10.2.0/db_1/lib32:/lib:/usr/lib:

/opt/ora10g/product/10.2.0/db_1/lib)

)

)

LISTENER1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1522))

)

)