oracle dblink mysql

配置oracle omsdb到 mysql(oms,ndop)dblink步骤:


1、核对数据库版本
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




（1） Oracle服务器上RPM包的安装
在Oracle所有的服务器上安装MySQL客户端与MySQL进行ODBC连接所需要的rpm包。
主要有如下几个包：
yum –y install mysql
yum –y install mysql-connector-odbc


对于Oracle，要进行ODBC的连接，还需要安装如下rpm包：
yum –y install unixODBC
yum –y install unixODBC-devel




（2） 在Oracle服务器上配置/etc/odbc.ini
在/etc/odbc.ini文件里，加入如下内容：
[root@omsdb-compare ~]#vi /etc/odbc.ini
[oms]
Driver       = /usr/lib64/libmyodbc5.so
Description  = this is a omsdb to mysql
SERVER       = 10.249.6.77
PORT         = 3306
USER         = global_user
Password     = global_user
Database     = oms
OPTION       = 3
#SOCKET       =
charset      = utf8
trace=0


[ndop]
Driver       = /usr/lib64/libmyodbc5.so
Description  = this is a oracle dblink to mysql ndop
SERVER       = 10.249.6.71
PORT         = 3306
USER         = global_user
Password     = global_user
Database     = dop
OPTION       = 3
#SOCKET       =
charset      = utf8
trace=0


（3） 测试连接是否畅通
使用如下命令可以测试当前的ODBC配置是否正确且能连接成功。
[root@omsdb-compare ~]# isql oms -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
[root@omsdb-compare ~]# isql ndop -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
[root@omsdb-compare ~]# 


（4） 配置Oracle的环境变量
检查Oracle环境变量，在LD_LIBRARY_PATH上加入$ORACLE_HOME/hs/lib项。


（5） 监听与TNS的配置
编辑$ORACLE_HOME/network/admin/listener.ora，加入如下内容：
[oracle@omsdb-compare admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = omsdb-compare)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )






SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC=
         (PROGRAM = dg4odbc)
         (SID_NAME= oms)
         (ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/dbhome_1)
         (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/db_1/lib:/u01/app/oracle/product/11.2.0/db_1/hs/lib)
      )
        (SID_DESC=
         (PROGRAM = dg4odbc)
         (SID_NAME= ndop)
         (ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/dbhome_1)
         (ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/lib:/u01/app/oracle/product/11.2.0.4/dbhome_1/hs/lib)
      )
  )


ADR_BASE_LISTENER = /u01/app/oracle


[oracle@omsdb-compare admin]$ 


编辑$ORACLE_HOME/network/admin/tnsnames.ora文件，加入如下内容：
[oracle@omsdb-compare admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
mysqloms=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.249.34.27)(PORT = 1521))
    (CONNECT_DATA =
      (SID = oms))
      (HS = OK)
  )




mysqlndop=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.249.34.27)(PORT = 1521))
    (CONNECT_DATA =
      (SID = ndop))
      (HS = OK)
  )


[oracle@omsdb-compare admin]$ 
（6） 配置ODBC监听
[oracle@omsdb-compare admin]$ cd /u01/app/oracle/product/11.2.0.4/dbhome_1/hs/admin/tnsnames.ora.sample
[oracle@omsdb-compare admin]$ vi initoms.ora 
HS_FDS_CONNECT_INFO = oms
HS_FDS_TRACE_LEVEL = OFF
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.AL32UTF8
#HS_FDS_SQLLEN_INTERPRETATION=32
HS_NLS_NCHAR=UCS2
set ODBCINI = /etc/odbc.ini


[oracle@omsdb-compare admin]$ vi initndop.ora 
HS_FDS_CONNECT_INFO = ndop
HS_FDS_TRACE_LEVEL = OFF
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.AL32UTF8
#HS_FDS_SQLLEN_INTERPRETATION=32
HS_NLS_NCHAR=UCS2
set ODBCINI = /etc/odbc.ini


[oracle@omsdb-compare admin]$ 


（7） 测试TNS
可以使用tnsping命令来检查网络是否可以走通。
[oracle@omsdb-compare admin]$ tnsping mysqloms


TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 30-MAY-2016 10:49:29


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


Used parameter files:
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora




Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.249.34.27)(PORT = 1521)) (CONNECT_DATA = (SID = oms)) (HS = OK))
OK (0 msec)
[oracle@omsdb-compare admin]$ tnsping mysqlndop


TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 30-MAY-2016 10:49:39


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


Used parameter files:
/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora




Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.249.34.27)(PORT = 1521)) (CONNECT_DATA = (SID = ndop)) (HS = OK))
OK (0 msec)
[oracle@omsdb-compare admin]$ 


（8） 创建dblink
网络走通以后，就可以创建dblink了。创建完成以后，尝试访问MySQL数据库上的
表，检查是否可以正常访问。
SQL>
sql>create public database link TO_OMS connect to "global_user" identified by "xxx" using 'mysqloms';
sql>create public database link TO_DOP connect to "global_user" identified by "xxx" using 'mysqlndop';


(9) 测试dblink
[oracle@omsdb-compare admin]$ sqlplus '/as sysdba'


SQL*Plus: Release 11.2.0.4.0 Production on Mon May 30 10:51:35 2016


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select count(*) from "t_user"@TO_OMS
  2  ;


  COUNT(*)
----------
2




SQL> select count(*) from "t_auth_role"@TO_DOP;


  COUNT(*)
----------
4

至此完成oracle到mysql的dblink.

有可能会出现一些表出现不来 换个表试试