oracle通过DBlink连接mysql

来源：互联网发布：吾玛牛奶怎么样知乎编辑：程序博客网时间：2024/05/16 16:08

oracle：系统redhat 5.5 数据库：11.2.0.1

mysql：系统centos6.3 数据库：5.6.21

1.先装 mysql-connector-odbc和unixODBC

[root@rac1 ~]# rpm -qa | grep mysql
mysql-5.0.77-4.el5_4.2
mysql-5.0.77-4.el5_4.2
mysql-connector-odbc-5.1.13-1.rhel5

[root@rac1 ~]# rpm -qa | grep ODBC
unixODBC-devel-2.2.11-7.1
unixODBC-2.2.11-7.1
unixODBC-devel-2.2.11-7.1
unixODBC-2.2.11-7.1

2.配置 /etc/odbc.ini
[myodbc3]
Driver       = /usr/lib64/libmyodbc5.so
Description  = MySQL ODBC 5.1 Driver DSN
SERVER       = 192.1.1.200
PORT         = 3306
USER         = bi
Password     = 123456
Database     = chanpin
OPTION       = 3
SOCKET       =
charset      = utf8

3.配置/etc/odbcinst.ini
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc5.so
Setup = /usr/lib64/libodbcmyS.so
FileUsage = 1

4.测试连接
[root@rac1 ~]# isql myodbc3 -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

5.配置oracle环境变量
export ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/grid
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=rac1
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH:/usr/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib:$ORACLE_HOME/hs/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
ODBCINSTINI=/etc/odbc.ini
export ODBCINSTINI

6.配置监听
listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))
    )
  )

SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
         (PROGRAM = dg4odbc)
         (SID_NAME= myodbc3)
         (ORACLE_HOME= /u01/app/oracle/product/11.2.0/db_1)
        (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0/db_1/hs/lib:/usr/lib64)
         )
   )

tnsname.ora:

myodbc3=
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522))
    (CONNECT_DATA =
      (SID = myodbc3))
      (HS = OK)
  )

7.配置odbc监听
路径：$ORACLE_HOME/hs/admin
注意：名字要跟odbc配置的名字一样我这里是myodbc3

[oracle@rac1 admin]$ cat initmyodbc3.ora
HS_FDS_CONNECT_INFO = myodbc3
HS_FDS_TRACE_LEVEL = ON
HS_FDS_TRACE_FILE_NAME = odbc_test.log
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISCTICS = FALSE
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SQLLEN_INTERPRETATION=32
set ODBCINI = /etc/odbc.ini

8.测试下监听
[oracle@rac1 admin]$ tnsping myodbc3

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-NOV-2014 03:02:16

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

Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.1.1.100)(PORT = 1522)) (CONNECT_DATA = (SID = myodbc3)) (HS = OK))
OK (0 msec)

9.创建dblink
SQL>create public database link myodbc connect to "bi" identified by "123456" using'myodbc3';
SQL> select count(*) from "t_user"@myodbc;

  COUNT(*)
----------
     53980

0 0