RAC下Fatal NI connect error 12170.報錯處理，fatal12170

RAC下Fatal NI connect error 12170.報錯處理，fatal12170

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處，否則有權追究著作權法律責任。

深藍的blog：http://blog.csdn.net/huangyanlong/article/details/46372849

 

查看資料庫警示日誌，發現錯誤：Fatal NI connect error 12170報錯

 

警示日誌如下：

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

Fatal NI connect error 12170.

 

  VERSION INFORMATION:

         TNS for Linux: Version 11.2.0.1.0 - Production

         Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

         TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 26-OCT-2014 06:05:44

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

   

  VERSION INFORMATION:

         TNS for Linux: Version 11.2.0.1.0 - Production

         Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

         TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 26-OCT-2014 06:05:44

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

   

TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.20)(PORT=19164))

 

  VERSION INFORMATION:

         TNS for Linux: Version 11.2.0.1.0 - Production

         Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

         TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production

  Time: 26-OCT-2014 06:05:44

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

   

TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.20)(PORT=19166))

TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.20)(PORT=19165))

Sun Oct 26 06:05:50 2014

Successfully onlined Undo Tablespace 2.

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

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

【解決方式】

[oracle@node1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-MAR-2015 12:34:22

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                02-MAR-2015 09:45:49

Uptime                    0 days 2 hr. 48 min. 33 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.20)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.105.24)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

  Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "xcky" has 1 instance(s).

  Instance "xcky1", status READY, has 1 handler(s) for this service...

Service "xckyXDB" has 1 instance(s).

  Instance "xcky1", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@node1 ~]$ cd /u01/11.2.0/grid/network/admin/

[oracle@node1 admin]$ ls

endpoints_listener.ora     listener.ora            samples     sqlnet.ora

listener1410255PM1539.bak  listener.ora.bak.node1  shrept.lst

[root@node1 admin]# chmod 775 listener.ora

[root@node1 admin]# chmod 775 sqlnet.ora

--確認oracle使用者對設定檔的存取權限

[oracle@node1 admin]$ vi listener.ora

--添加參數INBOUND_CONNECT_TIMEOUT_LISTENER = 0

--添加參數DIAG_ADR_ENABLED_LISTENER = OFF

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

INBOUND_CONNECT_TIMEOUT_LISTENER = 0

DIAG_ADR_ENABLED_LISTENER = OFF

~

~

[oracle@node1 admin]$ vi sqlnet.ora

--添加參數DIAG_ADR_ENABLED = OFF

--添加參數SQLNET.INBOUND_CONNECT_TIMEOUT =0

# sqlnet.ora.node1 Network Configuration File: /u01/11.2.0/grid/network/admin/sqlnet.ora.node1

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

DIAG_ADR_ENABLED = OFF

SQLNET.INBOUND_CONNECT_TIMEOUT =0

~

~

【官方文檔】

來看一下官方文檔中的說明，如下：

ORA-12170: TNS:Connect timeout occurred

Cause: The client failed to establish a connection and complete authentication in the time specified by the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in the sqlnet.ora file. This error may be a result of network or system delays, or it may indicate that a malicious client is trying to cause a denial-of-service attack on the database server.

See Also:

"Configuring the Listener and the Oracle Database To Limit Resource Consumption By Unauthorized Users" further information about setting the SQLNET.INBOUND_CONNECT_TIMEOUT parameter

Action: If the error occurred due to system or network delays that are normal for the particular environment, then perform these steps:

Turn on tracing to determine where clients are timing out.

See Also:

"Tracing Error Information for Oracle Net Services"

Reconfigure the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora to a larger value.

If you suspect a malicious client, then perform these steps:

Locate the IP address of the client in the sqlnet.log file on the database server to identify the source.

For example, the following sqlnet.log excerpt shows a client IP address of 10.10.150.35.

Fatal NI connect error 12170.

 

  VERSION INFORMATION:

        TNS for Solaris: Version 10.1.0.2.0

        Oracle Bequeath NT Protocol Adapter for Solaris: Version 10.1.0.2.0

        TCP/IP NT Protocol Adapter for Solaris: Version 10.1.0.2.0

  Time: 03-JUL-2002 13:51:12

  Tracing to file: /ora/trace/svr_13279.trc

  Tns error struct:

    nr err code: 0

    ns main err code: 12637

    TNS-12637: Packet receive failed

    ns secondary err code: 12604

    nt main err code: 0

    nt secondary err code: 0

    nt OS err code: 0

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.150.35)(PORT=52996))

 

Beware that an IP address can be forged.

If the time out occurs before the IP address can be retrieved by the database server, then enable listener tracing to determine the client that made the request.

See Also:

Tracing Error Information for Oracle Net Services

Restrict access to the client. For example, you can configure parameters for access rights in the sqlnet.ora file.

See Also:

"Configuring Database Access Control"

 

【過程梳理】

上面是10G的官方文檔內容，可以看到官方文檔，給出了一個建議：Reconfigure the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in sqlnet.ora to a larger value.

再找找官方文檔對於SQLNET.INBOUND_CONNECT_TIMEOUT的說明，如：

        如上，這個問題的出現是由於資料庫連接沒能成功完成，在串連結果來看出現了延遲造成的。在官方文檔中並沒有找到明確的處理方式，只是一些配置建議。

       後續在網上搜了搜同類的錯誤處理方式（有資料說這個錯誤可以再MOS文檔中找到），再結合官方文檔，初步瞭解到對於ORA-12170錯誤，由於在Automatic Diagnostic Repository中的 Oracle Net diagnostic是開啟狀態，從而對串連延遲錯誤進行擷取並且將其寫入警示日誌。

 

網友貼出來的MOS文檔參考，如下：

To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :

DIAG_ADR_ENABLED = OFF

Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:

DIAG_ADR_ENABLED_<listenername> = OFF

   - Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is 'LISTENER', the parameter would read:

DIAG_ADR_ENABLED_LISTENER = OFF

-Reload or restart the TNS Listener for the parameter change to take effect.

 

網友貼出的Metalink上給出的解決方案

1. set INBOUND_CONNECT_TIMEOUT_=0 in listener.ora

2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.

3. stop and start both listener and database.

4. Now try to connect to DB and observe the behaviour

 

*******************************************藍的成長記系列_20150523*************************************

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

藍的成長記——追逐DBA（1）：奔波於路上，挺進山東

藍的成長記——追逐DBA（2）：安裝！安裝！久違的記憶，引起我對DBA的重新認知

藍的成長記——追逐DBA（3）：古董上操作，資料匯入匯出成了問題

藍的成長記——追逐DBA（4）：追憶少年情愁，再探oracle安裝（Linux下10g、11g）

藍的成長記——追逐DBA（5）：不談技術談業務，惱人的應用系統

藍的成長記——追逐DBA（6）： 做事與做人：小技術，大為人

藍的成長記——追逐DBA（7）：基礎命令，地基之石

藍的成長記——追逐DBA（8）：重拾SP報告，回憶oracle的STATSPACK實驗

藍的成長記——追逐DBA（9）：國慶漸去，追逐DBA，新規劃，新啟程

藍的成長記——追逐DBA（10）：飛刀防身，熟絡而非專長：擺弄中介軟體Websphere

藍的成長記——追逐DBA（11）：回家後的安逸，暈暈乎乎醒了過來

藍的成長記——追逐DBA（12）：七天七收穫的SQL

藍的成長記——追逐DBA（13）：協調硬體廠商，六個故事：所見所感的“伺服器、儲存、交換器......”

藍的成長記——追逐DBA（14）：難忘的“雲”端，起步的hadoop部署

藍的成長記——追逐DBA（15）：以為FTP很“簡單”，誰成想一波三折

藍的成長記——追逐DBA（16）：DBA也喝酒，被捭闔了

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

 

********************************************足球與oracle系列_20150528***********************************

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

足球與oracle系列（1）：32路諸侯點兵，oracle32進程聯盟 之A組巴西SMON進程的大局觀

足球與oracle系列（2）：巴西揭幕戰預演，oracle體繫結構雜談

足球與oracle系列（3）：oracle進程排名，世界盃次回合即將戰罷！

足球與oracle系列（4）：從巴西慘敗於德國，想到，差異的RAC拓撲對比！ 

足球與oracle系列（5）：fifa14遊戲缺失的directX庫類比於oracle的rpm包！

足球與oracle系列（6）：伴隨建庫的亞洲杯——加油中國隊

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