oracle通过DBlink连接mysql(MariaDB)

1.安装先装 mysql-connector-odbc（或 mariadb-connector-odbc ）和unixODBC
https://downloads.mariadb.org/mariadb/repositories/#mirror=opencas&distro=CentOS&distro_release=centos7-amd64--centos7&version=10.0
https://downloads.mariadb.org/connector-odbc/2.0.10/#mirror=biznet

MariaDB [(none)]> show engines;
MariaDB [(none)]> install plugin connect soname 'ha_connect.so';
yum install unixODBC unixODBC-devel

　　

2.配置 /etc/odbc.ini
[myodbc3]
Driver = /usr/lib64/libmyodbc5.so
Description = MySQL ODBC 5.1 Driver DSN
SERVER = 192.1.1.200
PORT = 3306
USER = ods_user
Password = 123456
Database = mysql
OPTION = 3
SOCKET =
charset = utf8

3.配置/etc/odbcinst.ini
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc5.so
Setup = /usr/lib64/libodbcmyS.so
FileUsage = 1

4.测试连接
[root@rac1 ~]# isql myodbc3 -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>

5.配置oracle环境变量
export ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/grid
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=rac1
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH:/usr/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/hs/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
ODBCINSTINI=/etc/odbc.ini
export ODBCINSTINI

6.配置监听
listener.ora:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))
)
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(PROGRAM = dg4odbc)
(SID_NAME= myodbc3)
(ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0/db_1/hs/lib:/usr/lib64)
)
)

tnsname.ora:

myodbc3=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))
(CONNECT_DATA =
(SID = myodbc3))
(HS = OK)
)

7.配置odbc监听
路径：$ORACLE_HOME/hs/admin
注意：名字要跟odbc配置的名字一样 我这里是myodbc3

cat initmyodbc3.ora
HS_FDS_CONNECT_INFO = myodbc3
HS_FDS_TRACE_LEVEL = ON
HS_FDS_TRACE_FILE_NAME = odbc_test.log
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISCTICS = FALSE
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SQLLEN_INTERPRETATION=32
set ODBCINI = /etc/odbc.ini

8.测试下监听
tnsping myodbc3

9.创建dblink
SQL>create public database link myodbc connect to "ods_user" identified by "123456" using'myodbc3';
SQL> select count(*) from "t_user"@myodbc;

 

参考资料：

http://www.tuicool.com/articles/V3qYrme

http://blog.itpub.net/28321441/viewspace-750732/

http://www.server110.com/mariadb/201404/10139.html