How to find the invalid login attempt in the database

we can find this using the below query. You need to provide the username if you are looking for a particular user or just leave blank, it will show for all the users

SELECT
  TO_CHAR(TIMESTAMP,'MM/DD HH24:MI') TIMESTAMP,
  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
  SUBSTR(USERNAME,1,20) USERNAME,
  SUBSTR(TERMINAL,1,20) TERMINAL,
  ACTION_NAME,
  RETURNCODE,
   OS_USERNAME,
   USERNAME,
   USERHOST  
FROM
  SYS.DBA_AUDIT_SESSION
WHERE
  USERNAME LIKE '%&1%'
  AND TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE and RETURNCODE in (1017,20008)
ORDER BY
  TIMESTAMP DESC;

We have another view DBA_AUDIT_TRAIL, which can be also used to detect invalid login attempts. If you are interested in a particular user, give the username as input

alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';
select username, os_username, userhost, client_id, timestamp, from dba_audit_trail where returncode = 1017 and timestamp > sysdate - 1  and  USERNAME LIKE '%&1%'  order by timestamp;

You can below query also to summarize all the invalid attempts in the database

select username, os_username, userhost, client_id, trunc(timestamp), count(*) failed_logins from dba_audit_trail where returncode = 1017 and timestamp > sysdate - 7 group by username, os_username, userhost, client_id, trunc(timestamp) order by trunc(timestamp);

These all queries fetch the data from SYS.AUD$ tables which are enabled by setting audit_trail=DB in init.ora

–For Enable
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
–For Disable
ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;