Oracle透明网关访问MySQL数据库

Oracle Corporation --- FRIDAY    APR 27 2018 10:07:44.375

Heterogeneous Agent Release 11.2.0.4.0 - 64bit Production  Built with
   Oracle Database Gateway for ODBC

[root@test ~]# rpm -ivh mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm
warning: mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-connector-odbc   ########################################### [100%]
Success: Usage count is 1
Success: Usage count is 1

[mysql_test]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = mysql_ipaddr
Port            = 3306
User            = dbtest
Password        = abcd1234
Database        = test 

账号创建：
(root:localhost:Fri Apr 27 10:16:11 2018)[(none)]>create database test;
(root:localhost:Fri Apr 27 10:16:22 2018)[(none)]>grant all on test.* to dbtest@'%' identified by 'abcd1234';
(root:localhost:Fri Apr 27 10:16:40 2018)[(none)]>flush privileges;
连通性测试：

[root@test ~]# isql mysql_test    
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

##HS Configuration

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_LANGUAGE=AMERICAN_AMERICA.zhs16gbk

##ODBC Configuration

set ODBCINI=/etc/odbc.ini

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = dbsid)
  (SID_NAME = dbsid)
  (ORACLE_HOME=/U01/app/oracle/product/11.2.0.4)
  )
  (SID_DESC=
  (SID_NAME=mysql_test)
  (ORACLE_HOME=/U01/app/oracle/product/11.2.0.4)
  (PROGRAM=dg4odbc)
  )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))
    )
)

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

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-APR-2018 12:17:58

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))) (CONNECT_DATA = (SID = mysql_test)) (HS = OK))
OK (0 msec)

SQL>create  PUBLIC DATABASE LINK dlk connect to "dbtest" identified by "abcd1234" using 'dbsid_mysql';

SQL> select * from "t1"@dlk;

        id
----------
        10
        11
       
SQL> insert into "t1"@dlk values(30);

1 row created.

SQL> select * from t1@dlk;
select * from t1@dlk
                 *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from DLK

SQL> select * from "t1"@dlk;
select * from "t1"@dlk
                   *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[

SQL> select * from t1@dlk;
select * from t1@dlk
*
ERROR at line 1:
ORA-00942: table or view does not exist
[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.18-log]Table 'test.T1' doesn't exist
{42S02,NativeErr = 1146}
ORA-02063: preceding 2 lines from DLK