Oracle DB → **Transparent Gateway for SQL Server** (TG4MSQL) → ODBC → SQL Server
HS_FDS_CONNECT_INFO = "SERVER=YIRONG-NO1;DATABASE=jsoct"
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_RECOVERY_ACCOUNT = RECOVER
HS_FDS_RECOVERY_PWD = RECOVER
亦可用 **Migration Workbench** 圖形化產生(過程會建立 System DSN)。
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC = (SID_NAME = PLSExtProc)(ORACLE_HOME = /u01/app/oracle)(PROGRAM = extproc))
(SID_DESC = (GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle)(SID_NAME = orcl))
# Gateway for SQL Server
(SID_DESC =
(GLOBAL_DBNAME = tg4msql)
(PROGRAM = tg4msql)
(SID_NAME = tg4msql)
(ORACLE_HOME = /u01/app/oracle)
)
)
重啟 Listener:`lsnrctl reload`
TG4MSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SID = tg4msql))
(HS = OK)
)
CREATE PUBLIC DATABASE LINK oratoSql
CONNECT TO "sa" IDENTIFIED BY "lanwei"
USING 'TG4MSQL';
注意:User/Password **必須是 SQL Server 的帳密**,且區分大小寫(雙引號保留大小寫)。
SELECT * FROM sysobjects@oratoSql; -- 列出 SQL Server 物件
SELECT * FROM pubs.dbo.authors@oratoSql; -- 跨庫查詢
| 現象 | 原因 | 解法 |
|---|---|---|
| ORA-28500 / ORA-02019 | Gateway 連不上 SQL Server | 檢查 `HS_FDS_CONNECT_INFO`、ODBC DSN、防火牆 1433、SQL Server 允許遠端連線 |
| ORA-01017 | DB Link 帳密錯誤 | 確認 SQL Server 帳號/密碼、大小寫、雙引號 |
| 中文亂碼 | NLS/字碼頁不匹配 | Gateway `HS_NLS_NCHAR`、`HS_LANGUAGE` 設定;SQL Server 校對集 |
| 分散式交易失敗 | 缺 Recovery 帳號 | 確實設定 `HS_FDS_RECOVERY_ACCOUNT/PWD` 並建立對應 SQL Server 登入 |
Transparent Gateway 讓 Oracle 以 **透明 SQL** 直接存取 SQL Server,關鍵在 **Gateway 安裝 → inittg4msql.ora → Listener 註冊 SID → tnspnames.ora (HS=OK) → DB Link (SQL Server 帳密)** 五步驟。建議先在測試環境跑通 `SELECT * FROM dual@oratoSql` 再推生產。