ORACLE 11g ͨ¹ýodbc½¨Á¢Á¬½Óµ½mysql¿âµÄdatabase link

ÒÔÇ°×ö¹ýÔÚoracleͨ¹ýgateway½¨Á¢µ½sqlserverµÄdatabase link£¨ÏêÇé²Î¼û£ºhttps://blog.csdn.net/csdnhsh/article/details/95758865£©£¬ÏÖÔÚÐèÒªÎÞ·ì·ÃÎÊmysql£¬ËùÒÔÒ²ÐèÒª½¨Á¢ÀàËÆÕâÑùµÄÁ¬½ÓͨµÀ¡£

1¡¢·ÖÅämysqlÕ˺Å

ÏÈÔÚmysql¿âÉϽ¨Á¢Á¬½ÓÕ˺ţº

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

2¡¢°²×°dg4odbc

ÏêϸµÄgateways°²×°²½Öè²Î¿¼http://blog.csdn.net/mchdba/article/details/52719278 ÀïÃæµÄ ¡°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_NAME is the DSN for the remote database.
ORACLE_HOME is the actual Oracle home file path.
PROGRAM tells 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ΪСд£¬³¢ÊԸijɴóд×ֶΣ¬¾Í¿ÉÒÔ²éѯ³öÀ´ÁË¡£

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 »¹ÊDZ¨Ò»ÑùµÄ´íÎó³öÀ´

ÐÂÌí¼ÓÒ»ÁÐÊý×ÖÁУº

È¥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>

ÎÊÌâÒÀ¾É£¬µÈ´ýºóÐø½øÒ»²½½â¾ö¡£