这期内容当中小编将会给大家带来有关oracle如何通过dblink连接mysql实施,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。
本周由于公司一个系统的oracle数据库需要采集另一个系统mysql数据库的数据,于是对oracle通过dblink连接mysql进行了研究,测试环境是单实例oracle 10g 64位,折腾了半天搞定了,但是生产环境是10g rac,原本以为会很顺利,但是折腾了一天多都没搞定,问题主要出在监听上面,原本打算放弃,采用中间件连接mysql取数据,但是凭直觉感觉应该是单实例和rac的监听配置差异造成的,于是在rac节点上单独创建默认监听,问题果然解决了。
当配置有问题的时候都会报下面的错误:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
遇到的主要问题和解决办法有:
1、oracle 10g 64位的hsodbc应该有问题,文件大小为0,执行hsodbc没有任何信息;
解决:拷贝32机器下的hsodbc到$ORACLE_HOME/bin目录下,替换原hsodbc,注意oracle要有权限,下载:http://space.itpub.net/?uid-28321441-action-viewspace-itemid-750733
2、rac监听不能监听到hsodbc服务;
解决:netca选择single node configuration创建监听来监听hsodbc服务,监听端口选择1522;
3、连接mysql查询数据乱码。
解决:编辑/etc/odbc.ini,增加
charset =gbk
STMT =SET NAMES 'GBK'
其中gbk是MYSQL字符集。
具体实施步骤如下:
--oracle服务器:
-- 操作系统:Red Hat Enterprise Linux 5.3
-- 数据库: Oracle RDBMS 10.2.0.3.0
--mysql服务器:
-- 操作系统:windows server 2003
-- 数据库: MySQL 5.5
单实例操作如下,操作都在oracle服务器上完成:
--检查需要的rpm包,需要的包如下,由于hsodbc应该是只支持32位,所以32位包是必须的:
libtool-ltdl-1.5.22-6.1.i386.rpm
libtool-ltdl-1.5.22-6.1.x86_64.rpm
mysql-5.0.77-3.el5.i386.rpm
mysql-5.0.77-3.el5.x86_64.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.i386.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.i386.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.11-7.1
unixODBC-2.2.11-7.1
# rpm -qa |grep mysql
mysql-5.0.45-7.el5
mysql-5.0.45-7.el5
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-connector-odbc-3.51.26r1127-1.el5
--编辑/etc/odbc.ini
vi /etc/odbc.ini
#[ODBC Data Sources]
#myodbc3 = MySQL ODBC 3.51 Driver DSN
[test]
Driver = /usr/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
SERVER = 24.1.20.149
PORT = 3306
USER = root
Password = laopo
Database = teng_push
OPTION = 3
SOCKET =
charset =gbk
STMT =SET NAMES 'GBK'
--最后两个是解决中文乱码问题,gbk是MYSQL字符集
--切换到oracle用户
# su - oracle
--编辑环境变量配置文件,主要是LD_LIBRARY_PATH和最后面两项
$ vi ~/.bash_profile
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
#export LANG=zh_CN.GB18030
export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/bin:/usr/bin:/usr/sbin
:/usr/local/bin:/usr/X11R6/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
#ulimit -u 16384 -n 65536
umask 022
export EDITOR=vi
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
--使配置生效
source ~/.bash_profile
--查看odbc版本及参数文件路径
$odbcinst -j
unixODBC 2.2.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
USER DATA SOURCES..: /usr/local/etc/odbc.ini
--测试 My SQL ODBC 驱动
$isql test -v(测试前需要将/etc/odbc.ini中的Driver= /usr/lib/libmyodbc3.so 修改成/usr/lib64/libmyodbc3.so,测试完后还原)
isql test oracle manager -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
--配置 HSODBC 程序
vi $ORACLE_HOME/hs/admin/inittest.ora
HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
set DBCINI=/etc/odbc.ini
--确认 hsodbc 的配置是否正确,如果正确可以看到版本号
--注意:如果是64位oracle,由于hsodbc应该是只支持32位,这里需要拷贝32位机器上的hsodbc到$ORACLE_HOME/bin下替换原有的hsodbc
$ hsodbc
Oracle Corporation --- FRIDAY DEC 06 2012 12:20:44.240
Heterogeneous Agent Release 10.2.0.3.0 - Production Built with
Driver for ODBC
--修改监听文件,增加下面服务:
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
)
$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
--修改tnsnames.ora
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID= test)
)
(HS=OK)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
--重启监听,要有test服务
$ lsnrctl stop
$ lsnrctl start
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--tnsping测试服务
$ tnsping test
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)(HOST = 24.1.20.30)(PORT = 1521))) (CONNECT_DATA = (SID= test)) (HS=OK))
OK (0 msec)
--创建dblink
CREATE PUBLIC DATABASE LINK mysql CONNECT TO "root" IDENTIFIED BY "laopo" USING 'test';
--测试
select * from "emp"@mysql where "id"=1;
至此,单节点配置完成。
rac环境下每个节点都需要配置,和单实例唯一的区别是监听的配置,我试着多种方式去配置监听,但都没有监听到hsodbc的test服务,最后通过netca选择single node configuration创建监听来监听test服务才成功,注意监听端口改成1522
rac环境节点1的监听文件如下:
$vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER_RAC01 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER_RAC01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(PROGRAM=hsodbc)
(ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1)
(SID_NAME=test)
(ENVS=LD_LIBRARY_PATH=/opt/ora10g/product/10.2.0/db_1/lib32:/lib:/usr/lib:
/opt/ora10g/product/10.2.0/db_1/lib)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1522))
)
)
上述就是小编为大家分享的oracle如何通过dblink连接mysql实施了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注亿速云行业资讯频道。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。