Oracle通过ODBC连接MySql(Linux下)-配置多个odbc连接

一.环境（Linux系统64位操作系统）

1. 数据库：oracle11g 64位，mysql5.7 64位；

二.安装

1. 安装unixODBC：
   安装：yum install unixODBC*
   查看安装了哪些依赖包：rpm -qa |grep unixODBC

2. 安装mysql-connector：
   下载：
   https://dev.mysql.com/downloads/connector/odbc/
   https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm
   将mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm放在 /usr/local 目录下。
   安装：rpm -ivh mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm

3. ODBC配置： vi /etc/odbc.ini
   说明：User 、Password、Database分别为mysql数据库的用户名、密码、数据库

[dg4odbc]
Description    = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 192.168.71.90
Port            = 3306
User            = dbtest
Password        = 123456
Database        = testodbc
[mysql_test]
Description    = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = 192.168.71.90
Port            = 3306
User            = dbtest
Password        = 123456
Database        = testodbc                           

连通性测试：（dg4odbc 、mysql_test 和上边配置的名字对应）
isql dg4odbc -v
isql mysql_test -v

4.Oracle数据库相关配置
（1）vi /opt/oracle/product/112010/db_1/hs/admin/initdg4odbc.ora
已有内容注释掉，添加如下内容。这里配置的是数据库实例名、odbc lib包、odbc配置文件路径

HS_FDS_CONNECT_INFO = dg4odbc
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
set ODBCINI= /etc/odbc.ini

vi /opt/oracle/product/112010/db_1/hs/admin/initmysql_test.ora

HS_FDS_CONNECT_INFO = mysql_test
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
set ODBCINI= /etc/odbc.ini

（2）配置监听文件，给mysql重新配置一个监听：
vi /opt/oracle/product/112010/db_1/network/admin/listener.ora

SID_LIST_LISTENER1 =
  (SID_LIST =
   (SID_DESC =
      (SID_NAME = dg4odbc)
      (ORACLE_HOME = /opt/oracle/product/112010/db_1)
      (PROGRAM = dg4odbc)
    )
    (SID_DESC =
      (SID_NAME = mysql_test)
      (ORACLE_HOME = /opt/oracle/product/112010/db_1)
      (PROGRAM = dg4odbc)
    )
  )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.71.90)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER1 = /opt/oracle

启动监听（需要切换到oracle用户下：su - oracle）：
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ lsnrctl
LSNRCTL> start listener1
LSNRCTL> stop listener1

（只有一个监听的启动和停止：lsnrctl start/stop）

（3）配置tnsname：vi /opt/oracle/product/112010/db_1/network/admin/tnsnames.ora

dg4odbc =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.71.90)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dg4odbc)
    )
    (HS = OK)
  )

mysql_test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.71.90)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mysql_test)
    )

5. 用oracle连接工具创建dblink，使用管理员身份用户执行：

create database link dg4odbc connect to "dbtest" identified by "123456" using 'dg4odbc';--注意使用单引号
create database link mysql_test connect to "dbtest" identified by "123456" using 'mysql_test';

或者
先用管理员身份用户对业务用户授权，再用scott用户创建dblink

grant create database link to scott;

注：如果在create之后加public(即create pubic database link……),则创建的dblink就是公共的,不加public就只有创建者可以使用了.

6. 测试

select * from dual@dg4odbc;
select * from “table1”@dg4odbc;--表名区分大小写时，需要用双引号
insert into table1@"dg4odbc" values (2, '你好');
select m.*,m."aaa",m."bBb" from table1@dg4odbc m --注意区分大小写，否则报错：Oracle ORA-00904："xxx": 标识符无效

大功告成。

---

原文参考：https://www.linuxidc.com/Linux/2018-04/152116.htm
https://www.linuxidc.com/Linux/2015-11/124837.htm