提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

Oracle通过Gateways透明网关连接到SQL Server数据库（dblink）

Oracleserver数据库SQL连接 通过 网关 透明
2023-06-13 09:14:44 时间

简介

在企业里，通常可能有多种数据源，并且他们是异构的。所谓的异构就是说他们是不同的产品，例如: 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会下载如下的下载管理器：

打开即可：

下一步会自动下载：

还能看到下载性能：

还能设置限速：

等待下载完成即可。

安装

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

图形话界面安装

image-20220305151831993

image-20220305152357892

需要配置网关的自己的监听，注意端口不能重复即可：

image-20220305152854169

执行root.sh脚本：

静默安装

./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

netca -silent -responsefile $ORACLE_HOME/assistants/netca/netca.rsp -instype custom -listener LISTENER -lisport 1525

执行过程：

[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连接到SQL Server

配置gateway的监听

透明网关目录文件夹$TNS_ADMIN=/u01/app/gateway/dg4msql/admin

其中listener.ora便是监听文件，配置他即可。

[gateway@lhrora1221 ~]$ cd $TNS_ADMIN
[gateway@lhrora1221 admin]$ echo $TNS_ADMIN 
/u01/app/gateway/dg4msql/admin
[gateway@lhrora1221 admin]$ ll
total 40
-rw-r--r-- 1 gateway oinstall 11120 Dec 18  2013 dg4msql_cvw.sql
-rw-r--r-- 1 gateway oinstall   746 Jun  9  2007 dg4msql_tx.sql
-rw-r--r-- 1 gateway oinstall   365 Mar  5 16:08 initdg4msql.ora
-rw-r--r-- 1 gateway oinstall   315 Mar  5 16:18 listener.ora
-rw-r--r-- 1 gateway oinstall   388 Mar  5 16:08 listener.ora.sample
-rw-r--r-- 1 gateway oinstall   179 Mar  5 16:18 sqlnet2203058AM1818.bak
-rw-r--r-- 1 gateway oinstall   179 Mar  5 16:18 sqlnet.ora
-rw-r--r-- 1 gateway oinstall   244 Mar  5 16:08 tnsnames.ora.sample


-- vi listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lhrora1221)(PORT = 1525))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
      (SID_NAME = dg4msql)
      (PROGRAM = dg4msql)
      (ORACLE_HOME = /u01/app/gateway)
    )
  )

# (PROGRAM = dg4msql) 是固定的，只需要修改 SID_NAME
# 注意，配置了透明网关的监听后，需要重启一下监听服务。

[gateway@lhrora1221 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2022 16:55:45

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:41:10
Uptime                    0 days 0 hr. 14 min. 35 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/diag/tnslsnr/lhrora1221/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lhrora1221)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525)))
Services Summary...
Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

配置gateway数据库参数

透明网关目录文件夹$TNS_ADMIN=/u01/app/gateway/dg4msql/admin

Admin文件夹下默认存在安装透明网关时的数据库文件initdg4msql.ora。

数据文件命名规则：init + SID(SID就是上一步中配置的SID_NAME)，需要多个连接就建多个文件即可。

[gateway@lhrora1221 admin]$ more initdg4msql.ora 
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.66.236]:1433//LHRDB
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_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

cd  /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin
cat >> tnsnames.ora <<"EOF"
DG4MSQL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1525))
    (CONNECT_DATA =
      (SERVICE_NAME = dg4msql)
    )
   (HS = OK)
  )
EOF


[oracle@lhrora1221 admin]$ tnsping DG4MSQL

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 05-MAR-2022 16:47:38

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1525)) (CONNECT_DATA = (SERVICE_NAME = dg4msql)) (HS = OK))
OK (0 msec)

(HS=OK) #这个很重要表示异构数据库

在oracle数据库中测试：

CREATE DATABASE LINK dbl_mssql CONNECT TO "sa" IDENTIFIED BY "lhr" USING 'DG4MSQL';
select COUNT(*) from  dbo.test@dbl_mssql;
select 1 from dual@dbl_mssql;


SYS@lhrsdb> select 1 from dual@dbl_mssql;

         1
----------
         1
SYS@lhrsdb> select COUNT(*) from  test@dbl_mssql;
select COUNT(*) from  test@dbl_mssql
                      *
ERROR at line 1:
ORA-00942: table or view does not exist
[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}
ORA-02063: preceding 2 lines from DBL_MSSQL


SYS@lhrsdb> select COUNT(*) from  "test"@dbl_mssql;

  COUNT(*)
----------
      5134

SYS@lhrsdb> select name from  "test"@dbl_mssql where rownum<=2;
select name from  "test"@dbl_mssql where rownum<=2
       *
ERROR at line 1:
ORA-00904: "NAME": invalid identifier


SYS@lhrsdb> select "name" from  "test"@dbl_mssql where rownum<=2;

name
-----------
GRANTOR
GRANTEE

执行的查询操作，表名需要带双引号，因为MySQL和SQL server默认表名是区分大小写，而oracle是不区分大小写的(oracle默认自动转换成大写)。

Oracle连接到MySQL

参考文档：

http://blog.itpub.net/26736162/viewspace-2144661/

http://blog.itpub.net/26736162/viewspace-2644037/

Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)

https://www.xmmup.com/detailed-overview-of-connecting-oracle-to-mysql-using-dg4odbc-database-link-doc-id-1320645-1.html

Oracle使用DG4ODBC数据网关连接其它非Oracle数据库，可以不用安装前边的Gateways网关，其原理图如下：

从上图可知，Oracle连接MySQL需要涉及到如下组件：DG4ODBC, ODBC Driver Manager, ODBC Driver。

配置过程如下：

-- root操作
yum install -y unixODBC unixODBC-devel mysql-connector-odbc

cat >/etc/odbc.ini <<"EOF"

[myodbc5]  
Driver = /usr/lib64/libmyodbc5w.so 
Description = Connector/ODBC 5.2 Driver DSN  
SERVER = 172.17.0.3  
PORT = 3306  
USER = root  
PASSWORD = lhr  
DATABASE = lhrdb  
OPTION = 0  
TRACE = OFF

EOF


export ODBCINI=/etc/odbc.ini
isql myodbc5 -v



-- oracle操作
cd  /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin
cat >> tnsnames.ora <<"EOF"
myodbc5 =   
  (DESCRIPTION=  
    (ADDRESS=  
        (PROTOCOL=TCP) (HOST=127.0.0.1) (PORT=1521)  
    )  
    (CONNECT_DATA=  
      (SID=myodbc5)  
    )  
    (HS=OK)  
)
EOF

-- vi listener.ora
SID_LIST_LISTENER=  
  (SID_LIST=  
    (SID_DESC=  
      (SID_NAME=myodbc5)  
      (ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1)  
      (PROGRAM=dg4odbc)  
      (ENVS=LD_LIBRARY_PATH=/usr/lib:/u01/app/oracle/product/12.2.0.1/dbhome_1/lib)  
    )   
  )


cat > $ORACLE_HOME/hs/admin/initmyodbc5.ora <<"EOF"
HS_FDS_CONNECT_INFO=myodbc5
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
EOF

lsnrctl reload
lsnrctl status
tnsping myodbc5

odbcinst -j


create   public   database  link mysqltest  connect   to   "root"  identified  by   "lhr"  using  'myodbc5' ;  
select   count(*)  from  "lhrtest"@mysqltest;

执行过程：

[root@lhrora1221 ~]# rpm -qa | grep unixODBC
unixODBC-2.3.1-14.el7.x86_64
unixODBC-devel-2.3.1-14.el7.x86_64
[root@lhrora1221 ~]# rpm -qa | grep mysql-connector-odbc
mysql-connector-odbc-5.2.5-8.el7.x86_64
[root@lhrora1221 ~]# 
[root@lhrora1221 ~]# export ODBCINI=/etc/odbc.ini
[root@lhrora1221 ~]# isql myodbc5 -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show databases;
+-----------------------------------------------------------------+
| Database                                                        |
+-----------------------------------------------------------------+
| information_schema                                              |
| canal_manager                                                   |
| lhrdb                                                           |
| mysql                                                           |
| performance_schema                                              |
| sbtest                                                          |
| sys                                                             |
+-----------------------------------------------------------------+
SQLRowCount returns 7
7 rows fetched


[oracle@lhrora1221 admin]$ sas

SQL*Plus: Release 12.2.0.1.0 Production on Sat Mar 5 17:47:45 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS@lhrsdb> select   count(*)  from  "test"@mysqltest;

  COUNT(*)
----------
         5

参考

https://blog.csdn.net/cymm_liu/article/details/38473851

https://www.cnblogs.com/BinBinGo/p/12020246.html

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/gmswn/index.html

http://blog.itpub.net/26736162/viewspace-2144661/

http://blog.itpub.net/26736162/viewspace-2644037/

https://www.cnblogs.com/kerrycode/p/8487671.html

How to Make a Silent Install of Oracle Database Gateway 12c or higher (Doc ID 2639375.1)

How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or Generic Connectivity (文档 ID 234517.1)

相关文章