Oracle 使用dblink连接SQL Server

最新推荐文章于 2024-01-22 10:25:36 发布

原创最新推荐文章于 2024-01-22 10:25:36 发布 · 3.7k 阅读

2 ·

CC 4.0 BY-SA版权

Oracle 同时被 2 个专栏收录

28 篇文章

订阅专栏

SQL

9 篇文章

订阅专栏

本文详细介绍了如何从Oracle官网下载并安装数据库网关，通过具体步骤演示了使用Oracle账号上传、解压和安装过程。涵盖了环境变量设置、监听配置、tnsnames.ora与listener.ora文件修改、监听重启、DBLINK创建及链接测试等关键环节。

1.下载gateway文件

Oracle官网下载数据库版本对应的gateway

2.上传至服务器，解压出来（用Oracle账号解压）

unzip linux.x64_11gR2_gateways.zip

3.解压安装

Oracle用户下操作：

mkdir - /opt/app/gateway

[oracle@OAapp02 ~]$ cd gateways/
[oracle@OAapp02 gateways]$ ll
total 32
drwxr-xr-x.  6 oracle oinstall 4096 Aug 17  2009 doc
drwxr-xr-x.  4 oracle oinstall 4096 Aug 15  2009 install
drwxrwxr-x.  4 oracle oinstall 4096 Aug 15  2009 legacy
drwxrwxr-x.  2 oracle oinstall 4096 Aug 15  2009 response
-rwxr-xr-x.  1 oracle oinstall 3226 Aug 15  2009 runInstaller
drwxr-xr-x. 13 oracle oinstall 4096 Aug 15  2009 stage
-rw-r--r--.  1 oracle oinstall 6285 Aug 18  2009 welcome.html
[oracle@OAapp02 gateways]$ export DISPLAY=192.168.86.93:0.0
[oracle@OAapp02 gateways]$ ./runInstaller 
Starting Oracle Universal Installer...
	
Checking Temp space: must be greater than 120 MB.   Actual 68185 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 58891 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-06-24_11-21-00AM. Please wait ...[oracle@OAapp02 gateways]$ Oracle Universal Installer, Version 11.2.0.1.0 Production
copyright (C) 1999, 2009, Oracle. All rights reserved.

[root@OAapp02 ~]# /opt/app/gateway/root.sh
Running Oracle 11g root.sh script...
	
The following environment variables are set as:
	ORACLE_OWNER= oracle
	ORACLE_HOME=  /opt/app/gateway
	
Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
	[n]: 
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
	[n]: 
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
	[n]: 
	
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

4.需要创建监听，点击cancel，手动创建监听

5.查看这个文件的连接信息

[oracle@OAapp02 admin]$ cat 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=[10.0.13.101]:1433//WTS_DELI
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
	
[oracle@OAapp02 admin]$ pwd
/opt/app/gateway/dg4msql/admin

6.在listener.ora 加入以下内容

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

7.在tnsnames.ora加入以下内容

dg4msql =
	(DESCRIPTION =
	   (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.112.33)(PORT = 1521))
	   (CONNECT_DATA=(SID=dg4msql))
	   (HS=OK)
)

8.重新加载监听

[oracle@OAapp02 admin]$ lsnrctl reload
	
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-JUN-2020 11:47:04
	
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
	
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.112.33)(PORT=1521)))
The command completed successfully
[oracle@OAapp02 admin]$ 
[oracle@OAapp02 admin]$ 
[oracle@OAapp02 admin]$ lsnrctl status
	
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-JUN-2020 11:47:12
	
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
	
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.112.33)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                14-JAN-2020 08:03:44
Uptime                    162 days 3 hr. 43 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/app/oracle/diag/tnslsnr/OAapp02/listener/alert/log.xml
Listening Endpoints Summary...
	(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.112.33)(PORT=1521)))
Services Summary...
Service "dg4msql" has 1 instance(s).
	Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
	Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
	Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

9.创建DBLINK链接

create database link wtsdb connect to sa identified by "password" using 'dg4msql';

10.测试链接

SQL> show user;
USER is "ECOLOGY"
	
SQL> select count(*) from "dbo"."stock_data"@wtsdb;
	
 COUNT(*)
----------
 36282

参考文章：

https://www.cnblogs.com/xqzt/p/5688659.html
https://wenku.baidu.com/view/104739b365ce050876321347.html