以前做过在oracle通过gateway建立到sqlserver的database link（详情参见：http://www.jb51.cc/article/p-oenydkuj-bau.html），现在需要无缝访问@R_301_198@，所以也需要建立类似这样的连接通道。
1@H_403_10@、分配@H_403_10@@R_301_198@@H_403_10@账号@H_403_10@
先在@R_301_198@库上建立连接账号：
@H_403_10@
GRANT SELECT ON test.* TO data_query@'192.168.129.%' IDENTIFIED BY 'data_query_1125'; |
2@H_403_10@、安装@H_403_10@dg4odbc@H_403_10@
详细的gateways安装步骤参考http://www.jb51.cc/article/p-oenydkuj-bau.html 里面的 “2、安装模块” 里面有详细的步骤描述。
在安装的时候，有一个关键的选择步骤就是选择odbc组件包进行安装，如下所示：E:\u\oracle\install\pic\11.png，
3@H_403_10@、查看下安装的@H_403_10@dg4odbc@H_403_10@
[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@H_403_10@、安装@H_403_10@@R_301_198@@H_403_10@组件@H_403_10@
准备安装@R_301_198@客户端：yum install @R_301_198@-connector-odbc.x86_64 -y;
这样会安装一些依赖包主要是
@R_301_198@-connector*.x86_64.rpm
unixODBC*.x86_64.rpm
unixODBC*.i386.rpm
[root@hch_test_121_90 ~]# yum install @R_301_198@-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 @R_301_198@-connector-odbc.x86_64 0:5.1.5r1144-7.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved =============================================================================================================================================================================================== Package Arch Version Repository Size =============================================================================================================================================================================================== Installing: @R_301_198@-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: @R_301_198@-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 : @R_301_198@-connector-odbc-5.1.5r1144-7.el6.x86_64 1/1 Verifying : @R_301_198@-connector-odbc-5.1.5r1144-7.el6.x86_64 1/1 Installed: @R_301_198@-connector-odbc.x86_64 0:5.1.5r1144-7.el6 Complete! [root@hch_test_121_90 ~]# |
5@H_403_10@、配置@H_403_10@odbc-@R_301_198@@H_403_10@连接@H_403_10@
[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@H_403_10@、测试@R_301_198@组件的连接：@H_403_10@
[root@hch_test_121_90 ~]# isql rmt +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ sql> |
7@H_403_10@、配置@H_403_10@oracle@H_403_10@连接@H_403_10@
进去$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
rmt@R_301_198@ = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.121.90 )(PORT = 1521)) ) (CONNECT_DATA = (SID = rmt) ) (HS = OK) ) |
8@H_403_10@、建立@H_403_10@dblink@H_403_10@
create database link tg4@R_301_198@ 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 = rmt@R_301_198@) ) (HS = OK) )'; |
测试：
sql> select * from z_whs@tg4@R_301_198@; select * from z_whs@tg4@R_301_198@ * 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 TG4@R_301_198@ sql> |
解决办法：重启lsnrctl服务
9@H_403_10@、问题@H_403_10@ORA-28511@H_403_10@
sql> select * from Z_WHS@gw@R_301_198@; 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 GW@R_301_198@ Process ID: 20358 Session ID: 11 Serial number: 46988 no rows selected sql> |
再次查看：
sql> select * from Z_WHS@gw@R_301_198@; 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 GW@R_301_198@ Process ID: 20497 Session ID: 134 Serial number: 41341 no rows selected sql> select ID,cname from Z_WHS@gw@R_301_198@; select ID,cname from Z_WHS@gw@R_301_198@ * ERROR at line 1: ORA-00904: "CNAME": invalid identifier sql> select ID from Z_WHS@gw@R_301_198@; ID ---------- 1 2 3 sql> |
为什么查询单个字段@H_403_10@ID@H_403_10@能查到记录，查询@H_403_10@*@H_403_10@查不出记录呢，查看@H_403_10@Z_WHS@H_403_10@表结构，发现有一个字段@H_403_10@cname@H_403_10@为小写，尝试改成大写字段，就可以查询出来了。@H_403_10@
@R_301_198@> 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) @R_301_198@> @R_301_198@> 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 @R_301_198@> |
# PS@H_403_10@：这个时候，@H_403_10@oracle@H_403_10@的网关对@H_403_10@@R_301_198@@H_403_10@的表是锁定状态，任何对@H_403_10@@R_301_198@@H_403_10@表的@H_403_10@dml@H_403_10@、@H_403_10@ddl@H_403_10@操作都会处于等待状态，如下所示：@H_403_10@
| 13762502 | root | localhost | test | Query | 27 | Waiting for table Metadata lock | alter table Z_WHS change `cname` CNAME varchar(16) DEFAULT NULL | |
需要在oracle的窗口退出来，释放掉通过网关对@R_301_198@的锁，才能让@R_301_198@窗口的alter语句sesseion会话得到这个表的使用权限来顺利执行完成alter操作。
BUT@H_403_10@、但是，但是可惜的是@H_403_10@alter@H_403_10@语句执行完后，@H_403_10@select cname @H_403_10@还是报一样的错误出来@H_403_10@
新添加一列数字列：
| @R_301_198@> alter table Z_2 add column NUM int; Query OK,0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 @R_301_198@> update Z_2 set NUM=1; Query OK,1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 @R_301_198@> 再通过gateway查询结果： sql> conn dw/dwys0418 Connected. sql> select ID,NUM from "Z_2"@gw@R_301_198@; ID NUM ---------- ---------- 1 1 sql> select ID,NUM,CN from "Z_2"@gw@R_301_198@; 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 GW@R_301_198@ Process ID: 21333 Session ID: 11 Serial number: 47108 no rows selected sql> |
问题依旧，等待后续进一步解决。
