合 Oracle通过Gateways透明网关连接到SQL Server数据库（dblink）
Tags： OracleSQL ServerMySQLGateways透明网关
👉 本文共约3650个字，系统预计阅读时间或需14分钟。
目录
简介
在企业里，通常可能有多种数据源，并且他们是异构的。所谓的异构就是说他们是不同的产品，例如: Oracle Database, MS SQL Server, IBM DB2, Sybase ASE, MySQL, Postgre SQL, Excel, XML, Txt或者CSV等用于存放数据的产品或者文件。
oracle 透明网关（Transparent gateways）是Oracle连接异构数据库提供的一种技术。通过Gateway，可以在Oracle里透明的访问其他不同的数据库，如SQL Server, DB2, Sybase等等，就像远程Oracle数据库一样。因此透明网关也是Oracle数据仓库和数据迁移中的一个重要组成部分。透明网关的体系结构也很简单，在ORACLE和SQL SERVER之间使用ORACLE透明网关服务器实现互连互通，其中透明网关服务器可以与ORACLE或SQL SERVER数据库在同一台主机上，也可以是在独立的一台主机上。
配置后的sql查询的处理流程如下：
下载
安装包和数据库的安装包在一块，最新的软件下载：https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
历史版本下载：https://edelivery.oracle.com
历史版本下载需要登录Oracle账号，这个不需要mos即可，自己注册一个就行。
然后搜索“12.2.0.1 gateway”，
然后点击找到的项目，加入下载目录，然后点击Continue：
由于安装网关需要有数据库作为基础，所以这里带出来2个软件，但是我DB已经安装过了，所以，这里只下载Gateways就行：
点击Download会下载如下的下载管理器：
打开即可：
下一步会自动下载：
还能看到下载性能：
还能设置限速：
等待下载完成即可。
安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | useradd -g oinstall -G oinstall,dba gateway && echo "lhr" | passwd gateway --stdin cat >> /home/gateway/.bash_profile <<"EOF" export ORACLE_HOME=/u01/app/gateway export TNS_ADMIN=$ORACLE_HOME/dg4msql/admin export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH EOF echo xfce4-session > /home/gateway/.xsession chmod +x /home/gateway/.xsession service xrdp restart unzip LINUX.X64_12.2.0.1_gateways.zip cd gateways |
图形界面安装
需要配置网关的自己的监听，注意端口不能重复即可：
执行root.sh脚本：
静默安装
1 2 3 4 5 6 7 8 9 10 11 12 | ./runInstaller -silent -ignorePrereqFailure -ignoreSysPrereqs -showProgress -waitforcompletion \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=/u01/app/oraInventory \ ORACLE_BASE=/u01/app/ \ ORACLE_HOME=/u01/app/gateway \ oracle.install.tg.customComponents=oracle.rdbms.tg4msql:12.2.0.1.0,oracle.rdbms.hsodbc:12.2.0.1.0 \ oracle.install.tg.msqlConStr=192.168.66.236,1433,MSSQLSERVER,LHRDB -- root用户 /u01/app/gateway/root.sh netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER -lisport 1525 |
执行过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | [gateway@lhrora1221 gateways]$ ./runInstaller -silent -ignorePrereqFailure -ignoreSysPrereqs -showProgress -waitforcompletion \ > UNIX_GROUP_NAME=oinstall \ > INVENTORY_LOCATION=/u01/app/oraInventory \ > ORACLE_BASE=/u01/app/ \ > ORACLE_HOME=/u01/app/gateway \ > oracle.install.tg.customComponents=oracle.rdbms.tg4msql:12.2.0.1.0,oracle.rdbms.hsodbc:12.2.0.1.0 \ > oracle.install.tg.msqlConStr=192.168.66.236,1433,MSSQLSERVER,LHRDB Starting Oracle Universal Installer... Checking Temp space: must be greater than 415 MB. Actual 368199 MB Passed Checking swap space: must be greater than 150 MB. Actual 10546 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-03-05_03-52-10PM. Please wait ...[WARNING] [INS-32056] The specified Oracle Base contains the existing Central Inventory location: /u01/app/oraInventory. ACTION: Oracle recommends that the Central Inventory location is outside the Oracle Base directory. Specify a different location for the Oracle Base. [WARNING] [INS-13014] Target environment does not meet some optional requirements. CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2022-03-05_03-52-10PM.log ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/installActions2022-03-05_03-52-10PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually. You can find the log of this install session at: /u01/app/oraInventory/logs/installActions2022-03-05_03-52-10PM.log Prepare in progress. .................................................. 8% Done. Prepare successful. Copy files in progress. .................................................. 14% Done. .................................................. 20% Done. .................................................. 25% Done. .................................................. 30% Done. .................................................. 36% Done. .................................................. 42% Done. .................................................. 47% Done. .................................................. 52% Done. .................................................. 57% Done. .................................................. 64% Done. .................................................. 69% Done. .................................................. 74% Done. .................... Copy files successful. Link binaries in progress. Link binaries successful. Setup files in progress. Setup files successful. Setup Inventory in progress. Setup Inventory successful. Finish Setup successful. The installation of Oracle Database Gateways was successful. Please check '/u01/app/oraInventory/logs/silentInstall2022-03-05_03-52-10PM.log' for more details. Oracle Gateway Configuration in progress. Oracle Net Configuration Assistant in progress. .................................................. 95% Done. Oracle Net Configuration Assistant failed. [WARNING] [INS-32091] Software installation was successful. But some configuration assistants failed, were cancelled or skipped. ACTION: Refer to the logs or contact Oracle Support Services. [gateway@lhrora1221 admin]$ netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER -lisport 1525 Parsing command line arguments: Parameter "silent" = true Parameter "responsefile" = /u01/app/gateway/assistants/netca/netca.rsp Parameter "instype" = custom Parameter "listener" = LISTENER Parameter "lisport" = 1525 Done parsing command line arguments. Oracle Net Services Configuration: Configuring Listener:LISTENER Listener configuration complete. Oracle Net Listener Startup: Running Listener Control: /u01/app/gateway/bin/lsnrctl start LISTENER Listener Control complete. Listener started successfully. Profile configuration complete. Oracle Net Services configuration successful. The exit code is 0 [test@lhrora1221 admin]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2022 16:02:02 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lhrora1221)(PORT=1525))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 05-MAR-2022 16:01:56 Uptime 0 days 0 hr. 0 min. 6 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/gateway/dg4msql/admin/listener.ora Listener Log File /u01/app/gateway/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhrora1221)(PORT=1525))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525))) The listener supports no services The command completed successfully |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
Oracle 21c 通过gateway 连接 postgresql研究过没。我这正常配置，总是报错：
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
{08001,NativeErr = 101}
ORA-02063: 紧接着 4 lines (起自 HGDB)
你这个报错看着像是PG连接的问题，可以生成trace来详细分析，参考：https://www.xmmup.com/zaioraclezhongtongguodblinkfangwenpgshujuku.html
同样的配置Oracle11g、12c，直到19c都可以用，就到了21c不行了。不知道是不是对odbc驱动要求高，最新的psqlodbc13.2是不是不能满足了，得等postgresql方出对应的odbc驱动。
若Oracle是21c，那网关的配置文件中应该去掉参数：HS_NLS_NCHAR和HS_LANGUAGE就可以了
Oracle 21c连接PG可以参考：https://www.xmmup.com/zaioraclezhongtongguodblinkfangwenpgshujuku.html