您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle TNS 完整配置指南：tnsnames.ora、listener.ora、sqlnet.ora 核心解析

編輯：Oracle 教學

Oracle TNS 完整配置指南：tnsnames.ora、listener.ora、sqlnet.ora 核心解析

TNS 核心定位

TNS (Transparent Network Substrate) 是 Oracle Net 的核心層，負責管理客戶端與資料庫的網路連接。監聽器只是 TNS 的一個組件，而非全部。

大多數情況：客戶端需配置 TNS 才能連接資料庫
少數情況：JDBC Thin Driver 可繞過 TNS 直接連接
前提：客戶端需安裝 Oracle Client (含 OCI 庫)

核心配置檔案架構

檔案	角色	位置	作用端
tnsnames.ora	連接服務定義 (別名 → 連接描述)	$ORACLE_HOME/network/admin/	客戶端/伺服器端皆需
listener.ora	監聽器配置 (監聽地址、服務註冊)	$ORACLE_HOME/network/admin/	伺服器端
sqlnet.ora	網路安全/轉換/追蹤參數	$ORACLE_HOME/network/admin/	客戶端/伺服器端皆需
cman.ora	Connection Manager 配置 (可選)	$ORACLE_HOME/network/admin/	伺服器端 (CMAN)

listener.ora 監聽器配置詳解

核心結構

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db-server)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

# 9i+ 動態服務註冊，通常不需手動配置 SID_LIST_LISTENER
# 靜態註冊範例 (如外部程序、舊版相容)：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

多 IP 綁定配置

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

tnsnames.ora 客戶端連接定義

標準單一地址

ORCL_PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db-prod.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.prod.example.com)
      (SERVER = DEDICATED)
    )
  )

多地址故障轉移 (FAILOVER)

ORCL_RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac-node1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac-node2)(PORT = 1521))
      (LOAD_BALANCE = ON)          # 客戶端負載均衡
      (FAILOVER = ON)              # 連接故障轉移
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.rac.example.com)
      (SERVER = DEDICATED)
    )
  )

CONNECT_DATA 關鍵參數

參數	說明	常用值
SERVICE_NAME	服務名 (推薦，支援多實例)	orcl.prod.com
SID	系統識別碼 (舊式，單實例)	ORCL
SERVER	伺服器模式	DEDICATED (專用) / SHARED (共享)
INSTANCE_NAME	指定實例 (RAC 環境)	orcl1

sqlnet.ora 全域網路參數

常用配置範例

# 命名解析方法優先順序
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)

# 連接逾時 (秒)
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 10
SQLNET.INBOUND_CONNECT_TIMEOUT = 60

# 追蹤/日誌
TRACE_LEVEL_CLIENT = OFF
TRACE_LEVEL_SERVER = OFF
LOG_DIRECTORY_CLIENT = /var/log/oracle
LOG_FILE_CLIENT = sqlnet_client.log

# 安全參數
SQLNET.ENCRYPTION_CLIENT = ACCEPTED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
SQLNET.CRYPTO_CHECKSUM_CLIENT = ACCEPTED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA256)

# 死連接檢測
SQLNET.EXPIRE_TIME = 10  # 每 10 分鐘發送探測包

參數作用範圍區分

客戶端生效：OUTBOUND_CONNECT_TIMEOUT, NAMES.DIRECTORY_PATH, TRACE_LEVEL_CLIENT
伺服器端生效：INBOUND_CONNECT_TIMEOUT, TRACE_LEVEL_SERVER, SQLNET.EXPIRE_TIME
雙端需配置：加密/校驗參數 (ENCRYPTION_*, CRYPTO_CHECKSUM_*)

配置工具選擇

工具	優點	缺點	適用場景
Net Configuration Assistant (netca)	圖形化、驗證語法、支援目錄服務	需 X11/圖形環境	初次配置、複雜目錄服務
手動編輯	彈性高、版控友善、自動化部署	語法錯誤難偵錯	批量部署、版本控管、容器化
Enterprise Manager Cloud Control	集中管理、合規檢查	需 EM 環境	大型企業環境

驗證與除錯命令

# 監聽器狀態
lsnrctl status
lsnrctl services

# 連接測試
tnsping ORCL_PROD
sqlplus user/pass@ORCL_PROD

# 追蹤啟用 (除錯時)
# sqlnet.ora 加入:
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_DIRECTORY_CLIENT = /tmp/tns_trace
TRACE_FILE_CLIENT = client.trc