oracle多次密码错误登录，用户锁住或失效

多次输入错误账号查询状态：

select username,account_status from dba_users;

TEST                   EXPIRED(GRACE) 密码错误延迟登录，延迟登录还能登录
或者
TEST                   LOCKED(TIMED)  密码错误锁
TEST                   EXPIRED(GRACE) & LOCKED(TIMED) 延迟登录和锁

发生LOCKED(TIMED)后，登录报错
ERROR:
ORA-28000: the account is locked

查询配置表

select * from dba_profiles;

可以看到默认配置密码登录10次失败就会被锁

密码错误超时终止，日志报如下告警
Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for Linux: Version 11.2.0.4.0 - Production
    Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 06-SEP-2024 08:45:52
  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=192.168.210.9)(PORT=56688))
WARNING: inbound connection timed out (ORA-3136)

开启密码登录失败输出详细日志

alter system set events '1017 trace name errorstack level 10';

Errors in file /oracle/app/diag/rdbms/orcldg/orcldg/trace/orcldg_ora_22340.trc:
ORA-01017: invalid username/password; logon denied
Fri Sep 06 09:00:39 2024
Dumping diagnostic data in directory=[cdmp_20240906090039], requested by (instance=1, osid=22340), summary=[abnormal process termination].

*** 2024-09-06 09:03:13.934
*** SESSION ID:(840.2215) 2024-09-06 09:03:13.934
*** CLIENT ID:() 2024-09-06 09:03:13.934
*** SERVICE NAME:(orcldg) 2024-09-06 09:03:13.934
*** MODULE NAME:(sqlplus@host2 (TNS V1-V3)) 2024-09-06 09:03:13.934
*** ACTION NAME:() 2024-09-06 09:03:13.934

日志会记录详细的应用和主机名

关闭事件：

ALTER SYSTEM SET EVENTS '1017 trace name errorstack off';

当然也可以把登录错误密码改成无限制，但有被安全爆破风险

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;