it is difficult to audit failed sign-on attempts because the user never gets connected to Oracle but you can try this solution below :
Step 1 :
sqlplus / as sysdba
Connected.
SQL> alter system set audit_trail=DB scope=spfile ;
OR
Step 1 :
sqlplus / as sysdba
Connected.
SQL> alter system set audit_trail=DB scope=spfile ;
OR
audit_trail=true
Step 2 :
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.
Step 3 :
sqlplus / as sysdba
SQL> audit session whenever not successful ;
Step 4 :
Test auditing if it works
sqlplus scott/osama ;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Step 5:
sqlplus / as sysdba
Use the below Script :
select os_username, username, terminal, to_char(timestamp,’MM-DD-YYYY HH24:MI:SS’) “TIME” from dba_audit_trail;
OR
select os_username,username,userhost,to_char(timestamp,’mm/dd/yyyy hh24:mi:ss’) timestamp,returncode from dba_audit_session where action_name = ‘LOGON’ and returncode > 0
order by timestamp ;
OS_USERNAME USERNAME TERMINAL TIME
————— ————— ————— ——————-
oracle SCOTT pst/1 07-06-201216:21:13
Enjoy
Osama …
Technology Geek 