Oracle 通过ODBC 连接MySql
来源:互联网 发布:紫鸟数据魔方可靠吗 编辑:程序博客网 时间:2024/05/17 23:43
折腾了oracle到mysql的dblink访问,分享给大家:要连接的两端:
oracle rhel 6.5 64位 11.2.0.4
mysql 64 5.5 utf8
操作都在oracle服务器上完成:
-----------------------------------------------------------------
--检查需要的rpm包,需要的包如下:
libtool-ltdl-1.5.22-6.1.x86_64.rpm
mysql-5.0.77-3.el5.x86_64.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.x86_64.rpm
perl-DBI-1.52-2.el5.x86_64.rpm
unixODBC-2.2.11-7.1.x86_64.rpm
上面unixODBC-2.2.11-7.1、mysql-5.0.77-3.el5、mysql-connector-odbc是需要的包,其他是安装这些包的前提。
---------------------------------------------------------------
安装完后检查
# rpm -qa |grep unixODBC
unixODBC-2.2.14-12.el6_3.x86_64
unixODBC-devel-2.2.14-12.el6_3.x86_64
# rpm -qa |grep mysql
qt-mysql-4.6.2-26.el6_4.x86_64
mysql-server-5.1.71-1.el6.x86_64
mysql-libs-5.1.71-1.el6.x86_64
mysql-5.1.71-1.el6.x86_64
mysql-connector-odbc-5.1.5r1144-7.el6.x86_64
mysql-devel-5.1.71-1.el6.x86_64
--编辑/etc/odbc.ini
vi /etc/odbc.ini
[myodbc]
Driver = /usr/lib64/libmyodbc5.so
Description = MySQL ODBC 5.1 Driver DSN
SERVER = 192.168.0.2
PORT = 3306
USER = my_sql
Password = my_sql
Database = my_db
OPTION = 3
SOCKET =
#charset = gbk
--切换到oracle用户
# su - oracle
--编辑环境变量配置文件,主要是LD_LIBRARY_PATH和最后面两项
$ vi ~/.bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=nop
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/hs/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib:/usr/lib64
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jkd/jre/lib/i386
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jkd/jre/lib/i386/server
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/rdbms/lib
export CLASS_PATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib
export CLASS_PATH=$CALSS_PATH:$ORACLE_HOME/rdbms/jlib
export CLASS_PATH=$CALSS_PATH:$ORACLE_HOME/network/jlib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=american_america.AL32UTF8
export ORACLE_TERM=xterm
export EDITOR=vi
export PATH=$ORACLE_HOME/bin:$PATH
export LANG=en_US
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
ODBCINSTINI=/etc/odbc.ini
export ODBCINSTINI
--使配置生效
source ~/.bash_profile
--查看odbc版本及参数文件路径
$odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
--测试 My SQL ODBC 驱动
$isql myodbc
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
--配置 HSODBC 程序
vi $ORACLE_HOME/hs/admin/initmyodbc.ora
HS_FDS_CONNECT_INFO = myodbc
HS_FDS_TRACE_LEVEL = debug
HS_FDS_TRACE_LEVEL = 4
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.UTF8 --此处要跟目标mysql的字符集一致
HS_NLS_NCHAR = UCS2
HS_IDLE_TIMEOUT =1440
HS_LONG_PIECE_TRANSFER_SIZE=1258291
HS_FDS_SQLLEN_INTERPRETATION=64 --此处要跟目标mysql的位数一致
set ODBCINI = /etc/odbc.ini
--配置监听
$ cd $TNS_ADMIN
$ ls
listener.ora samples shrept.lst tnsnames.ora
$ vim listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mypc)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(PROGRAM = dg4odbc)
(SID_NAME= myodbc)
(ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib:/u01/app/oracle/product/11.2.0/dbhome_1/odbc/lib:/usr/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0/dbhome_1/hs/lib:/usr/lib64)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
vim tnsnames.ora
添加:
MYODBC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mypc)(PORT = 1521))
)
(CONNECT_DATA =
(SID = myodbc)
)
(HS = OK)
)
--重启监听,要有 MYODBC 服务
$ lsnrctl stop
$ lsnrctl start
--tnsping测试服务
$ tnsping myodbc
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mypc)(PORT = 1521))) (CONNECT_DATA = (SID = myodbc)) (HS = OK))
OK (0 msec)
--创建dblink
create public database link mysql connect to "my_sql" identified by "my_sql" using 'myodbc';
--测试
select * from "my_tab"@mysql;
阅读全文
0 0
- Oracle 通过ODBC 连接MySql
- 通过ODBC连接oracle
- 通过ODBC连接Oracle
- Oracle 通过ODBC 连接GreenPlum
- PowerBuilder通过ODBC连接MySQL
- PowerBuilder通过ODBC连接MySQL
- Debian通过ODBC连接Mysql
- cognos通过odbc连接mysql
- LINUX通过ODBC连接MySQL
- ORACLE通过odbc数据源连接sqlserver
- C++通过ODBC连接Oracle数据库
- ORACLE通过odbc数据源连接sqlserver
- 通过脚本生成oracle的ODBC连接
- VC与MYSQL连接通过ODBC
- [MySQL]VC++通过Pipe连接数据库(ODBC)
- vb6通过ODBC连接mysql数据库
- excel通过ODBC连接Mysql数据库
- c#通过ODBC数据源连接Mysql数据库
- SAP 取月度期初库存和月度期末库存(历史库存)-mard和mardh的区别
- git 合并本地两个分支
- 通过jQuery Ajax使用FormData对象上传文件
- 挑战程序竞赛系列(25):3.5最大权闭合图
- A
- Oracle 通过ODBC 连接MySql
- zabbix api
- deeplab caffe ubuntu16.04 CUDA 8.0
- string.h详解
- 算法题练习系列之(十五): D进制的A+B
- Linux系统安装JDK
- Servlet--获取项目运行目录getRealPath(一)
- 前端框架Vue(12)——如何将 vue-cli 项目打包压缩(npm run build)后放到服务器
- String StringBuffer StringBuffer的区别