一、概述
linux环境下,Oracle数据库通过DBLink远程连接MySQL数据库。
二、环境信息
1、Oracle
①操作系统:Linux X86-64
②数据库版本:11.2.0.4.0
③字符集:SIMPLIFIED CHINESE_CHINA.AL32UTF8
2、MySQL
①操作系统:Linux i686
②数据库版本:8.0.16+
③字符集:UTF8
三、配置
实现 oracle to mysql dblink 涉及以下概念:DG4ODBC, ODBC, unixODBC,Mysql,ODBCINI,DBLINK
1、安装unixODBC
1)判断32位还是64位
因为32位和64位的配置不一样,64位更复杂一些,因此我们首先得确定Oracle和DG4ODBC是32位还是64位:
$ file $ORACLE_HOME/bin/dg4odbc
/mysql/data/u01/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped
根据以上输出判断是64位,需要下载对应的64位 ODBC Driver Manager 和 64位的 ODBC Driver
2)安装unixODBC
到 (https://www.unixodbc.org/) 下载最新的
unixodbc,当前最新版是 unixODBC-2.3.12.tar.gz
,本次下载了2.3.7的版本。其中,unixODBC-2.3.7.tar.gz
wget https://www.unixodbc.org/unixODBC-2.3.12.tar.gz
tar -zxvf unixODBC-2.3.12.tar.gz
cd unixODBC-2.3.12
./configure --prefix=/usr/local/unixODBC-2.3.12 --includedir=/usr/include --libdir=/usr/lib64 -bindir=/usr/bin --sysconfdir=/etc
make && make install
3) 测试
执行命令:odbcinst -j
,如果安装成功会显示:
[root@vbox66 local]# odbcinst -j
unixODBC 2.3.12
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
2、mysql odbc驱动安装
推荐用rpm包直接安装
2.1、rpm包安装
wget https://cdn.mysql.com//Downloads/Connector-ODBC/8.1/mysql-connector-odbc-8.1.0-1.el9.aarch64.rpm
rpm -ivh mysql-connector-odbc-8.0.33-1.el7.x86_64.rpm
2.2、源码安装
#官网下载:
https://cdn.mysql.com//Downloads/Connector-ODBC/8.1/mysql-connector-odbc-8.1.0-linux-glibc2.28-x86-64bit.tar.gz
#解压
tar -zxvf mysql-connector-odbc-8.1.0-linux-glibc2.28-x86-64bit.tar.gz
#重命名
mv mysql-connector-odbc-8.1.0-linux-glibc2.28-x86-64bit mysql-odbc-8.1.0
#创建软连接
ln -s /opt/mysql-odbc-8.1.0/lib/libmyodbc8a.so /usr/lib64/libmyodbc8a.so
ln -s /opt/mysql-odbc-8.1.0/lib/libmyodbc8w.so /usr/lib64/libmyodbc8w.so
3、配置 ODBC Driver
ODBC.INI是ODBC配置文件,记录了连接到服务器所需的驱动信息和数据库信息。Driver Manager将使用它来确定加载哪个驱动程序(使用数据源名DSN)。驱动程序将根据指定的DSN来读取连接参数。
在/etc/odbc.ini文件中定义DSN(数据源名称,如mysql_link),配置数据源信息(驱动、IP、端口、用户名、口令、库名等)
vi /etc/odbc.ini
# 注意名称 myodbc8
[myodbc8]
Description = ODBC for MySQL
#注意修改路径
#Driver = MySQL ODBC 8.0 Unicode Driver
或者下面的
Driver = /usr/lib64/libmyodbc8a.so
# MYSQL 地址
Server = 172.28.102.78
# MYSQL 端口
Port = 3306
# MYSQL 用户
User = mysql_user
# MYSQL 密码
Password = mysql_pwd
# MYSQL 数据库
Database = mysql_db
CHARSET = utf8mb4
测试是否配置成功,如果命令无法执行,可以先试着添加环境变量,注意修改实际路径
修改.bash_profile,增加以下内容
export LD_LIBRARY_PATH=/usr/local/unixODBC-2.3.12/lib
export PATH=/usr/sbin:/usr/local/unixODBC-2.3.12/bin:$PATH
export ODBCINI=/etc/odbc.ini
用isql测试mysql odbc是否配置成功,isql是unixODBC带的一个ODBC客户端访问工具,可使用isql +数据源名来访问目标数据库
[root@hker etc]# isql myodbc8
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_skke_dev |
+-----------------------------------------------------------------+
| d_bom |
| d_bom_detail |
4、配置ORACLE相关网关监听等
在"ORACLE_HOME\hs\admin"目录下,默认存在名为"initdg4odbc.ora"的文件,每个使用DG4ODBC的实例,都必须对应一个"init*.ora"文件,文件命名规则:init+<网关sid>+.ora,文件主要参数:
HS_FDS_CONNECT_INFO:ODBC数据源管理中的DSN名称
HS_FDS_TRACE_LEVEL:追踪级别参数,出于性能影响一般不配置或者配置为"OFF",若遇到网关问题需要跟踪日志,则配置为"Debug"
4.1、hs透明网关配置 initmyodbc8.ora
以下配置路径在 "ORACLE_HOME/hs/admin
"目录下
$cd $ORACLE_HOME/hs/admin/
$vim initmyodbc8.ora
HS_FDS_CONNECT_INFO = myodbc8
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_DATE_FORMAT=YYYY-MM-DD
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR = UCS2
HS_FDS_SUPPORT_STATISTICS = FALSE
set ODBCINI=/etc/odbc.ini
##set LD_LIBRARY_PATH=/usr/lib64/unixODBC-2.2.14/lib
4.2、配置listener.ora
以下配置路径在 "ORACLE_HOME/network/admin
"目录下
在listener.ora中增加如下配置,sid_name要与上面hs网关配置的网关sid一致,红色部分是新增加。可以新建个单独的监听,也可以在原有的监听中加入(建议单独建立监听)
TEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.xx)(PORT = 1521))
)
)
SID_LIST_TEST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME= /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
#####以下为新增, 注意调整路径和sid######
(SID_DESC =
(ORACLE_HOME= /home/oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = myodbc8)
(PROGRAM=dg4odbc)
)
######以上为新增########
)
4.3、配置tnsnames.ora
以下配置路径在 "ORACLE_HOME/network/admin
"目录下
ADDRESS:填写透明网关的IP地址和端口
SID:指定连接网关的SID
HS:指定连接的是非ORACLE数据库
vi tnsname.ora (注意:HS=OK,在CONNECT 右括号后面)
新增如下配置
myodbc8 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.xx.xx)(PORT = 1521)
)
(CONNECT_DATA =
(SID = myodbc8)
)
(HS = OK)
)
4.4、使上述配置文件生效
$ lsnrctl reload
# 或者
$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-AUG-2017 14:18:57
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbmsprddb222)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 24-AUG-2017 08:56:23
Uptime 0 days 5 hr. 22 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "myodbc8" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4.5、验证
$tnsping myodbc8
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 24-AUG-2017 14:29:43
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xx.xx.)(PORT = 1521)) (CONNECT_DATA = (SID = myodbc8)) (HS = OK))
OK (0 msec)
4.6、创建dblink
注意:用户名和密码一定的加””,dblink 名字 为 dlk
### create public database link dlk connect to "mysql_user" identified by "mysql_pwd" using 'myodbc8'; ###
SQL> create public database link dlk connect to "mysql_user" identified by "mysql_pwd" using 'myodbc8';
#验证
SQL> select count(*) from trans_expert_map@mysqltest;
COUNT(*)
----------
371