开始本文的前提是:
1. 有一个MS Windows的服务器
2. 该服务器上安装了MS SQL Server

从甲骨文的官方网站获取GATEWAY在MS Windows下的安装介质:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

安装过程是图形化的,一步步的下一步就好:

配置参数:
参数文件位于:
MS SQL Server:C:\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora
样例文件:C:\product\11.2.0\tg_1\inventory\Templates\dg4msql\admin\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.92.170]/MSSQLSERVER/THEWORLD

HS_FDS_CONNECT_INFO="192.168.92.170;DATABASE=THEWORLD"

HS_FDS_TRACE_LEVEL=debug

#HS_FDS_RECOVERY_ACCOUNT=sa
#HS_FDS_RECOVERY_PWD=【SA的口令】

HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


修改监听器配置文件:
位于:C:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora
修改为:

# listener.ora Network Configuration File: C:\product\11.2.0\tg_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  ( SID_LIST = 
    ( SID_DESC = 
      (SID_NAME=dg4msql)
      (ORACLE_HOME=C:\product\11.2.0\tg_1)
      (PROGRAM=dg4msql)

    )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.170)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = C:\product\11.2.0\tg_1

重启或重新加载监听器:

PS C:\Users\Administrator> lsnrctl stop

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 24-5月 -2017 14:31:33

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.92.170)(PORT=1521)))
命令执行成功
PS C:\Users\Administrator>
PS C:\Users\Administrator> lsnrctl start

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 24-5月 -2017 14:31:38

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

启动tnslsnr: 请稍候...

TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
系统参数文件为C:\product\11.2.0\tg_1\network\admin\listener.ora
写入c:\product\11.2.0\tg_1\diag\tnslsnr\WIN-QPBSFJPBRNU\listener\alert\log.xml的日志信息
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.170)(PORT=1521)))
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.92.170)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
启动日期                  24-5月 -2017 14:31:41
正常运行时间              0 天 0 小时 0 分 3 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
监听程序参数文件          C:\product\11.2.0\tg_1\network\admin\listener.ora
监听程序日志文件          c:\product\11.2.0\tg_1\diag\tnslsnr\WIN-QPBSFJPBRNU\listener\alert\log.xml
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.170)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
服务摘要..
服务 "dg4msql" 包含 1 个实例。
  实例 "dg4msql", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功
PS C:\Users\Administrator>
PS C:\Users\Administrator>

然后,在另外一台装了Oracle的服务器上配置TNSNAMES,以便于可以访问上面配置的SQL Server:

[oracle@oracle-me database]$ cd $ORACLE_HOME/network/admin
[oracle@oracle-me admin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@oracle-me admin]$ 
[oracle@oracle-me admin]$ ls
listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora
[oracle@oracle-me admin]$ 
[oracle@oracle-me admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LENKA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-me)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = lenka)
    )
  )

dg4msql =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.170)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = dg4msql)
    )
    (HS=OK)
  )
[oracle@oracle-me admin]$ 

测试一下:

[oracle@oracle-me admin]$ tnsping dg4msql

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-MAY-2017 23:58:59

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.92.170)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = dg4msql)) (HS=OK))
OK (0 msec)
[oracle@oracle-me admin]$ 

然后,创建DBLINK,并查询一下:

[oracle@oracle-me admin]$ ps -ef | grep ora_
oracle     7244      1  0 23:49 ?        00:00:00 ora_pmon_lenka
oracle     7246      1  0 23:49 ?        00:00:00 ora_psp0_lenka
oracle     7248      1  0 23:49 ?        00:00:00 ora_vktm_lenka
oracle     7252      1  0 23:49 ?        00:00:00 ora_gen0_lenka
oracle     7254      1  0 23:49 ?        00:00:00 ora_diag_lenka
oracle     7256      1  0 23:49 ?        00:00:00 ora_dbrm_lenka
oracle     7258      1  0 23:49 ?        00:00:00 ora_dia0_lenka
oracle     7260      1  0 23:49 ?        00:00:00 ora_mman_lenka
oracle     7262      1  0 23:49 ?        00:00:00 ora_dbw0_lenka
oracle     7264      1  0 23:49 ?        00:00:01 ora_lgwr_lenka
oracle     7266      1  0 23:49 ?        00:00:00 ora_ckpt_lenka
oracle     7268      1  0 23:49 ?        00:00:00 ora_smon_lenka
oracle     7270      1  0 23:49 ?        00:00:00 ora_reco_lenka
oracle     7272      1  0 23:49 ?        00:00:00 ora_mmon_lenka
oracle     7274      1  0 23:49 ?        00:00:00 ora_mmnl_lenka
oracle     7276      1  0 23:49 ?        00:00:00 ora_d000_lenka
oracle     7278      1  0 23:49 ?        00:00:00 ora_s000_lenka
oracle     7286      1  0 23:49 ?        00:00:00 ora_qmnc_lenka
oracle     7319      1  0 23:49 ?        00:00:00 ora_cjq0_lenka
oracle     7390      1  0 23:50 ?        00:00:00 ora_q000_lenka
oracle     7392      1  0 23:50 ?        00:00:00 ora_smco_lenka
oracle     7394      1  0 23:50 ?        00:00:00 ora_q001_lenka
oracle     7396      1  0 23:50 ?        00:00:00 ora_w000_lenka
oracle    10677      1  0 23:55 ?        00:00:00 ora_w001_lenka
oracle    10679      1  0 23:55 ?        00:00:00 ora_w002_lenka
oracle    10996      1  0 23:58 ?        00:00:00 ora_w003_lenka
oracle    10999      1  0 23:58 ?        00:00:00 ora_w004_lenka
oracle    11018      1  0 23:58 ?        00:00:00 ora_w005_lenka
oracle    11021      1  0 23:58 ?        00:00:00 ora_w006_lenka
oracle    11023      1  0 23:58 ?        00:00:00 ora_w007_lenka
oracle    11130   2668  0 23:59 pts/1    00:00:00 grep ora_
[oracle@oracle-me admin]$ 
[oracle@oracle-me admin]$ 
[oracle@oracle-me admin]$ export ORACLE_SID=lenka
[oracle@oracle-me admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 24 00:00:08 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> create database link dg4msql connect to sa identified by "Abcd!234" using 'dg4msql';

Database link created.

SQL> col people_name for a26
SQL> col people_location for a26
SQL> col people_comment for a20
SQL> 
SQL> set linesize 400
SQL> 
SQL> select * from people@dg4msql;

 PEOPLE_NO PEOPLE_NAME                PEOPLE_LOCATION            PEOPLE_COMMENT
---------- -------------------------- -------------------------- --------------------
         0 Alienware                  America                    Computer
         1 Zen                        Indian                     Belive
         2 Lenka                      Chinese                    Angel

SQL> 

可以看到,从Oracle得到了MS SQL Server的内容:

——————————————
Done。

1
说点什么

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
0 Comment authors
Oracle 11g Gateway + MySQL – Adamhuan's Data Center – 【逻辑驱动数据】 Recent comment authors

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

  Subscribe  
提醒
trackback

[…] Oracle Gateway + Microsoft SQL Server […]