Oracle 網路架構與連線問題排查全指南：sqlnet.ora、tnsnames.ora、listener.ora 三大配置檔深度解析

三大配置檔角色定位

連線流程完整追蹤

1. 客戶端執行 SQLPLUS USER/PASS@ORCL
2. 讀取 SQLNET.ORA → NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)
3. 優先查 TNSNAMES.ORA 找 ORCL 記錄 → 取得 HOST、PORT、SERVICE_NAME
4. TCP 連接 Listener (Port 1521)
5. Listener 依 SERVICE_NAME 或 SID 尋找對應服務處理程序
6. 專用伺服器模式：Listener 啟動新 Server Process，回傳重導向端口，Client 直連
7. 共享伺服器模式：Listener 導向 Dispatcher，Client 連 Dispatcher
8. 連線建立完成，Listener 任務結束，後續通訊不經 Listener

配置檔標準範本

SQLNET.ORA

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
# EZCONNECT 允許 user/pass@host:port/service 直連，無需 TNSNAMES
SQLNET.EXPIRE_TIME = 10          # 檢測死連線 (分鐘)
SQLNET.INBOUND_CONNECT_TIMEOUT = 60

TNSNAMES.ORA

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

SALES =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SALES-SRV)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SALES)
    )
  )

LISTENER.ORA (伺服器端)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /U01/APP/ORACLE/PRODUCT/11.2.0/DBHOME_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

# 關鍵：ADMIN_RESTRICTIONS_LISTENER=ON 防止遠程 LSNRCTL 停止監聽
# SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=ON 支援 RAC 節點故障快速轉移

三大診斷指令

Hostname 直連模式 (免 TNSNAMES)

1. Listener.ora 設定 GLOBAL_DBNAME 並確保 HOST 可解析
2. SQLNET.ORA 設定 NAMES.DIRECTORY_PATH=(HOSTNAME)
3. Client 直連：SQLPLUS USER/PASS@DBHOST

適合大量 Client、IP 固定、DNS 完善的環境，避免分發 TNSNAMES.ORA 維護成本。

常見錯誤代碼速查

本地連線不經 Listener

SQLPLUS / AS SYSDBA          -- OS 認證，Bequeath 協議，無需 Listener
SQLPLUS USER/PASS            -- 本地 Bequeath，需資料庫 OPEN
SQLPLUS USER/PASS@LOCAL_SID  -- 經 Listener，需 Listener 運行