**導讀**
> 作者：楊漆
> 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
---------- -----------------
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
---------- -----------------
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資訊。
問題解決！
