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;

id

----------

30