Oracle案例04——TNS-12547: TNS:lost contact

Rangle發表於2018-04-23
Oracle

Oracle資料庫伺服器DG從庫重啟後，無法完成資料同步，具體報錯資訊如下：

一、報錯資訊

alter log報錯

***********************************************************************

Fatal NI connect error 12547, connecting to:

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 23-APR-2018 10:30:45
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12547

TNS-12547: TNS:lost contact
    ns secondary err code: 12560
    nt main err code: 517

TNS-00517: Lost contact
    nt secondary err code: 104
    nt OS err code: 0
Error 12547 received logging on to the standby
FAL[client, USER]: Error 12547 connecting to LOGDB for fetching gap sequence

二、錯誤原因分析

根據報錯資訊可以看到通過tns連線logdb服務名存在問題，可通過排查監聽是否啟動，是否有防火牆限制等。

三、處理步驟

通過排查監聽和防火牆限制，發現沒有問題，主庫通過tns服務名連線正常，從庫通過tnsping報錯

[oracle@dg ~]$ tnsping logdb

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 23-APR-2018 11:26:28

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1xx.xx.xx.xx)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = logdb)))
TNS-12547: TNS:lost contact

從庫通過telnet 主庫IPaddr 1521埠檢查，發現連線上後直接被關閉

# telnet  1xx.xx.xx.xx 1521   
Trying 1xx.xx.xx.xx...
Connected to 1xx.xx.xx.xx.
Escape character is '^]'.
Connection closed by foreign host.

這裡基本可以斷定是監聽問題，具體檢查後發現只sqlnet.ora做了監聽限制

tcp.validnode_checking=yes
tcp.invited_nodes=(ip1,ip2,ip3,ip4,ip5,ip6)

將備庫IP新增進允許訪問列表即可，然後通過tnsping檢查連線正常，然後通過手動恢復主備同步。

（DG主備同步恢復過程略）

 

相關文章