Oracle建立dblink MySQL

 ODBC connector:

mysql-connector-odbc-5.1.13-1.rhel5

 

ODBC驅動：

unixODBC-2.2.11-7.1

unixODBC-devel-2.2.11-7.1

 

OS版本:

Red Hat Enterprise Linux Server release 5.4 (Tikanga)

 
Oracle DB server中配置：

[root@scmauto opt]# rpm -ivh unixODBC-2.2.11-7.1.i386.rpm

warning: unixODBC-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186

Preparing...                ########################################### [100%]

   1:unixODBC               ########################################### [100%]

[root@scmauto opt]# rpm -qa|grep unixODBC

unixODBC-2.2.11-7.1

[root@scmauto opt]# rpm -ivh unixODBC-2.2.11-7.1.x86_64.rpm

warning: unixODBC-2.2.11-7.1.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 37017186

Preparing...                ########################################### [100%]

   1:unixODBC               ########################################### [100%]

[root@scmauto opt]# rpm -ivh unixODBC-devel-2.2.11-7.1.i386.rpm

warning: unixODBC-devel-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 37017186

Preparing...                ########################################### [100%]

   1:unixODBC-devel         ########################################### [100%]

[root@scmauto opt]# rpm -ivh unixODBC-devel-2.2.11-7.1.x86_64.rpm

warning: unixODBC-devel-2.2.11-7.1.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 37017186

Preparing...                ########################################### [100%]

   1:unixODBC-devel         ########################################### [100%]

 

[root@scmauto Server]# yum install mysql-connector-odbc

[root@scmauto Server]# vi /etc/odbc.ini

[my1613]

Driver=/usr/lib64/libmyodbc5.so

Server=10.202.16.13

User=root

Password=Oracle123

Port=3306

database=test1

charset=utf8

 

[root@scmauto Server]# isql my1613

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

 

scmauto<*scmauto*/u01/product/oracle/hs/admin>$more initmy1613.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

 

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = my1613

HS_FDS_TRACE_LEVEL = debug

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so

HS_LANGUAGE=AMERICAN_AMERICA.UTF8

#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

#HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

HS_FDS_SQLLEN_INTERPRETATION=32

HS_IDLE_TIMEOUT = 1440

 

#HS_NLS_NCHAR=AL32UTF8

HS_NLS_NCHAR=UCS2

 

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

 

scmauto<*scmauto*/u01/product/oracle/network/admin>$vi tnsnames.ora

my1613 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.202.10.150)(PORT = 1526))

    (CONNECT_DATA = (SERVICE_NAME = my1613))

    (HS = OK)

  )

 

scmauto<*scmauto*/u01/product/oracle/network/admin>$vi listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = my1613)

      (ORACLE_HOME = /u01/product/oracle)

      (PROGRAM = dg4odbc)

      (ENVS="LD_LIBRARY_PATH=/usr/lib:/usr/lib64:/u01/product/oracle/lib")

    )

  )

 

[root@scmauto unixODBC-2.3.4]# ./configure --prefix=/usr/local/unixODBC-2.3.4 --includedir=/usr/include --libdir=/usr/lib --bindir=/usr/bin --sysconfdir=/etc

 

[root@scmauto unixODBC-2.3.4]# make && make install

 

[root@scmauto unixODBC-2.3.4]# odbcinst -j

unixODBC 2.3.4

DRIVERS............: /usr/local/etc/odbcinst.ini

SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini

FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

 

 

CREATE PUBLIC DATABASE LINK MY1613

 CONNECT TO "root"

 IDENTIFIED BY "Oracle123"

 USING 'my1613';

 
Oracle中執行：

select * from "emp1"@MY1613