Enable Auditing for DB user login attempts in 11g

1 - Add initialization parameters & bounce instance:

alter system set audit_trail=db scope=spfile;
audit_file_dest='/u01/app/oracle/mysid/mydir/'


2 - Enable auditing of failed logion attempts :

SQL> audit create session whenever not successful;


To view all the login attempts either success or failure :
========================================

col os_username format a15
col username format a15
col terminal format a15
col to_char(timestamp) format a35
select os_username,username,terminal,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') from dba_audit_trail;


To view the failed login attempts:
========================

set lines 130
col OS_USERNAME for a20
col USERNAME for a20
col USERHOST for a20
select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail
where returncode > 0;



Also we can able to see the sys failed and succesful login attempts in audit_file_dest files.