oracle 连接mysql数据库

jiawang

Primary Note for Oracle Gateway Products (Doc ID 1083703.1)
Document 1351618.1 Installation Overview For Oracle Database Gateway Products


https://docs.oracle.com/en/


Click on-


‘Database’ option
All Oracle Database documentation
choose the version you require from the dropdown list
then ‘Integrating Information’
Gateway documentation is under the ‘Heterogeneous Connectivity’ heading
Oracle使用ODBC访问mysql


[说明]


Oracle数据库网关可以透明地访问其他数据库，Oracle HS Agent将SQL语句转换为非Oracle数据库（mysql）可以理解的SQL语句，
并通过ODBC 数据源发送该SQL语句，在mysql中执行后将结果返回Oracle数据库，本文介绍如何配置并访问mysql数据库。


[测试环境]


Oracle 服务器
操作系统：Red Hat Enterprise Linux Server release 7.4 (Maipo)
软件版本：Oracle 11.2.0.4
主机地址：192.168.56.101
监听端口：1521


mysql 服务器
操作系统：Red Hat Enterprise Linux Server release 7.4 (Maipo)
软件版本：8.0.26-commercial MySQL Enterprise Server
主机地址：192.168.56.101
服务端口：3306
数据库名：t
用户密码：root/123456


[配置]
1 mysql 服务器
安装并创建相关数据库，使用远程测试，确保远程连接正常。


2 Oracle 服务器
2.1 安装相关包
安装 unixODBC 软件包
yum -y install unixODBC*
安装 mysql ODBC 驱动
yum install mysql-connector* --测试发现系统自带的版本较低触发了相关问题，无法解决，升级改包后问题解决
报错信息++++++++++
select * from “t1”@msqlk
*
ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.101)(PORT
=1521)))(CONNECT_DATA=(SID=mysql_test)))
ORA-02063: preceding line from MSQLK
Process ID: 9691
Session ID: 29 Serial number: 165
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm
下载新的驱动包
yum localinstall mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm
安装改应用包


2.2创建数据源
为驱动创建数据源
odbcinst -j
odbc_config --odbcini --odbcinstini


2.3配置 mysql ODBC驱动库
/etc/odbcinst.ini
默认已经创建了[mysql]条目。


[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1
Trace=yes
TraceFile=/usr/etc/sql01.log


[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib64/libmyodbc8w.so
#Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc8w.so
#Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1


[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib64/libmyodbc8w.so
UsageCount=1


[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib64/libmyodbc8a.so
UsageCount=1


2.4创建数据源
数据源名称为 mysql_test，可自定义其他名称，注意在后继配置中引用时保持一致。
mysql数据库名为 t, 服务器/端口为 192.168.56.101/3306, 用户名/密码为 root/123456
/etc/odbc.ini


[pgdsn]
Driver = PostgreSQL
Description = PostgreSQL ODBC Driver
Database = runoobdb
Servername = 192.168.56.101
Username = testuser
Password = 123456
Port = 5432
UseDeclareFetch = 1
CommLog = /tmp/pgodbclink.log
Debug = 1
LowerCaseIdentifier = 1


[mysql_test]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc8w.so
Server = 192.168.56.101
Port = 3306
User = root
Password = 123456
Database = t


2.5 测试 ODBC 连接
[oracle@rhel7 admin]$ isql mysql_test
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL> select * from t1;
±----------+
| id |
±----------+
| 30 |
| 30 |
±----------+
SQLRowCount returns 2
2 rows fetched
SQL>


2.6 创建 Oracle 数据库网关
在 ORACLE_HOME/hs/admin 目录下创建 init<数据源名>.ora 文件，本测试为 initmysql_test.ora。 [oracle@rhel7 admin] cat initmysql_test.ora
HS_FDS_CONNECT_INFO = mysql_test
#HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#HS_FDS_SUPPORT_STATISTICS=FALSE
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1


##ODBC Configuration
set ODBCINI=/etc/odbc.ini


trace=0


2.7 调整 Oracle 监听配置


注意集群环境的监听文件位置为$ORACLE_HOME/network/admin/listener.ora ，rac环境为grid 下面的文件
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME = pgdsn)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(ENVS=“LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/db_1”)
(PROGRAM=dg4odbc)
)
(SID_DESC=
(SID_NAME = mysql_test)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(ENVS=“LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/db_1”)
(PROGRAM=dg4odbc)
)
)


2.8 添加 Oracle TNS 配置
配置文件为 $ORACLE_HOME/network/admin/tnsnames.ora


db_mysql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)))
(CONNECT_DATA =(SID = mysql_test))
(HS = OK)
)


2.9 重启监听
lsnrctl stop
lsnrctl start


2.10 创建 Database Link
SQL> create PUBLIC DATABASE LINK msqlk connect to “root” identified by “123456” using ‘db_mysql’;


2.11 访问测试
注意表名需要添加双引号。
SQL> select * from “t1”@msqlk;


no rows selected


SQL> insert into “t1”@msqlk values(30);


1 row created.


SQL> select * from “t1”@msqlk;