Oracle Net 網絡配置故障診斷全流程：listener.ora、sqlnet.ora、tnsnames.ora 深度解析

三大配置文件職責邊界

位置：$ORACLE_HOME/network/admin/ (可通過 TNS_ADMIN 環境變量重定向)

連接建立全鏈路解析

用戶輸入：sqlplus scott/tiger@ORCL
        ↓
1. 讀取 sqlnet.ora → NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT, LDAP)
   → 決定按順序嘗試 TNSNAMES → EZCONNECT → LDAP 解析 "ORCL"
        ↓
2. TNSNAMES 模式：讀取 tnsnames.ora 尋找 ORCL 條目
   → 獲取 HOST、PORT、SERVICE_NAME、SERVER=DEDICATED/SHARED
        ↓
3. TCP 三次握手連接 HOST:PORT → 監聽器進程
        ↓
4. 監聽器根據 SERVICE_NAME/SID 匹配 listener.ora 中 SID_LIST
   → 驗證 GLOBAL_DBNAME (若配置)
        ↓
5. 監聽器啟動專用/共享服務器進程 → 移交連接
        ↓
6. 服務器進程完成認證 → 會話建立
        ↓
7. 監聽器職責結束，不再參與後續數據傳輸

核心配置範本與關鍵參數

sqlnet.ora

# 名稱解析順序：優先本地 TNSNAMES，回退 EZCONNECT
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

# 安全加固
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA512)
SQLNET.EXPIRE_TIME = 10          # 死連接檢測 10 分鐘
SQLNET.INBOUND_CONNECT_TIMEOUT = 15
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 15

# 審計
SQLNET.AUTHENTICATION_SERVICES = (NTS, BEQ)
ADR_BASE = /u01/app/oracle/diag

tnsnames.ora

# 標準專用服務器連接
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db-server.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.example.com)
    )
  )

# 負載均衡/故障轉移 (RAC/Data Guard)
ORCL_HA =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.example.com)
      (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
    )
  )

# EZCONNECT 直連 (無需 tnsnames.ora)
# sqlplus scott/tiger@"db-server:1521/orcl.example.com"

listener.ora

# 監聽器基礎配置
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db-server.example.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

# 靜態服務註冊 (Data Guard 備庫/外部過程/非動態註冊實例必需)
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_dgmgrl.example.com)  # Data Guard Broker 專用
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.example.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )

# 安全加固
ADMIN_RESTRICTIONS_LISTENER = ON
INBOUND_CONNECT_TIMEOUT_LISTENER = 10
SECURE_REGISTER_LISTENER = (IPC)  # 僅允許本地 IPC 動態註冊
# PASSWORDS_LISTENER =      # 12c+ 建議使用本地 OS 認證替代

三大黃金診斷命令

1. lsnrctl：監聽器狀態與控制

lsnrctl status           # 完整狀態：端口、服務、實例狀態、處理程序數
lsnrctl services         # 服務級詳情：SERVICE_NAME、實例、處理程序、狀態
lsnrctl start|stop|reload|save_config
# 關鍵輸出解讀：
# "Service "orcl" has 1 instance(s). Instance "ORCL", status READY, has 1 handler(s)"
# "Handler(s): DEDICATED SERVER established:123 refused:0"

2. tnsping：客戶端解析與連通性驗證

tnsping ORCL
# 輸出關鍵信息：
# Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
# Used TNSNAMES adapter to resolve the alias
# Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db-server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl)))
# OK (15 msec)  ← 成功：監聽器響應正常
# TNS-12541: TNS:no listener  ← 監聽器未啟動/端口錯誤/防火牆
# TNS-12514: TNS:listener does not currently know of service requested  ← 服務未註冊/靜態註冊缺失

3. 服務端實例狀態驗證

SQL> SHOW SGA;                    -- 實例是否啟動
SQL> SELECT OPEN_MODE FROM V$DATABASE;  -- READ WRITE / MOUNTED / READ ONLY
SQL> SELECT INSTANCE_NAME, STATUS, DATABASE_STATUS FROM V$INSTANCE;

常見故障模式速查表

進階場景：Host Naming (無 tnsnames.ora 直連)

前提：sqlnet.ora → NAMES.DIRECTORY_PATH=(TNSNAMES, HOSTNAME)，DNS 解析主機名，監聽器 GLOBAL_DBNAME=主機名。

# 客戶端直連
sqlplus scott/tiger@db-server.example.com

# 監聽器靜態註冊關鍵
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = db-server.example.com) (SID_NAME = ORCL)))

優點：客戶端零配置；缺點：依賴 DNS 穩定性、不支持負載均衡/故障轉移、安全審計困難。生產環境建議仍使用 tnsnames.ora/EZCONNECT。

RAC/Data Guard 環境特殊檢查點

• 掃描 VIP (SCAN) 配置：srvctl config scan、srvctl config scan_listener
• 客戶端使用 SCAN 地址：HOST=scan-cluster.example.com
• Data Guard：主備 GLOBAL_DBNAME 区分 (主: orcl、備: orcl_dgmgrl)，Broker 依賴
• 遠程監聽器參數：REMOTE_LISTENER=scan-cluster:1521 (動態註冊到 SCAN)