Oracle12C登录卡顿问题

青

潇湘秦2024年12月27日约 5 分钟

昨天一个微信上的朋友找我帮忙看一个登录问题，如下是他的问题描述

在他们沟通排除了网络问题后，开始看做进一步的问题排查

1.排查问题时段listener log和alert log

发现问题时段连接频率确实有变低，alert log中无明显报错；

2.拉取问题时段AWR报表

从AWR报表一眼即可看到问题的所在：Failed Logon Delay这个异常一般都是因为有自动或者定时任务的程序使用的用户名和密码错误引起的；另外还有个一个原因就是从oracle 12C开始将密码错误尝试的参数SEC_MAX_FAILED_LOGIN_ATTEMPTS 由原来的10次调整为3次，结合用户的版本就是12.2版本，那这基本就是问题的所在了；

AWR报表是处理ORACLE异常，调优的不二利器，最近我联合几位ORACLE ACE建了一个付费微信群，并有原ORACLE优化器开发工程师特邀嘉宾，会在群中为群友免费解读疑难AWR，感兴趣的联系博主：xiaowai23z （备注:AWR）

3. 检查和解决步骤

(a) 检查 `Failed Logon Delay` 的等待事件

登录到数据库并检查等待事件：

SELECT event, total_waits, time_waited, average_waitFROM v$session_eventWHERE event = 'Failed Logon Delay';

(b) 检查失败登录记录

通过动态性能视图 dba_audit_session 查看登录失败的详细信息：

SELECT username, os_username, userhost, timestamp, returncodeFROM dba_audit_sessionWHERE returncode !=0ORDERBYtimestampDESC;

常见 returncode的含义：

ORA-01017 无效的用户名或密码。
ORA-28000 账户被锁定。

(c) 检查账户状态

检查是否有被锁定的账户：

SELECT username, account_statusFROM dba_usersWHERE account_status LIKE'%LOCKED%';

如果发现有账户被锁定，可以手动解锁：

ALTERUSER username ACCOUNT UNLOCK;

(d) 检查登录失败的次数

查看登录失败的次数限制：

SHOWPARAMETER SEC_MAX_FAILED_LOGIN_ATTEMPTS;

修改登录失败限制（例如将其设置为 10 次）

这个是静态参数需要重启生效

ALTERSYSTEMSET SEC_MAX_FAILED_LOGIN_ATTEMPTS =10scope=spfile;

（e）创建fail logon trigger追踪密码错误用户

-- Create tablecreate table SYS.A_DB_UNSUCCESSFUL_LOGINS(  username   VARCHAR2(30),  osuser     VARCHAR2(30),  machine    VARCHAR2(64),  terminal   VARCHAR2(30),  ipaddr     VARCHAR2(30),  program    VARCHAR2(48),moduleVARCHAR2(48),  isdba      VARCHAR2(10),  errormsg   VARCHAR2(60),  logon_time DATE default SYSDATE);
---create trigger
CREATE OR REPLACE TRIGGER SYS.a_db_unsuccessful_login_trg
AFTER servererror ON DATABASE
DECLARE
    v_ipaddr     a_db_unsuccessful_logins.ipaddr%TYPE;    v_logon_user a_db_unsuccessful_logins.username%TYPE;    v_machine    a_db_unsuccessful_logins.machine%TYPE;    v_terminal   a_db_unsuccessful_logins.terminal%TYPE;    v_osuser     a_db_unsuccessful_logins.osuser%TYPE;    v_program    a_db_unsuccessful_logins.program%TYPE;    v_module     a_db_unsuccessful_logins.module%TYPE;    v_errormsg   a_db_unsuccessful_logins.errormsg%TYPE;    v_isdba      a_db_unsuccessful_logins.isdba%TYPE;    v_log_error  BOOLEAN DEFAULT FALSE;
/************************************************************************
    name:  a_db_unsuccessful_login_trg
    purpose: log unsuccessful LOGON action.
	revisions:
    ver        DATE         author           description
    ---------  ----------  ---------------  ---------------------------------
    1.0        2023/11/01    norton.fan   create this TRIGGER.
    ************************************************************************/
BEGIN
    -- read the context
    v_osuser     := sys_context('USERENV','OS_USER');    v_machine    := sys_context('USERENV','HOST');    v_terminal   := sys_context('USERENV','TERMINAL');    v_ipaddr     := sys_context('USERENV','IP_ADDRESS');    v_logon_user := sys_context('USERENV','SESSION_USER');    v_isdba      := sys_context('USERENV','ISDBA');    --cut strange charfor windows server.
    v_machine := REPLACE(v_machine,chr(0),'');
--fetch additional session info.
    SELECT program ,module      INTO v_program ,v_module      FROM v$sessionWHEREsid= (SELECT sid FROM v$mystat WHERErownum=1);
    IF is_servererror(1017)    THEN       v_log_error := TRUE;       v_errormsg  := 'ORA-01017: invalid username/password; logon denied';    END IF;
    IF is_servererror(20017)    THEN       v_log_error := TRUE;       v_errormsg  := 'ORA-20017: invalid access privilege; logon denied';    END IF;
    IF v_log_error    THEN       --recordthe unsuccessful logon       INSERT INTO sys.a_db_unsuccessful_logins          (username          ,osuser          ,machine          ,terminal          ,ipaddr          ,program          ,module          ,isdba          ,errormsg          ,logon_time)       VALUES          (v_logon_user          ,v_osuser          ,v_machine          ,v_terminal          ,v_ipaddr          ,v_program          ,v_module          ,v_isdba          ,v_errormsg          ,SYSDATE);
    END IF; END a_db_unsuccessful_login_trg;/