不说废话！
纯干货！
吐血分享！
How to create DBLink from Oracle to Mysql?
Ready？
Go！
查看Oracle和DG4ODBC是32位还是64位
$ file $ORACLE_HOME/bin/dg4odbc
安装ODBC Driver Manager
下载路径www.unixodbc.org
将下载文件传到Oracle服务器上，解压安装
$ cd soft
$ gunzip -c unixODBC-2.3.6.tar.gz | tar -xvf
root用户执行安装，安装目录/usr/local/unixODBC
# cd usr/local
# mkdir unixODBC
# cd soft/unixODBC-2.3.6
# ./configure --prefix=/usr/local/unixODBC
# make
# make install
# chown -R oracle:oinstall usr/local/unixODBC安装ODBC Driver
下载路径http://dev.mysql.com/downloads/connector/odbc/#downloads
$ cd soft
$ gunzip -c mysql-connector-odbc-8.0.11-linux-glibc2.12-x86-64bit.tar.gz
root用户执行
# cd soft/mysql-connector-odbc-8.0.11-linux-glibc2.12-x86-64bit
# cp bin/* usr/local/bin
# cp lib/* usr/local/lib
# myodbc-installer -a -d -n "MySQL ODBC 8.0 Driver" -t "Driver=/usr/local/lib/libmyodbc8w.so"
# myodbc-installer -a -d -n "MySQL ODBC 8.0" -t "Driver=/usr/local/lib/libmyodbc8a.so"
确认是否正确安装
# myodbc-installer -d -l
MySQL ODBC 8.0 Driver
MySQL ODBC 8.0配置ODBC数据源
oracle用户执行
$ cd usr/local/unixODBC/etc
$ vi odbc.ini
[test]
Driver = usr/local/lib/libmyodbc8a.so
Description = Connector/ODBC 8.0 ANSI Driver DSN
SERVER = 192.168.0.2
PORT = 3306
USER = test
PASSWORD = *****
DATABASE = test
OPTION = 0
TRACE = OFF
测试ODBC连接是否正确，oracle用户执行
$ export ODBCINI=/usr/local/unixODBC/etc/odbc.ini
$ export LD_LIBRARY_PATH=/usr/local/unixODBC/lib:$LD_LIBRARY_PATH
$ isql test -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
以上返回结果表明连接正常
在listener.ora和tnsnames.ora中增加mysql库连接信息
vi $ORACLE_HOME/network/admin/listener.ora
如果为RAC数据库，listener位置在grid下
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=test)
(ORACLE_HOME=/oracle/app/12.2.0)
(PROGRAM=dg4odbc) (ENV="LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/oradata/app/12.2.0/lib:/lib:/usr/lib")
)
)
重启listener，完成后会看到test已在listener中注册。
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
vi $ORACLE_HOME/network/admin/tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1526))
)
(CONNECT_DATA =
(SID = test)
)
(HS=OK)
)
备注：
HOST为Oracle数据库的HOST
PORT为Oracle数据库的PORT
SID为/usr/local/unixODBC/etc/odbc.ini文件中定义的名称
(HS=OK)必须加上
测试tns是否能通
$ tnsping test
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1526))) (CONNECT_DATA = (SID = test)) (HS=OK))
OK (0 msec)配置gateway inittemptest.ora文件
$ vi $ORACLE_HOME/hs/admin/inittest.ora
HS_FDS_CONNECT_INFO=test
# Data source name in odbc.ini
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_SHAREABLE_NAME=/usr/local/unixODBC/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
#
# ODBC env variables
set ODBCINI=/usr/local/unixODBC/etc/odbc.ini创建dblink
SQL> create database link oracletomysql connect to "test" identified by "******" using 'test';
SQL> select count(*) from "a"@oracletomysql;
COUNT(*)
----------
1DBlink终于建完了，想想从Oracle到Oracle的步骤，是多么简单。
