Oracle 監聽器多資料庫多埠/單埠多實例配置指南

Oracle 監聽器多資料庫多埠/單埠多實例配置指南

單一資料庫伺服器支援多個 SID（如 KKMAN、ORCL）時，監聽器可採用多埠（每個 SID 獨立埠）或單埠多實例（共用 1521，透過 SERVICE_NAME 路由）兩種架構。本文提供完整 listener.ora 與 tnsnames.ora 配置範本。

架構對比

方案一：多埠監聽

Server 端 listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = KKMAN)
      (SID_NAME = KKMAN)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac02)(PORT = 1522))
    )
  )

Client/Server 端 tnsnames.ora

KKMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = KKMAN)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

方案二：單埠多實例（推薦）

Server 端 listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = KKMAN)
      (SID_NAME = KKMAN)
    )
  )

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

Client/Server 端 tnsnames.ora

KKMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = KKMAN)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

重新載入監聽器

$ lsnrctl stop
$ lsnrctl start
# 或熱重載（不中斷現有連線）
$ lsnrctl reload

驗證連線

$ tnsping KKMAN
$ tnsping ORCL
$ sqlplus user/pass@KKMAN
$ sqlplus user/pass@ORCL

進階：動態服務註冊（PMON 自動註冊）

• 資料庫啟動時 PMON 自動向監聽器註冊 SERVICE_NAMES、INSTANCE_NAME
• 無需在 listener.ora 靜態配置 SID_LIST（除非需啟動前連線/外部程序）
• 查詢註冊狀態：lsnrctl services

常見問題

1. ORA-12514：SERVICE_NAME 不匹配 → 檢查 tnsnames.ora SERVICE_NAME vs 實際註冊服務名
2. ORA-12541：無監聽器 → lsnrctl status、防火牆、HOST/IP 解析
3. ORA-12505：SID 不匹配 → 確認 SID_NAME vs 實際 Instance Name
4. 多 IP 綁定：listener.ora 多 ADDRESS 或 HOST=0.0.0.0