INBOUND_CONNECT_TIMEOUT 설정

INBOUND_CONNECT_TIMEOUT 설정은 악의적인 연결 요청으로 데이터베이스 서버를 Flooding시키는 등의 상황을 방지하기 위해 도입되었습니다.
어떠한 이유든지 TIMEOUT 설정값 이내에 인증 프로세스를 마치지 못하는 경우에 alert.log에  "WARNING: inbound connection timed out (ORA-3136)"를 발생시킵니다.
해당 파라미터를 설정하지 않는 경우, default value는 60초입니다.
해당 값을 변경한 뒤에 DB, Listener 재기동은 필요 없습니다.​

그럼 이제 해당 파라미터를 설정하는 방법을 설명 드리겠습니다.

 

listener.ora에 파라미터 추가

--Oracle Restart구성에서는 grid user가 listener를 관장하므로 grid의 listener.ora에 설정해준다.
[test:/home/oracle] cat $ORACLE_HOME/network/admin/listener.ora 
#Backup file is  /oracle/app/base/crsdata/test/output/listener.ora.bak.test.grid line added by Agent
# listener.ora Network Configuration File: /oracle/app/grid/product/19c/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON            # line added by Agent


INBOUND_CONNECT_TIMEOUT_LISTENER=20		--LISTENER_NAME 적어주는 것.

 

sqlnet.ora에 파라미터 추가

--sqlnet.ora는 oracle에 추가.
[test:/home/oracle] cat $ORACLE_HOME/network/admin/sqlnet.ora 
#sqlnet.authentication_services=(none)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.INBOUND_CONNECT_TIMEOUT=20

 

리스너를 통한 접속 지연 유발

[oracle@test2 ~]$ sqlplus huser@ORACLE

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 8 15:02:05 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password: 
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

이 상태에서 20초가 지나면 DB서버의 alert.log에 ORA-3136이 떨어진다.

 

DB서버의 alert.log 확인

[test:/home/oracle] tail -f $ORACLE_BASE/diag/rdbms/o19300/o19300/trace/alert_o19300.log
...
2022-06-08T15:04:29.810202+09:00


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

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 19.0.0.0.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
  Version 19.3.0.0.0
  Time: 08-JUN-2022 15:04:29
  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=xxx.xxx.xxx.xxx)(PORT=xxxxx))
2022-06-08T15:04:29.825016+09:00
WARNING: inbound connection timed out (ORA-3136)

alert.log에 기록된 Client address 정보(HOST,PORT)를 통해 listener.log에서 해당 연결이 언제 처리되었는지 확인할 수 있다. 각 log에서 timestamp를 확인하여 실제 적영된 timeout 값을 확인할 수 있다.