oracle配置dblink访问mysql数据库

总体思路：

oracle——>dg4odbc——>odbc——>mysql
以上是其中的一种方式。

环境说明：mysql安装在win server操作系统  
             oracle 11.2.0.4单实例，linux系统
复制

1、查看DG4ODBC版本

[oracle@VM_0_22_centos bin]$ file $ORACLE_HOME/bin/dg4odbc
/u01/app/oracle/product/11.2.0/db_1/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped
复制

2、安装unixODBC

yum源配置这里忽略

[root@VM_0_22_centos ~]# yum install unixODBC*
复制

3、安装成功后测试

[root@VM_0_22_centos ~]# odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@VM_0_22_centos ~]#
复制

4、mysql odbc驱动安装

官网下载：https://dev.mysql.com/downloads/file/?id=506086

[root@VM_0_22_centos soft]# rpm -ivh mysql-connector-odbc-8.0.26-1.el6.x86_64.rpm
warning: mysql-connector-odbc-8.0.26-1.el6.x86_64.rpm: Header V3 DSA/SHA256 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:mysql-connector-odbc ########################################### [100%]
Success: Usage count is 1
Success: Usage count is 1
复制

5、配置

vi /etc/odbc.ini
[mysql]
Description = mysql
Driver = MySQL ODBC 8.0 Unicode Driver
Server = ** //MySQL服务器IP
Database = yqt_if //MySQL数据库名 （对大小写敏感）
Port = 3306 //端口
USER = root //数据库用户名
Password = root //用户名密码
Socket =
Option = 3
Stmt =
CHARSET = UTF8 //数据库字符集
复制

6、连接测试

[root@VM_0_22_centos soft]# isql mysql -v
[S1000][unixODBC][MySQL][ODBC 8.0(w) Driver]Host 'IP' is not allowed to connect to this MySQL server
[ISQL]ERROR: Could not SQLConnect

备注：不支持远程连接

mysql数据库root用户授权
mysql -uroot -proot
use mysql
select host from user where user='root';
update user set host = '%' where user ='root'; //%匹配所有IP都可以远程连接 localhost --> %
flush privileges;
select host from user where user='root';
[root@VM_0_22_centos ~]# isql mysql -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show databases;
+-----------------------------------------------------------------+
| Database                                                        |
+-----------------------------------------------------------------+
| information_schema                                              |
| mysql                                                           |
| performance_schema                                              |
| sys                                                             |
| yqt_if                                                          |
+-----------------------------------------------------------------+
SQLRowCount returns 5
5 rows fetched
SQL> 
复制

7、配置Oracle监听 追加内容

vi listener.ora
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VM_0_22_centos)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC=
      (SID_NAME=myodbc5)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM=dg4odbc)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
ADR_BASE_LISTENER = /u01/app/oracle
复制

8、配置tnsnames.ora 追加

myodbc5 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <IP> )(PORT = 1521))
    (CONNECT_DATA =
      (SID=myodbc5)
    )
      (HS=OK)
  )
复制

9、配置ODBC监听

cd $ORACLE_HOME/hs/admin
vi initmyodbc5.ora
HS_FDS_CONNECT_INFO=mysql   
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS= FALSE
HS_LANGUAGE=AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR=UCS2  
set  ODBCINI=/etc/odbc.ini 

[oracle@VM_0_22_centos admin]$ lsnrctl stop
[oracle@VM_0_22_centos admin]$ lsnrctl start
SQL> alter system register;

##原监听
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM_0_22_centos)(PORT=1521)))
Services Summary...
Service "YTHJT" has 1 instance(s).
Instance "YTHJT", status READY, has 1 handler(s) for this service...
Service "YTHJTXDB" has 1 instance(s).
	Instance "YTHJT", status READY, has 1 handler(s) for this service...


##现监听
	(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VM_0_22_centos)(PORT=1521)))
Services Summary...
Service "YTHJT" has 1 instance(s).
 Instance "YTHJT", status READY, has 1 handler(s) for this service...
Service "YTHJTXDB" has 1 instance(s).
	Instance "YTHJT", status READY, has 1 handler(s) for this service...
Service "myodbc5" has 1 instance(s).
 Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
 The command completed successfull

[oracle@VM_0_22_centos admin]$ tnsping myodbc5

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-AUG-2021 23:20:56

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

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


 Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521)) (CONNECT_DATA = (SID=myodbc5)) (HS=OK))
OK (0 msec)
复制

10、创建dblink并测试结果

SQL> create database  link TEST_MYSQL 
connect to "root" identified by "root" 
using  'myodbc5';

##测试dblink 查询mysql数据
SQL> select * from "bill_log"@TEST_MYSQL;

no rows selected

SQL> select cvid from "bd_cvdoc"@TEST_MYSQL;

CVID
--------------------------------------------
15841726323
复制