简介
在企业里，通常可能有多种数据源，并且他们是异构的。所谓的异构就是说他们是不同的产品，例如: 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就行：
image-20220305091445233
image-20220305091720746
点击Download会下载如下的下载管理器：
打开即可：
下一步会自动下载：
还能看到下载性能：
还能设置限速：
等待下载完成即可。
安装
1useradd -g oinstall -G oinstall,dba gateway && echo "lhr" | passwd gateway --stdin
2
3
4cat >> /home/gateway/.bash_profile UNIX_GROUP_NAME=oinstall
3> INVENTORY_LOCATION=/u01/app/oraInventory
4> ORACLE_BASE=/u01/app/
5> ORACLE_HOME=/u01/app/gateway
6> oracle.install.tg.customComponents=oracle.rdbms.tg4msql:12.2.0.1.0,oracle.rdbms.hsodbc:12.2.0.1.0
7> oracle.install.tg.msqlConStr=192.168.66.236,1433,MSSQLSERVER,LHRDB
8Starting Oracle Universal Installer...
9
10Checking Temp space: must be greater than 415 MB. Actual 368199 MB Passed
11Checking swap space: must be greater than 150 MB. Actual 10546 MB Passed
12Preparing 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.
13 ACTION: Oracle recommends that the Central Inventory location is outside the Oracle Base directory. Specify a different location for the Oracle Base.
14[WARNING] [INS-13014] Target environment does not meet some optional requirements.
15 CAUSE: Some of the optional prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/installActions2022-03-05_03-52-10PM.log
16 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.
17You can find the log of this install session at:
18 /u01/app/oraInventory/logs/installActions2022-03-05_03-52-10PM.log
19
20Prepare in progress.
21.................................................. 8% Done.
22
23Prepare successful.
24
25Copy files in progress.
26.................................................. 14% Done.
27.................................................. 20% Done.
28.................................................. 25% Done.
29.................................................. 30% Done.
30.................................................. 36% Done.
31.................................................. 42% Done.
32.................................................. 47% Done.
33.................................................. 52% Done.
34.................................................. 57% Done.
35.................................................. 64% Done.
36.................................................. 69% Done.
37.................................................. 74% Done.
38....................
39Copy files successful.
40
41Link binaries in progress.
42
43Link binaries successful.
44
45Setup files in progress.
46
47Setup files successful.
48
49Setup Inventory in progress.
50
51Setup Inventory successful.
52
53Finish Setup successful.
54The installation of Oracle Database Gateways was successful.
55Please check '/u01/app/oraInventory/logs/silentInstall2022-03-05_03-52-10PM.log' for more details.
56
57Oracle Gateway Configuration in progress.
58
59Oracle Net Configuration Assistant in progress.
60.................................................. 95% Done.
61
62Oracle Net Configuration Assistant failed.
63[WARNING] [INS-32091] Software installation was successful. But some configuration assistants failed, were cancelled or skipped.
64 ACTION: Refer to the logs or contact Oracle Support Services.
65
66
67[gateway@lhrora1221 admin]$ netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER -lisport 1525
68
69Parsing command line arguments:
70 Parameter "silent" = true
71 Parameter "responsefile" = /u01/app/gateway/assistants/netca/netca.rsp
72 Parameter "instype" = custom
73 Parameter "listener" = LISTENER
74 Parameter "lisport" = 1525
75Done parsing command line arguments.
76Oracle Net Services Configuration:
77Configuring Listener:LISTENER
78Listener configuration complete.
79Oracle Net Listener Startup:
80 Running Listener Control:
81 /u01/app/gateway/bin/lsnrctl start LISTENER
82 Listener Control complete.
83 Listener started successfully.
84Profile configuration complete.
85Oracle Net Services configuration successful. The exit code is 0
86
87[test@lhrora1221 admin]$ lsnrctl status
88
89LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2022 16:02:02
90
91Copyright (c) 1991, 2016, Oracle. All rights reserved.
92
93Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lhrora1221)(PORT=1525)))
94STATUS of the LISTENER
95------------------------
96Alias LISTENER
97Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
98Start Date 05-MAR-2022 16:01:56
99Uptime 0 days 0 hr. 0 min. 6 sec
100Trace Level off
101Security ON: Local OS Authentication
102SNMP OFF
103Listener Parameter File /u01/app/gateway/dg4msql/admin/listener.ora
104Listener Log File /u01/app/gateway/network/log/listener.log
105Listening Endpoints Summary...
106 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhrora1221)(PORT=1525)))
107 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525)))
108The listener supports no services
109The command completed successfully
配置
Oracle连接到SQL Server
配置gateway的监听
透明网关目录文件夹$TNS_ADMIN=/u01/app/gateway/dg4msql/admin
其中listener.ora便是监听文件，配置他即可。
1[gateway@lhrora1221 ~]$ cd $TNS_ADMIN
2[gateway@lhrora1221 admin]$ echo $TNS_ADMIN
3/u01/app/gateway/dg4msql/admin
4[gateway@lhrora1221 admin]$ ll
5total 40
6-rw-r--r-- 1 gateway oinstall 11120 Dec 18 2013 dg4msql_cvw.sql
7-rw-r--r-- 1 gateway oinstall 746 Jun 9 2007 dg4msql_tx.sql
8-rw-r--r-- 1 gateway oinstall 365 Mar 5 16:08 initdg4msql.ora
9-rw-r--r-- 1 gateway oinstall 315 Mar 5 16:18 listener.ora
10-rw-r--r-- 1 gateway oinstall 388 Mar 5 16:08 listener.ora.sample
11-rw-r--r-- 1 gateway oinstall 179 Mar 5 16:18 sqlnet2203058AM1818.bak
12-rw-r--r-- 1 gateway oinstall 179 Mar 5 16:18 sqlnet.ora
13-rw-r--r-- 1 gateway oinstall 244 Mar 5 16:08 tnsnames.ora.sample
14
15
16-- vi listener.ora
17LISTENER =
18 (DESCRIPTION_LIST =
19 (DESCRIPTION =
20 (ADDRESS = (PROTOCOL = TCP)(HOST = lhrora1221)(PORT = 1525))
21 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
22 )
23 )
24
25SID_LIST_LISTENER =
26 (SID_LIST =
27 (SID_DESC =
28 (SID_NAME = dg4msql)
29 (PROGRAM = dg4msql)
30 (ORACLE_HOME = /u01/app/gateway)
31 )
32 )
33
34# (PROGRAM = dg4msql) 是固定的，只需要修改 SID_NAME
35# 注意，配置了透明网关的监听后，需要重启一下监听服务。
36
37[gateway@lhrora1221 admin]$ lsnrctl status
38
39LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2022 16:55:45
40
41Copyright (c) 1991, 2016, Oracle. All rights reserved.
42
43Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lhrora1221)(PORT=1525)))
44STATUS of the LISTENER
45------------------------
46Alias LISTENER
47Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
48Start Date 05-MAR-2022 16:41:10
49Uptime 0 days 0 hr. 14 min. 35 sec
50Trace Level off
51Security ON: Local OS Authentication
52SNMP OFF
53Listener Parameter File /u01/app/gateway/dg4msql/admin/listener.ora
54Listener Log File /u01/app/diag/tnslsnr/lhrora1221/listener/alert/log.xml
55Listening Endpoints Summary...
56 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhrora1221)(PORT=1525)))
57 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525)))
58Services Summary...
59Service "dg4msql" has 1 instance(s).
60 Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
61The command completed successfully
配置gateway数据库参数
透明网关目录文件夹$TNS_ADMIN=/u01/app/gateway/dg4msql/admin
Admin文件夹下默认存在安装透明网关时的数据库文件initdg4msql.ora。
数据文件命名规则：init + SID(SID就是上一步中配置的SID_NAME)，需要多个连接就建多个文件即可。
1[gateway@lhrora1221 admin]$ more initdg4msql.ora
2# This is a customized agent init file that contains the HS parameters
3# that are needed for the Database Gateway for Microsoft SQL Server
4
5#
6# HS init parameters
7#
8HS_FDS_CONNECT_INFO=[192.168.66.236]:1433//LHRDB
9# alternate connect format is hostname/serverinstance/databasename
10HS_FDS_TRACE_LEVEL=OFF
11HS_FDS_RECOVERY_ACCOUNT=RECOVER
12HS_FDS_RECOVERY_PWD=RECOVER
只要修改HS_FDS_CONNECT_INFO参数就可以了。格式是：[hostname:port]/serverinstance/databasename
，其中hostname是机器名称或IP，PORT是SQL Server的端口号，SQL Server2005默认为1433.serverinstance是SQL Server的实例名，一般空着就行。Databasename是SQL Server的数据库名。因为我们在安装过程中指定了主机名和数据库名，这里已经有信息了。
配置oracle服务器的服务名配置文件tnsnames.ora
1cd /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin
2cat >> tnsnames.ora select COUNT(*) from test@dbl_mssql;
12select COUNT(*) from test@dbl_mssql
13 *
14ERROR at line 1:
15ORA-00942: table or view does not exist
16[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]TEST' 42S02,NativeErr = 208}[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]ԤHY000,NativeErr = 8180}
17ORA-02063: preceding 2 lines from DBL_MSSQL
18
19
20SYS@lhrsdb> select COUNT(*) from "test"@dbl_mssql;
21
22 COUNT(*)
23----------
24 5134
25
26SYS@lhrsdb> select name from "test"@dbl_mssql where rownum
