Oracle Client 安裝與遠端連線配置圖文詳解

Oracle Client (Instant Client / Full Client) 安裝後，需配置 tnsnames.ora、sqlnet.ora 才能連線遠端資料庫。本文以圖文並茂方式說明通訊架構、Listener 配置、Client 端設定全流程。

通訊架構核心

Client App → OCI/ODP.NET/JDBC → tnsnames.ora (解析 TNS 別名)
                                    ↓
                              TCP/IP + Oracle Net Protocol
                                    ↓
Server Listener (port 1521) → listener.ora (驗證 SID/Service)
                                    ↓
                              Database Instance

Server 端：Listener 配置 (listener.ora)

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl.example.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = orcl)
    )
  )

啟動：lsnrctl start；驗證：lsnrctl status。

Client 端：tnsnames.ora 配置

ORCL_REMOTE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.example.com)
    )
  )

• HOST：資料庫伺服器 IP 或主機名 (需可解析)。
• SERVICE_NAME：對應 Server GLOBAL_DBNAME 或 SERVICE_NAMES 參數。
• SID (舊寫法)：對應 SID_NAME，單實例可用。

Client 端：sqlnet.ora 進階參數

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.EXPIRE_TIME = 10          # 偵測死連線 (分鐘)
TCP.CONNECT_TIMEOUT = 60         # 連線逾時 (秒)
TRACE_LEVEL_CLIENT = OFF         # 關閉 Trace 提升效能

驗證連線

tnsping ORCL_REMOTE
sqlplus user/pass@ORCL_REMOTE

Instant Client 輕量部署 (無安裝程式)

1. 下載 instantclient-basic-linux.x64-19.x.x.x.x.zip + sqlplus + odbc 等套件。
2. 解壓至 /opt/oracle/instantclient_19_3。
3. 設定環境變數：
   export ORACLE_HOME=/opt/oracle/instantclient_19_3
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin (放置 tnsnames.ora)
4. 建立 network/admin 目錄放入 tnsnames.ora、sqlnet.ora。

常見錯誤對照

照著架構圖 → Server Listener → Client tnsnames → 驗證，遠端連線零失誤。