Client 連線到 Oracle Database 拋出錯誤 ORA-12523 發生在 Windows ( Oracle Client Connect to Database on Windows getting ORA-12523 )

 Problem：

今天在客戶的測試環境發生一個狀況，客戶的測試環境有模擬AP系統主備切換測試，資料庫主備架構為 dataguard，切換過程只有將 dataguard 暫停，並開啟 Standby 至 read only 模式，切換過程裡，AP連至DB的FQDN裡的IP會從DNS Server去調整，造成 Client 連線到 Standby 產生 ORA-12523的錯誤，檢查過程裡是有發現 Listener 都使用 FQDN 名稱，database 的 local_listener 也一併使用 FQDN。

Today, a situation occurred in the customer's test environment. The customer's test environment has a simulated AP system active-standby switch test. The database master-standby architecture is dataguard. During the switchover process, dataguard will be suspended, and Standby will be turned on to read only mode. The IP in the FQDN of the AP connecting to the DB will be adjusted from the DNS Server, resulting in an ORA-12523 error when the Client connects to the Standby. During the inspection process, it is found that the Listener uses the FQDN name, and the local_listener of the database also uses the FQDN.

Solution：

最後解法是採用將 Listener 設定的 FQDN 全部改為 IP Address，但使用FQDN最建議的做法，還是寫入到本機 etc\hosts 裡，必免 DNS 出問題，反而找不出原因。

The final solution is to change all the FQDNs set by the Listener to IP Addresses, but the most recommended way to use FQDNs is to write them into the local etc\hosts to avoid DNS problems, but no reason can be found.

-- 1. Restart Listener

# net stop OracleOraGI19Home1TNSListener
# net start OracleOraGI19Home1TNSListener

-- 2. Register dynamic Service

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.20.30.40)(PORT=1521)))';

-- 3. Change default listener LISTENER using IP address
-- listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.30.40)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

-- 4. Change tnsnames.ora using IP address in the connect string
-- tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.30.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

 

參考來源：

Redo transport on Windows getting ORA-12523 (Doc ID 2370988.1)