If AUDIT_TRAIT is set to DB:

SQL> show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

set linesize 190
col returncode format 999999
col TERMINAL format a30
column os_username format a20
column username format a20
column action_name format a20
col USERHOST format a40
col TERMINAL format a30

select os_username, username, action_name, userhost, terminal, returncode, count(*)
	from dba_audit_trail 
	where returncode !=0 
			and timestamp >= sysdate-INTERVAL '2' HOUR
GROUP BY os_username, username,action_name, userhost, terminal, returncode;

OS_USERNAME    USERNAME    ACTION_NAME   USERHOST        TERMINAL       RETURNCODE       COUNT(*)
-------------- ----------- ------------- --------------- -------------  ---------------  ------------
orarom         DBSNMP      LOGON         hostnameX       UNKNOWN IMDB01            1017           851


*Note of most common return codes:
-Return code 28000: account locked.
-Return code 28001: password expired
-Return code 1017: invalid password provided

If AUDIT_TRAIT is set to XML:

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      XML, EXTENDED

col OS_HOST format a50
col OS_USER format a20
col DB_USER format a20
set linesize 190

select  OS_USER, DB_USER, OS_HOST,  RETURNCODE,  count(*)
	from V$XML_AUDIT_TRAIL 
	where returncode !=0 
		 and EXTENDED_TIMESTAMP >= sysdate-INTERVAL '1' HOUR
	GROUP BY OS_USER, DB_USER, OS_HOST, RETURNCODE
order by 5;

OS_USER              DB_USER              OS_HOST             RETURNCODE     COUNT(*)
-------------------- -------------------- ------------------  ------------- ------------
oracle               /                    hostnameX                     979            1
oracle               /                    hostnameX                     903            1
oracle               /                    hostnameX                     942            1
oracle               /                    hostnameX                     904            4
oracle               DBSNMP               hostnameX                    1017          294

SQL> 

In this case, this is the agent configured with a invalid password.

Last modified: 1 July 2021

Author

Comments

Write a Reply or Comment

Your email address will not be published.