定位错误密码来源，解决账号因密码错误被锁

1、有审计的话，可以直接查询aud$表：

查询最近1天由于密码错误登陆失败的信息（有开审计的情况下使用）

select sessionid, userid, userhost, comment$text, spare1, ntimestamp#

  from aud$

 where returncode = 1017

   and ntimestamp# > sysdate - 1;

 

2、使用EVENT触发写入到alert后查看trace文件：

alter system set events ='1017 trace name errorstack level 1';

......

alter system set events ='1017 trace name errorstack off';

 

3、使用trigger实现写入到alert日志：

create or replace trigger logon_denied_to_alert

after servererror on database

declare

  message   varchar2(120);

  IP        varchar2(15);

  v_os_user varchar2(80);

  v_module  varchar2(50);

  v_action  varchar2(50);

  v_pid     varchar2(10);

  v_sid     number;

begin

  IF (ora_is_servererror(1017)) THEN

    if sys_context('userenv', 'network_protocol') = 'tcp' then

      IP := sys_context('userenv', 'ip_address');

    else

      select distinct sid into v_sid from sys.v_$mystat;

      SELECT p.SPID

        into v_pid

        FROM V$PROCESS p, V$SESSION v

       WHERE p.ADDR = v.PADDR

         AND v.sid = v_sid;

    end if;

    v_os_user := sys_context('userenv', 'os_user');

    dbms_application_info.READ_MODULE(v_module, v_action);

    message := to_char(sysdate, 'Dy Mon dd HH24:MI:SS YYYY') ||

               ' logon denied from ' || nvl(IP, v_pid) || ' ' || v_os_user ||

               ' with ' || v_module || ' ' || v_action;

    sys.dbms_system.ksdwrt(2, message);

  end if;

end;

 

附登录相关常见错误：

[oracle@JY-DB01 u01]$ oerr ora 28009

28009, 00000, "connection as SYS should be as SYSDBA or SYSOPER"

// *Cause: connect SYS/<password> is no longer a valid syntax

// *Action: Try connect SYS/<password> as SYSDBA or

// connect SYS/<password> as SYSOPER

// [oracle@JY-DB01 u01]$ oerr ora 2800028000, 00000, "the account is locked"

// *Cause: The user has entered wrong password consequently for maximum

// number of times specified by the user's profile parameter

// FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account

// *Action: Wait for PASSWORD_LOCK_TIME or contact DBA [oracle@JY-DB01 u01]$ oerr ora 10170

1017, 00000, "invalid username/password; logon denied"

// *Cause:

// *Action: [oracle@JY-DB01 u01]$ oerr ora 1920

01920, 00000, "user name '%s' conflicts with another user or role name"

// *Cause: There is already a user or role with that name.

// *Action: Specify a different user name. [oracle@JY-DB01 u01]$ oerr ora 1005

01005, 00000, "null password given; logon denied"

// *Cause:

// *Action: [oracle@JY-DB01 u01]$ oerr ora 2002

02002, 00000, "error while writing to audit trail"

// *Cause:

// *Action: