Monday, September 14, 2020

what is causing account lock ?


check profile

     col PROFILE format a10

     col LIMIT format a15

     select * from dba_profiles where resource_type='PASSWORD' order by 1,2;



option 1


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

     check listener , alert.log

     turn off trace : alter system set events '1017 trace name errorstack off';



option 2


col dbusername form a15

col userhost form a15

col event_timestamp form a30


select dbusername,userhost,event_timestamp 

   from unified_audit_trail 

   where return_code=1017 and 

   event_timestamp>sysdate-1/12 order by 3


option 3

     AUDIT connect whenever  NOT SUCCESSFUL;

     AUDIT SESSION WHENEVER NOT SUCCESSFUL;

     

        

   -- oracle doc : 352389.1

     AUDIT SESSION WHENEVER NOT SUCCESSFUL;

     select userid, userhost, terminal, clientid, TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE  from aud$ where returncode=1017

     and NTIMESTAMP# > sysdate - 1/12 ;


option 4

   -- oracle doc : 221944.1

     SQL>AUDIT CREATE SESSION BY ACCESS WHENEVER NOT SUCCESSFUL;

     SQL>AUDIT CONNECT BY ACCESS WHENEVER NOT SUCCESSFUL;

     

      col USERID format a10

      col USERHOST format a50

      col TERMINAL format a10

      col userhost  form a20

     select returncode, action#, userid, userhost, terminal, TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF') from aud$ where returncode='1017' and action#=100

    and NTIMESTAMP# > sysdate - 1/12 ;

     

     SELECT TO_CHAR(NTIMESTAMP# , 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE   from aud$ where returncode=1017

     and NTIMESTAMP# > sysdate - 1/12 ;