Fatal NI connect error 12170的解决方案

**导读**

> 作者：杨漆

> 16年关系型数据库管理，从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM；运维路上不平坦，跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿，希望帮到大家少走弯路、少熬夜。

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

Fatal NI connect error 12170.

VERSION INFORMATION:

TNS for Linux: Version 12.2.0.1.0 - Production

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

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

Time: 19-JUL-2021 07:44:37

Tracing not turned on.

Tns error struct:

ns main err code: 12535

TNS-12535: TNS:operation timed out

ns secondary err code: 12560

nt main err code: 505

TNS-00505: Operation timed out

nt secondary err code: 110

nt OS err code: 0

Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.8)(PORT=57816))

2021-07-19T07:44:37.913683+08:00

RFS[2453]: Possible network disconnect with primary database

2021-07-19T07:44:50.197565+08:00

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

Fatal NI connect error 12170.

VERSION INFORMATION:

TNS for Linux: Version 12.2.0.1.0 - Production

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

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

Time: 19-JUL-2021 07:44:50

Tracing not turned on.

Tns error struct:

ns main err code: 12535

TNS-12535: TNS:operation timed out

ns secondary err code: 12560

nt main err code: 505

TNS-00505: Operation timed out

nt secondary err code: 110

nt OS err code: 0

Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.2)(PORT=57810))

2021-07-19T07:44:50.201483+08:00

RFS[2450]: Possible network disconnect with primary database

2021-07-19T08:00:17.721570+08:00

RFS[2489]: Selected log 26 for T-3.S-196711 dbid 1513741333 branch 985960599

2021-07-19T08:00:17.855381+08:00

Media Recovery Waiting for thread 3 sequence 196711 (in transit)

2021-07-19T08:00:17.856272+08:00

登陆10.10.10.8检查归档应用状态（第二备库）

SQL> select count(*),to_char(min(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='NO';

COUNT(*) TO_CHAR(MIN(FIRST

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

0

SQL> select count(*),to_char(max(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='YES';

COUNT(*) TO_CHAR(MAX(FIRST

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

6413 20210719 08:18:02

登陆10.10.10.8检查归档应用状态（网络状态良好的第一备库）进行对比

SQL> select count(*),to_char(min(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='NO';

COUNT(*) TO_CHAR(MIN(FIRST

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

0

SQL> select count(*),to_char(max(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='YES';

COUNT(*) TO_CHAR(MAX(FIRST

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

14711 20210719 08:18:02

应用一切正常！ 没有影响Master--Standby的运行，但日志量膨胀地太大，看到error总感觉有问题。

fatal NI connect error 12170产生的原因为：

1.备库试图连接主机，没有得到Master端的正确响应

2.被连接的主机没有响应导致一个已建立的连接失败

12C的自动诊断仓库默认将连接失败信息写入告警日志里，如果这类信息比较多会导致告警日志快速膨胀，降低磁盘空间可用量。

解决方案：

1.在sqlnet.ora中添加一行

DIAG_ADR_ENABLED=OFF

2.在listener.ora中添加一行

DIAG_ADR_ENABLED_LISTENER=OFF ## DIAG_ADR_ENABLED_<listenername>=OFF

3.重启监听

lsnrclt restart（业务会中断）

or

lsnrctl reload (业务不中断，如果业务不是很紧张，最好使用lsnrctl restart确保参数生效）

检查监听状态

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.8)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production

Start Date 19-JUL-2021 09:03:38

Uptime 0 days 0 hr. 0 min. 3 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/network/admin/listener.ora

Listener Log File /u01/app/oracle/product/network/log/listener.log

Listening Endpoints Summary...

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

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

Services Summary...

Service "orcl" has 1 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

Service "orcldg3" has 1 instance(s).

Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

经过2小时的观察，日志中再没出现过类似Error信息。

问题解决！