Oracle 透過透明網關存取多個 SQL Server 實戰手冊

架構概覽

利用 Oracle Transparent Gateway for SQL Server (TG4MSQL)，在 Oracle 端建立 Database Link，透過 Heterogeneous Services (HS) 直接查詢 SQL Server 資料，彷彿查詢本地表。本文以雙資料庫（pubs、Northwind）為例，記錄完整部署步驟。

環境前置條件

• Oracle 9i SE/EE on Windows (IP: 192.168.0.2)
• 已安裝 Oracle Transparent Gateway for SQL Server 選項
• SQL Server 運行中，已建立測試用戶 testuser/testuser_pwd 於 pubs 與 Northwind

步驟 1：Gateway 端建立初始化檔

位置：%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

關鍵參數說明：

• HS_FDS_CONNECT_INFO：ODBC 連線字串核心，SERVER 對應 SQL Server 主機名/IP，DATABASE 對應目標資料庫。
• HS_DB_NAME：HS 內部識別名，**必須與 SID 一致**（下文 listener.ora 會用到）。
• RECOVERY 帳號：用於分散式交易復原，務必在 SQL Server 建立對應登入。

步驟 2：配置 Listener 註冊 HS 服務

編輯 %ORACLE_HOME%\network\admin\listener.ora，在 SID_LIST_LISTENER 區段新增：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =                         -- 原有 Oracle 執行個體
      (GLOBAL_DBNAME = test9)
      (ORACLE_HOME = D:\Oracle\Ora92)
      (SID_NAME = test9)
    )
    (SID_DESC =                         -- 新增：pubs gateway
      (SID_NAME = pubs)
      (ORACLE_HOME = D:\Oracle\Ora92)
      (PROGRAM = tg4msql)
    )
    (SID_DESC =                         -- 新增：northwind gateway
      (SID_NAME = northwind)
      (ORACLE_HOME = D:\Oracle\Ora92)
      (PROGRAM = tg4msql)
    )
  )

重點：PROGRAM = tg4msql 告訴 Listener 此 SID 由 TG4MSQL 程式處理，而非 Oracle 影子進程。**修改後必須重啟 Listener 服務**。

步驟 3：Oracle 端配置 TNS 別名

編輯 tnsnames.ora（Client 端與 Server 端皆需）：

pubs =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
    )
    (CONNECT_DATA = (SID = pubs))
    (HS = pubs)                         -- 關鍵：啟用 Heterogeneous Services
  )

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

測試連通性：

tnsping pubs
tnsping northwind
-- 均顯示 OK (xx 毫秒) 即成功

步驟 4：放寬 Global Names 限制並建立 DB Link

-- 系統層級關閉 global_names 檢查（避免 DB Link 名稱必須等於遠端全域名稱）
ALTER SYSTEM SET global_names = FALSE SCOPE = BOTH;

-- 建立公用 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';

步驟 5：實測查詢與坑點避雷

-- 正常查詢
SELECT * FROM stores@pubs;
SELECT * FROM region@northwind;

⚠️ 必踩的坑：雙引號識別符

SQL Server 物件名稱在 Oracle 端會變為**區分大小寫的雙引號識別符**！

-- 錯誤
SELECT zip FROM stores@pubs;           -- ORA-00904: "ZIP": invalid identifier

-- 正確
SELECT "zip" FROM stores@pubs;         -- 必須雙引號 + 原始大小寫

-- 或建立本地視圖轉換
CREATE VIEW v_pubs_stores AS
SELECT "stor_id" AS stor_id, "zip" AS zip FROM stores@pubs;

使用 SQL Navigator/Toad 反向工程可見建表語句：

CREATE TABLE stores (
  "stor_id"     CHAR(4) NOT NULL,
  "stor_name"   VARCHAR2(40),
  "zip"         CHAR(5)
) ...

維運檢查清單

結語

Transparent Gateway 讓 Oracle 以「聯邦查詢」方式整合異構資料，**零程式碼改動、即時同步、支援分散式交易**，是異構整合的首選方案。關鍵在於：初始化檔參數與 SID 名稱嚴格一致、Listener 註冊 PROGRAM=tg4msql、TNS 別名帶 HS=OK、雙引號識別符處理。掌握這幾點，部署即一次成功。