实验目的：

通过Oracle Database Gateways使linux7.7下oracle19c rac的dbcenter库可创建dblink连接windows下sqlserver2008 r2的adjyc库，实现直接可查询到sqlserver下的指定表数据。

数据库版本	ORACLE19.8RAC	SQLSERVER2008R2
IP	192.168.52.183/184	192.168.52.152
数据库	dbcenter	adjyc
端口	1521 (确保oracle主机可以telnet 192.168.52.152 1433端口通)	1433
用户密码	jyc/jyc	jyc/jyc
测试表		ip

Oracle Database Gateways 19c for linux x86-64介质下载：

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html#license-lightbox

 

官方参考文档：

https://docs.oracle.com/en/database/oracle/oracle-database/19/otgis/config-sqlserver-gateway.html#GUID-C7E48634-3FB1-4401-8B54-D1A4BC92B1E5

SQLSERVER2008R2数据库介质下载：

https://msdn.itellyou.cn/

ed2k://|file|cn_sql_server_2008_r2_enterprise_x86_x64_ia64_dvd_522233.iso|4662884352|1DB025218B01B48C6B76D6D88630F541|/

 

1.两节点oracle用户下都安装Gateways：

[root@rac1 ~]# /oracle/app/oracle/product/19c/dbhome_1/root.sh

Performing root user operation.

 

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /oracle/app/oracle/product/19c/dbhome_1

 

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

The contents of "oraenv" have not changed. No need to overwrite.

The contents of "coraenv" have not changed. No need to overwrite.

 

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

Now product-specific root actions will be performed.

 

[oracle@rac1 dbhome_1]$ pwd

/oracle/app/oracle/product/19c/dbhome_1

[oracle@rac1 dbhome_1]$ ls -lt|head

total 116

drwxrwx--- 10 oracle oinstall  4096 Nov 26 13:17 install

drwxr-xr-x  7 oracle oinstall  4096 Nov 26 13:17 cfgtoollogs

drwxr-xr-x  8 oracle oinstall   226 Nov 26 13:17 oui

drwxr-xr-x  2 oracle oinstall  8192 Nov 26 13:17 bin

drwxr-xr-x  7 oracle oinstall    67 Nov 26 13:17 dg4msql

drwxr-x--- 15 oracle oinstall   250 Nov 26 13:17 inventory

drwxr-xr-x  2 oracle oinstall  4096 Nov 26 12:59 dbs

drwxr-xr-x  6 oracle asmadmin    56 Nov 14 12:02 admin

drwxr-xr-x  4 oracle oinstall    37 Jul 31 18:41 dataguard

[oracle@rac1 dbhome_1]$ cd dg4msql

[oracle@rac1 dg4msql]$ pwd

/oracle/app/oracle/product/19c/dbhome_1/dg4msql

[oracle@rac1 dg4msql]$ ll

total 0

drwxr-xr-x 2 oracle oinstall 128 Nov 26 13:17 admin

drwxr-xr-x 2 oracle oinstall 220 Nov 26 13:17 demo

drwxr-xr-x 4 oracle oinstall  50 Nov 26 13:17 driver

drwxr-xr-x 2 oracle oinstall  23 Nov 26 13:17 lib

drwxr-xr-x 2 oracle oinstall   6 Nov 26 13:17 log

[oracle@rac1 dg4msql]$ cd admin

[oracle@rac1 admin]$ ls

dg4msql_cvw.sql  dg4msql_tx.sql  initdg4msql.ora  listener.ora.sample  tnsnames.ora.sample

[oracle@rac1 admin]$ ll

total 28

-rw-rw-r-- 1 oracle oinstall 11120 Dec 17  2013 dg4msql_cvw.sql

-rw-rw-r-- 1 oracle oinstall   746 Jun  8  2007 dg4msql_tx.sql

-rw-rw-r-- 1 oracle oinstall   365 Nov 26 13:17 initdg4msql.ora

-rw-rw-r-- 1 oracle oinstall   411 Nov 26 13:17 listener.ora.sample

-rw-rw-r-- 1 oracle oinstall   244 Nov 26 13:17 tnsnames.ora.sample

[oracle@rac1 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.52.152]:1433//adjyc

# alternate connect format is hostname/serverinstance/databasename

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

 

2.两节点的grid用户下listener.ora添加：

SID_LIST_LISTENER =
    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
      (PROGRAM = dg4msql)
    )
  )

两节点分别重启监听。

lsnrctl stop

lsnrctl start

两节点检查服务：

[oracle@rac1 ~]$ lsnrctl status|grep dg4

Service "dg4msql" has 1 instance(s).

  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...

3.两节点的oracle用户下tnsnames.ora添加：

dg4msql =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID=dg4msql)
    )
    (HS = OK)
  )

测试:

[oracle@rac1 admin]$ tnsping dg4msql

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 26-NOV-2020 13:42:22

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))) (CONNECT_DATA = (SID=dg4msql)) (HS = OK))

OK (0 msec)

 

4.pdb库里创建dblink

pdb的tnsnames.ora中连接串：

DBCENTER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbcenter)
    )
  )

[oracle@rac1 ~]$ sqlplus jyc/jyc@dbcenter

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 26 13:36:11 2020

Version 19.8.0.0.0

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

Last Successful login time: Thu Nov 26 2020 13:25:06 +08:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL> create public database link dg4msql connect to jyc identified by jyc using 'dg4msql';

Database link created.

SQL> select count(*) from ip@dg4msql;

  COUNT(*)

----------

     70333