Oracle Transparent Gateway 多實例配置：單機訪問多個 SQL Server 數據庫（pubs/Northwind）完整實施

前置條件

• Oracle 9i 安裝時選擇 Transparent Gateway for Microsoft SQL Server。
• Windows 平台，IP 假設 192.168.0.2。

1. 創建各目標庫初始化文件

路徑：$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

2. 配置 Listener.ora 註冊多 SID

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (GLOBAL_DBNAME=test9)(ORACLE_HOME=d:\oracle\ora92)(SID_NAME=test9))
    (SID_DESC = (SID_NAME=pubs)(ORACLE_HOME=d:\oracle\ora92)(PROGRAM=tg4msql))
    (SID_DESC = (SID_NAME=northwind)(ORACLE_HOME=d:\oracle\ora92)(PROGRAM=tg4msql))
  )

• Gateway SID 必須設置 PROGRAM=tg4msql 指定異構服務程序。
• GLOBAL_DBNAME 僅本地 Oracle 實例需要，Gateway SID 不需設置。

3. 配置 TNSNAMES.ORA 定義服務名

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

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

HS=OK 告訴 Oracle 這是異構服務，需通過 Gateway 處理。

4. 創建 Database Link 驗證

CREATE DATABASE LINK pubs_link
  CONNECT TO "sa" IDENTIFIED BY "password"
  USING 'PUBS_DB';

CREATE DATABASE LINK northwind_link
  CONNECT TO "sa" IDENTIFIED BY "password"
  USING 'NORTHWIND_DB';

-- 測試
SELECT * FROM "pubs"."dbo"."authors"@pubs_link;
SELECT * FROM "Northwind"."dbo"."Customers"@northwind_link;

• SQL Server 表名區分大小寫，雙引號保持原樣。
• Gateway 將 Oracle SQL 翻譯為 T-SQL，支援 SELECT/INSERT/UPDATE/DELETE、存儲過程調用。

常見問題

• ORA-28500：檢查 HS_FDS_CONNECT_INFO 服務器名/實例名、防火牆 1433 端口。
• ORA-28511：listener.ora 中 Gateway SID 缺少 PROGRAM=tg4msql。
• 字符集亂碼：設置 HS_LANGUAGE=AL32UTF8 (初始化文件) 或 NLS_LANG 環境變量。