How to trace failure connection session in Oracle

In Oracle we are going to trace the logon session which is not successfully for some reasons.

Trace with Auditing

1. Check the auditing is enabled.

Show parameter audit

2. If not then enabled the auditing of the database

alter system set audit_trail=db scope=spfile;
shutdown immediate
startup

3. Enable auditing for failed login attempts

audit create session whenever not successful;

4. Check the audit records after sometime for fetching unsuccessful logins:

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

OR

col ntimestamp# for a30 heading "Timestamp"
col userid for a20 heading "Username"
col userhost for a15 heading "Machine"
col spare1 for a15 heading "OS User"
col comment$text for a80 heading "Details" wrap

select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;

Trace with creating a trigger for capturing the logon attempts in alert log file

Following trigger make an entry of logon attempts in alert log file with help of dbms_system package.

1. Create the following trigger with sys user:

CREATE OR REPLACE TRIGGER logon_denied_write_alertlog AFTER SERVERERROR ON DATABASE
DECLARE
l_message varchar2(2000);
BEGIN
-- ORA-1017: invalid username/password; logon denied
IF (IS_SERVERERROR(1017)) THEN
select 'Failed login attempt to the "'|| sys_context('USERENV' ,'AUTHENTICATED_IDENTITY') ||'" schema'
|| ' using ' || sys_context ('USERENV', 'AUTHENTICATION_TYPE') ||' authentication'
|| ' at ' || to_char(logon_time,'dd-MON-yy hh24:mi:ss' )
|| ' from ' || osuser ||'@'||machine ||' ['||nvl(sys_context ('USERENV', 'IP_ADDRESS'),'Unknown IP')||']'
|| ' via the "' ||program||'" program.'
into l_message
from sys .v_$session
where sid = to_number(substr(dbms_session.unique_session_id,1 ,4), 'xxxx')
and serial# = to_number(substr(dbms_session.unique_session_id,5 ,4), 'xxxx');

-- write to alert log
sys.dbms_system.ksdwrt( 2,l_message );
END IF;
END;
/

2. If not need drop this triggers

drop trigger logon_denied_write_alertlog;