记录配置Oracle通过透明网关访问MySQL配置

一、Oracle数据库环境说明

Centos 7x64   

Oracle 12c 安装时自动安装上透明网关dg4odbc

主机名 orcl

二、Mysql数据库环境说明

Centos 7x64   

MySQL 社区版主机名 5.7

IP地址：10.xx.xx.227

二、数据访问流程

oracle——dg4odbc——odbc——mysql

三、安装MySQL连接器

到mysql下载

https://dev.mysql.com/downloads/connector/odbc/

四、安装ODBC连接器

默认已安装unixODBC,安装MySQL连接器即可。

[root@orcl ~]# rpm -qa|grep unixODBC

unixODBC-devel-2.3.1-14.el7.x86_64

unixODBC-2.3.1-14.el7.x86_64

[root@orcl database]# ll

总用量 4285180

drwxr-xr-x. 7 root root        117 1月  27 2017 database

-rwxrwxrwx. 1 root root    2034035 5月  22 20:44 mysql-connector-odbc-8.0.33-10.fc38.x86_64.rpm

-rw-r--r--. 1 root root 3453696911 5月  22 16:49 V839960-01.zip

-rw-r--r--. 1 root root  932290415 5月  22 16:48 V840026-01.zip

[root@orcl database]#  

五、ODBC配置

/etc/odbcinst.ini

# Example driver definitions

# Driver from the postgresql-odbc package

# Setup from the unixODBC package

[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

# Driver from the mysql-connector-odbc package

# Setup from the unixODBC package

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc5.so

Setup = /usr/lib64/libodbcmyS.so

FileUsage = 1

/etc/odbc.ini

[ssm]
Description = MySQL ODBC Driver
Driver = MySQL
Server = 10.xx.xx.227
Port = 3306
Database = ssm
User = root
Password = 123456                    

执行命令测试配置是否成功

[root@orcl database]# isql -v ssm
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

以上测试结果显示，证明ODBC配置正确 。

六、Oracle数据库相关配置

在此目录下建立 /$ORACLE_HOME/hs/admin/initssm.ora文件

# 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 = ssm
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HD_FDS_SUPPORT_STATISTICS = FALSE
HS_NLS_NCHAR = UCS2

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

监听器配置

# listener.ora Network Configuration File: /u1/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=ssm)
      (ORACLE_HOME=/u1/app/oracle/product/12.2.0/dbhome_1)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/usr/lib64:/u1/app/oracle/product/12.2.0/dbhome_1/lib)
    )
  )

DIAG_ADR_ENABLED_ = OFF

TNSNAME配置

ORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))


ssm=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
    (CONNECT_DATA =
      (SID=ssm)
    )
    (HS = OK)
  )

LISTENER_ssm =
  (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))                                                            

重启监听器即可。

七、测试

SQL>create PUBLIC DATABASE LINK test connect to "root" identified by "1234" using 'ssm';