Oracle 透明閘道同時存取多個 SQL Server 實戰：配置、DB Link、雙引號陷阱全解析

前言

企業異構環境常需 Oracle 同時存取多個 SQL Server 資料庫。Oracle Transparent Gateway for SQL Server (TG4MSQL) 提供原生解決方案。本文以雙資料庫 (pubs、Northwind) 為例，完整記錄 Gateway 安裝、init.ora 配置、Listener 註冊、TNSNAMES 設定、Global Names 參數、DB Link 建立、查詢驗證，並深度解析「欄位名雙引號」這一遷移必踩的坑。

架構總覽

Oracle Client → Oracle Server (Gateway) → SQL Server (pubs) 
                                    → SQL Server (Northwind)
Gateway 運行於 Oracle Server 同一台 Windows 機器 (IP: 192.168.0.2)

一、Gateway 端配置 (Windows, IP: 192.168.0.2)

1. 安裝 Transparent Gateway for SQL Server

• Oracle 9i Standard/Enterprise Edition 安裝時選擇 Oracle Transparent Gateway → Microsoft SQL Server
• 安裝目錄：%ORACLE_HOME%\tg4msql\

2. 建立各資料庫專用 init.ora

路徑：%ORACLE_HOME%\tg4msql\admin\

initpubs.ora：

HS_FDS_CONNECT_INFO = "SERVER=sqlserver_hostname;DATABASE=pubs"
HS_DB_NAME = pubs
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_RECOVERY_ACCOUNT = RECOVER
HS_FDS_RECOVERY_PWD = RECOVER

initnorthwind.ora：

HS_FDS_CONNECT_INFO = "SERVER=sqlserver_hostname;DATABASE=Northwind"
HS_DB_NAME = Northwind
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_RECOVERY_ACCOUNT = RECOVER
HS_FDS_RECOVERY_PWD = RECOVER

3. 修改 Listener.ora 註冊 Gateway SID

路徑：%ORACLE_HOME%\network\admin\listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =                    -- 原有 Oracle 實例
      (GLOBAL_DBNAME = test9)
      (ORACLE_HOME = d:\Oracle\Ora92)
      (SID_NAME = test9)
    )
    (SID_DESC =                    -- Gateway: pubs
      (SID_NAME = pubs)
      (ORACLE_HOME = d:\Oracle\Ora92)
      (PROGRAM = tg4msql)
    )
    (SID_DESC =                    -- Gateway: northwind
      (SID_NAME = northwind)
      (ORACLE_HOME = d:\Oracle\Ora92)
      (PROGRAM = tg4msql)
    )
  )

關鍵：PROGRAM = tg4msql 告訴 Listener 此 SID 由 Gateway 程式處理。

4. 重啟 Listener 服務

LSNRCTL> STOP
LSNRCTL> START

每新增一個 SQL Server 資料庫，皆需重啟 Listener。

二、Oracle Server 端配置 (可同機或異機)

1. TNSNAMES.ORA 新增連線描述

pubs =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = pubs)
    )
    (HS = pubs)
  )

northwind =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = northwind)
    )
    (HS = northwind)
  )

關鍵：(HS = pubs) 標記此為異構服務連線。

2. 測試連線

TNSPING pubs
TNSPING northwind

預期輸出：OK (20 msec)

3. 設定 GLOBAL_NAMES = FALSE

ALTER SYSTEM SET GLOBAL_NAMES = FALSE;

避免 DB Link 名稱必須等於遠端 Global Name 的限制，提升彈性。

4. 建立 Public Database Link

CREATE PUBLIC DATABASE LINK pubs
  CONNECT TO testuser IDENTIFIED BY testuser_pwd USING 'pubs';

CREATE PUBLIC DATABASE LINK northwind
  CONNECT TO testuser IDENTIFIED BY testuser_pwd USING 'northwind';

假設 SQL Server 兩資料庫皆有相同帳號 testuser/testuser_pwd。

三、查詢驗證與雙引號陷阱

基本查詢

SELECT * FROM stores@pubs;
SELECT * FROM region@northwind;

⚠️ 關鍵坑點：欄位名帶雙引號

Oracle 透過 Gateway 查詢 SQL Server 時，SELECT * 回傳的欄位名會被雙引號包裹 (保留 SQL Server 大小寫)。

-- 建表複製
CREATE TABLE stores AS SELECT * FROM stores@pubs;

-- 直接查詢欄位 → 錯誤
SELECT zip FROM stores;
-- ORA-00904: "ZIP": invalid identifier

-- 必須加雙引號
SELECT "zip" FROM stores;
-- 正常回傳

檢視建表 DDL 確認

CREATE TABLE stores (
  "stor_id"     CHAR(4) NOT NULL,
  "stor_name"   VARCHAR2(40),
  "stor_address" VARCHAR2(40),
  "city"        VARCHAR2(20),
  "state"       CHAR(2),
  "zip"         CHAR(5)
) ...

所有欄位名均為小寫並帶雙引號。

四、解決雙引號問題的三種策略

建議：策略 1 + 2 結合

1. 立即建立視圖層隔離應用
2. 規劃維護窗口重建核心表去除雙引號
3. 新開發規範：Gateway 同步表統一建立視圖

五、總結：多 SQL Server Gateway 部署清單

1. Gateway 端：%ORACLE_HOME%\tg4msql\admin\init.ora 各一份
2. Listener.ora：每資料庫一個 SID_DESC (SID_NAME=, PROGRAM=tg4msql)
3. 新增資料庫 → 重啟 Listener
4. Oracle 端：TNSNAMES 加入 (HS = ) 項目
5. GLOBAL_NAMES = FALSE
6. 建立 Public DB Link
7. 驗證 TNSPING、查詢、建表
8. 處理欄位名雙引號問題 (視圖/重建)

結語

Oracle Transparent Gateway 讓異構查詢變得透明自然，但「透明」不代表「無差異」。欄位名大小寫保留導致的雙引號問題，是 Oracle 存取 SQL Server/MySQL/PostgreSQL 等異構源的通用特徵。掌握 init.ora、Listener.ora (PROGRAM=tg4msql)、TNSNAMES (HS=...) 三大配置鐵三角，並預先規劃欄位名正規化策略，即可構建穩定、可維護的異構資料整合平台。