OracleForMysql透明网关实施
目录
环境：
Oracle：11.2.0.4
Mysql：5.6.36
软件包：
p13390677_112040_Linux-x86-64_5of7.zip #Oracle_gateway安装包
mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm #mysql odbc插件安装包
一、ORACLE透明网关安装/检查
1、如果透明网关装在Oracle服务器上，则直接使用dg4odbc命令即可得到以下返回：
[oracle@test ~]$ dg4odbc
Oracle Corporation --- THURSDAY APR 25 2024 09:51:31.820
Heterogeneous Agent Release 11.2.0.4.0 - 64bit
Production Built with
Oracle
Database Gateway for ODBC
2、如果透明网关装在mysql端或第三台无Oracle软件的机器时，则需要单独安装透明网关：
涉及安装包：
p13390677_112040_Linux-x86-64_5of7.zip
安装步骤：
A、 依赖安装：
yum -y install binutils-*
yum -y install compat-libstdc++-*
yum -y install elfutils-libelf-*
yum -y install elfutils-libelf-devel-*
yum -y install elfutils-libelf-devel-static-*
yum -y install gcc-*
yum -y install gcc-c++-*
yum -y install glibc-*
yum -y install glibc-common-*
yum -y install glibc-devel-*
yum -y install glibc-headers-*
yum -y install kernel-headers-*
yum -y install ksh-*
yum -y install libaio-*
yum -y install libaio-devel-*
yum -y install libgcc-*
yum -y install libgomp-*
yum -y install libstdc++-*
yum -y install libstdc++-devel-*
yum -y install make-*
yum -y install sysstat-*
yum -y install unixODBC-*
yum -y install unixODBC-devel-*
B、 用户创建及环境变量设置
groupadd -g 1002 oinstall
groupadd -g 1003 dba
useradd -u 1004 -g oinstall -G dba oracle
echo oracle | passwd --stdin oracle
======================================================
echo "
export ORACLE_BASE=/u01/app/oracle
export
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH >>
/home/oracle/.bash_profile
======================================================
chown -R oracle.oinstall /home/oracle/.bash_profile
C、 Oracle用户解压并运行runInstall
D、 图形化界面安装
二、mysql-connector安装
下载地址：
https://dev.mysql.com/downloads/connector/odbc/
1、 依赖包安装：
yum install unixODBC*
[root@test ~]# rpm -qa|grep unixODBC*
unixODBC-devel-2.2.14-14.el6.x86_64
unixODBC-2.2.14-14.el6.x86_64
2、 mysql-connector安装
三、odbc配置
1、记录driver文件路径
2、查看odbc.ini路径
[root@test ~]# odbcinst -j
3、编辑odbc.ini文件
[root@test ~]# cat /etc/odbc.ini
[mysql_test] #连接名（透明网关配置文件、监听文件等需要用到）
Description
= ODBC for MySQL #描述信息
Driver
= /usr/lib64/libmyodbc8w.so #odbcinst文件中记录的driver文件路径
Server
= 10.10.20.129 #mysql服务器ip
Port
= 3306 #mysql服务端口
User
= dbtest #mysql远程连接用户名
Password
= abcd1234 #mysql远程连接用户密码
Database
= test #远程连接的库名
4、odbc连接测试
[root@test ~]# isql mysql_test
四、透明网关（dg4odbc）配置
1、创建配置文件并修改具体内容
[root@test ~]# su - oracle
[oracle@test ~]$ cd $ORACLE_HOME/hs/admin
[oracle@test admin]$ cp initdg4odbc.ora
initmysql_test.ora
[oracle@test admin]$ vim initmysql_test.ora
# This is a sample agent init file that contains
the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mysql_test #odbc.ini中的连接名
HS_FDS_TRACE_LEVEL = off #日志选项（on、off、debug）
HS_FDS_SHAREABLE_NAME =
/usr/lib64/libodbc.so #odbc的deriver文件
HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252 #oracle数据库的字符集
HS_NLS_NCHAR = UCS2 #dblink处理访问时乱码问题
set ODBCINI=/etc/odbc.ini #odbc.ini文件的路径
#
# ODBC specific environment variables
#
五、透明网关listener监听配置
1、创建新的静态监听
[oracle@test admin]$ cat
$ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File:
/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME
= orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
(SID_DESC=
(SID_NAME = mysql_test)
(ORACLE_HOME =
/u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
2、重新加载监听文件
[oracle@test admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production
on 25-APR-2024 11:38:18
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
The command completed successfully
3、检查监听
[oracle@test admin]$ lsnrctl status
六、ORACLE生产端配置
1、tnsnames文件配置
orcl_mysql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.20.128)(PORT = 1521))
)
(CONNECT_DATA =
(SID =
mysql_test)
)
(HS = OK)
)
2、测试tns连接
3、使用tns创建dblink
create
PUBLIC DATABASE LINK dlk connect to “dbtest” identified by “abcd1234”
using 'orcl_mysql';
4、使用连接串创建dblink
create public database link mysql_emr connect to "read" identified by "Read@2020" using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.68.10.16 )(PORT = 1521)))(CONNECT_DATA =(SID = mysql_emr))(HS = OK))’;
5、测试dblink
