ORACLE 11g 通过odbc建立连接到mysql库的database link

前端之家收集整理的这篇文章主要介绍了ORACLE 11g 通过odbc建立连接到mysql库的database link前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。


以前做过在oracle通过gateway建立到sqlserver的database link(详情参见:http://www.jb51.cc/article/p-oenydkuj-bau.html),现在需要无缝访问MysqL,所以也需要建立类似这样的连接通道。


1、分配MysqL账号

先在MysqL库上建立连接账号:

GRANT SELECT ON test.* TO data_query@'192.168.129.%' IDENTIFIED BY 'data_query_1125';


2、安装dg4odbc

详细的gateways安装步骤参考http://www.jb51.cc/article/p-oenydkuj-bau.html 里面的 “2、安装模块” 里面有详细的步骤描述。

在安装的时候,有一个关键的选择步骤就是选择odbc组件包进行安装,如下所示:E:\u\oracle\install\pic\11.png,


3、查看下安装的dg4odbc

查看安装是否完成,就直接用莫N股了dg4odbc命令来看

[oracle@hch_test_121_61 ~]$ dg4odbc

Oracle Corporation --- TUESDAY NOV 15 2016 19:17:19.106

Heterogeneous Agent Release 11.2.0.1.0 - 64bit Production Built with

Oracle Database Gateway for ODBC

[oracle@hch_test_121_61 ~]$


4、安装MysqL组件

准备安装MysqL客户端:yum install MysqL-connector-odbc.x86_64 -y;

这样会安装一些依赖包主要是

MysqL-connector*.x86_64.rpm

unixODBC*.x86_64.rpm

unixODBC*.i386.rpm

[root@hch_test_121_90 ~]# yum install MysqL-connector-odbc.x86_64 -y

Loaded plugins: fastestmirror,security

Determining fastest mirrors

* base: ftp.sjtu.edu.cn

* extras: mirrors.163.com

* updates: mirrors.163.com

base | 3.7 kB 00:00

epel | 4.3 kB 00:00

epel/primary_db | 5.9 MB 00:06

extras | 3.4 kB 00:00

updates | 3.4 kB 00:00

updates/primary_db | 3.1 MB 00:00

zabbix | 951 B 00:00

zabbix-non-supported | 951 B 00:00

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package MysqL-connector-odbc.x86_64 0:5.1.5r1144-7.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================================================================================

Package Arch Version Repository Size

===============================================================================================================================================================================================

Installing:

MysqL-connector-odbc x86_64 5.1.5r1144-7.el6 base 114 k

Transaction Summary

===============================================================================================================================================================================================

Install 1 Package(s)

Total download size: 114 k

Installed size: 345 k

Downloading Packages:

MysqL-connector-odbc-5.1.5r1144-7.el6.x86_64.rpm | 114 kB 00:00

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : MysqL-connector-odbc-5.1.5r1144-7.el6.x86_64 1/1

Verifying : MysqL-connector-odbc-5.1.5r1144-7.el6.x86_64 1/1

Installed:

MysqL-connector-odbc.x86_64 0:5.1.5r1144-7.el6

Complete!

[root@hch_test_121_90 ~]#

5、配置odbc-MysqL连接

[root@hch_test_121_90 ~]# more /etc/odbc.ini

# Setup from the unixODBC64-libs package

[rmt]

Driver = /usr/lib64/libmyodbc5.so

Server = 10.254.3.72

Port = 3306

User = data_query

Password = data_query_1125

Database = test

[root@hch_test_121_90 ~]#

6、测试MysqL组件的连接:

[root@hch_test_121_90 ~]# isql rmt

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

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

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

sql>

7、配置oracle连接

进去$ORACLE_HOME/hs/admin/,配置initrmt.ora

[oracle@hch_test_121_90 admin]$ more initrmt.ora

HS_FDS_CONNECT_INFO=rmt

# Data source name in odbc.ini

HS_FDS_TRACE_LEVEL= debug

HS_FDS_SHAREABLE_NAME=libodbc.so

HS_FDS_SUPPORT_STATISTICS=FALSE

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

#

# ODBC env variables

set ODBCINI=/etc/odbc.ini

HS_FDS_SHAREABLE_NAME=libodbc.so

[oracle@hch_test_121_90 admin]$

配置$ORACLE_HOME/network/admin/listener.ora

SID_NAMEis the DSN for the remote database.
ORACLE_HOME
is the actual Oracle home file path.
PROGRAMtells Oracle to use heterogeneous services.

(SID_DESC =

(SID_NAME = rmt)

(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM = dg4odbc)

#(ENVS=LD_LIBRARY_PATH=/usr/lib64:/usr/lib:/home/orared/product/11.2.4/lib:/home/orared/product/11.2.4/odbc/lib)

)

配置$ORACLE_HOME/network/admin/tnsnams.ora

rmtMysqL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90 )(PORT = 1521))

)

(CONNECT_DATA =

(SID = rmt)

)

(HS = OK)

)

8、建立dblink

create database link tg4MysqL connect to data_query identified by "data_query_1125"

using '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90)(PORT = 1521))

)

(CONNECT_DATA =

(SID = rmtMysqL)

)

(HS = OK)

)';

测试:

sql> select * from z_whs@tg4MysqL;

select * from z_whs@tg4MysqL

*

ERROR at line 1:

ORA-28545: error diagnosed by Net8 when connecting to an agent

Unable to retrieve text of NETWORK/NCR message 65535

ORA-02063: preceding 2 lines from TG4MysqL

sql>

解决办法:重启lsnrctl服务

9、问题ORA-28511

sql> select * from Z_WHS@gwMysqL;

ERROR:

ORA-28511: lost RPC connection to heterogeneous remote agent using

SID=ORA-28511: lost RPC connection to heterogeneous remote agent using

SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.90)(PORT

=1521)))(CONNECT_DATA=(SID=rmt)))

ORA-02063: preceding line from GWMysqL

Process ID: 20358

Session ID: 11 Serial number: 46988

no rows selected

sql>

再次查看:

sql> select * from Z_WHS@gwMysqL;

ERROR:

ORA-28511: lost RPC connection to heterogeneous remote agent using

SID=ORA-28511: lost RPC connection to heterogeneous remote agent using

SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.90)(PORT

=1521)))(CONNECT_DATA=(SID=rmt)))

ORA-02063: preceding line from GWMysqL

Process ID: 20497

Session ID: 134 Serial number: 41341

no rows selected

sql> select ID,cname from Z_WHS@gwMysqL;

select ID,cname from Z_WHS@gwMysqL

*

ERROR at line 1:

ORA-00904: "CNAME": invalid identifier

sql> select ID from Z_WHS@gwMysqL;

ID

----------

1

2

3

sql>

为什么查询单个字段ID能查到记录,查询*查不出记录呢,查看Z_WHS表结构,发现有一个字段cname为小写,尝试改成大写字段,就可以查询出来了。

MysqL> show create table Z_WHS;

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

| Table | Create Table |

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

| Z_WHS | CREATE TABLE `Z_WHS` (

`ID` bigint(20) NOT NULL DEFAULT '0' COMMENT '投诉主题表主键',

`cname` varchar(16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

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

1 row in set (0.00 sec)

MysqL>

MysqL> alter table Z_WHS change `cname` CNAME varchar(16) DEFAULT NULL;

Query OK,0 rows affected (38.95 sec)

Records: 0 Duplicates: 0 Warnings: 0

MysqL>

# PS:这个时候,oracle的网关对MysqL的表是锁定状态,任何对MysqL表的dmlddl操作都会处于等待状态,如下所示:

| 13762502 | root | localhost | test | Query | 27 | Waiting for table Metadata lock | alter table Z_WHS change `cname` CNAME varchar(16) DEFAULT NULL |

需要在oracle的窗口退出来,释放掉通过网关对MysqL的锁,才能让MysqL窗口的alter语句sesseion会话得到这个表的使用权限来顺利执行完成alter操作。

BUT、但是,但是可惜的是alter语句执行完后,select cname 还是报一样的错误出来

添加一列数字列:

MysqL添加数字列

MysqL> alter table Z_2 add column NUM int;

Query OK,0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

MysqL> update Z_2 set NUM=1;

Query OK,1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MysqL>

再通过gateway查询结果:

sql> conn dw/dwys0418

Connected.

sql> select ID,NUM from "Z_2"@gwMysqL;

ID NUM

---------- ----------

1 1

sql> select ID,NUM,CN from "Z_2"@gwMysqL;

ERROR:

ORA-28511: lost RPC connection to heterogeneous remote agent using

SID=ORA-28511: lost RPC connection to heterogeneous remote agent using

SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.121.90)(PORT

=1521)))(CONNECT_DATA=(SID=rmt)))

ORA-02063: preceding line from GWMysqL

Process ID: 21333

Session ID: 11 Serial number: 47108

no rows selected

sql>

问题依旧,等待后续进一步解决

原文链接:https://www.f2er.com/oracle/211750.html

猜你在找的Oracle相关文章