oracle到 mysql dblink_oracle建dblink去查询mysql

环境：

源端：Oracle /172.22.32.251/rhel 6.7

目标端:mysql 5.1/172.22.33.122

查询这2个包是否安装

[root@dbtest4 /]# rpm -qa | grep mysql-connector

mysql-connector-odbc-5.1.5r1144-7.el6.x86_64

[root@dbtest4 /]#  rpm -qa | grep ODBC

unixODBC-devel-2.2.14-14.el6.x86_64

unixODBC-2.2.14-14.el6.x86_64

然后配置odbc配置文件

[root@dbtest4 /]# cat /etc/odbc.ini

[mysqlodbc]

Driver       = /usr/lib64/libmyodbc5.so

Description  = MySQL ODBC 5.1 Driver DSN

SERVER       = 172.22.33.122

PORT         = 3306

USER         = app

Password     = app2020

Database     = amro_ho01

OPTION = 0

TRACE =  OFF

[root@dbtest4 /]# cat /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/psqlodbc.so

Setup = /usr/lib/libodbcpsqlS.so

Driver64 = /usr/lib64/psqlodbc.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/lib/libmyodbc5.so

Setup = /usr/lib/libodbcmyS.so

Driver64 = /usr/lib64/libmyodbc5.so

Setup64 = /usr/lib64/libodbcmyS.so

FileUsage = 1

测试连接

[root@dbtest4 /]# isql mysqlodbc -v

+—————————————+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+—————————————+

SQL> show databases

+—————————————————————–+

| Database                                                        |

+—————————————————————–+

| information_schema                                              |

| amro_ho01                                                       |

| amro_ho01_test                                                  |

| backup                                                          |

| messap_test                                                     |

| mysql                                                           |

| ogg                                                             |

| performance_schema                                              |

| qr_code_manage                                                  |

| sys                                                             |

| xie                                                             |

+—————————————————————–+

SQLRowCount returns 11

11 rows fetched

配置oracle环境变量

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/ora11g

export ORACLE_SID=amro

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib/$ORACLE_HOME/hs/lib:/usr/lib64:$LD_LIBRARY_PATH:

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GRID_HOME/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export NLS_DATE_FORMAT=”yyyy-mm-dd hh24:mi:ss”

umask 022

ODBCINI=/etc/odbc.ini; export ODBCINI

ODBCSYSINI=/etc; export ODBCSYSINI

ODBCINSTINI=/etc/odbc.ini

export ODBCINSTINI

配置监听,加个静态

[oracle@testdb1 admin]$ cat listener.ora

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dbtest4)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = mysqlodbc)

(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)

(PROGRAM = dg4odbc)

(ENVS=LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib:/usr/lib64)

)

)

然后加个tns

omy =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.22.32.251)(PORT = 1521))

(CONNECT_DATA =

(SID = mysqlodbc)

)

(HS = OK)

)

配置odbc监听

路径：$ORACLE_HOME/hs/admin

注意：名字要跟odbc配置的名字一样 mysqlodbc

配置透明网关。

[oracle@dbtest4 admin]$ vi initmysqlodbc.ora

HS_FDS_CONNECT_INFO = mysqlodbc

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so

HS_FDS_SQLLEN_INTERPRETATION=32

HS_LONG_PIECE_TRANSFER_SIZE=1258291

set DBCINI=/etc/odbc.ini

然后测试下连接

[oracle@dbtest4 admin]$ tnsping omy

TNS Ping Utility for Linux: Version 11.2.0.3.0 – Production on 19-OCT-2020 17:04:15

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.22.32.251)(PORT = 1521)) (CONNECT_DATA = (SID = mysqlodbc)) (HS = OK))

OK (0 msec)

创建dblink

SQL> create public database link to_mysql connect to “root” identified by “123456” using ‘omy’;

SQL> select owner,object_name from dba_objects where object_type=’DATABASE LINK’;

查询：

SQL> select count(*) from “bm_tsk_001″@to_mysql;