linux通过odbc连接mysql,Oracle通过ODBC连接MySql(Linux下)-只配置一个odbc连接

一.环境(Linux系统64位操作系统)

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

二.安装

安装unixODBC：

安装：yum install unixODBC*

查看安装了哪些依赖包：rpm -qa |grep unixODBC

安装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

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

连通性测试： isql dg4odbc -v

±--------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

±--------------------------------------+

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

(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)

)

)

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)

)

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

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

或者

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

grant create database link to scott;

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

测试

select * from dual@dg4odbc;

select * from test.table1@dg4odbc;

insert into test.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