|
以前做过在oracle通过gateway建立到sqlserver的database link（详情参见：http://www.52php.cn/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.52php.cn/article/p-oenydkuj-bau.html 里面的 “2、安装模块” 里面有详细的步骤描述。
在安装的时候，有一个关键的选择步骤就是选择odbc组件包进行安装，如下所示：E:uoracleinstallpic11.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_HOMEis 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表的dml、ddl操作都会处于等待状态，如下所示：
| 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>
问题依旧，等待后续进一步解决。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
