I activated a ORA_LOGON_FAILURES policy and I've tried to execute

select * from unified_audit_trail where unified_audit_policies = 'ORA_LOGON_FAILURES';

But it doesn't work. All my failed attempts to log in weren't audited. So what do I do wrong?

  • which oracle version you are using? Mar 5, 2017 at 13:48
  • @Md Haidar Ali Khan Mar 5, 2017 at 13:50

1 Answer 1


You have enabled ORA_LOGON_FAILURES, but have you enabled Unified Auditing properly?

With the default, mixed-mode auditing, setting audit_trail to none, prevents logon failures to be audited with ORA_LOGON_FAILURES enabled.

This is how it works with everything set to default (audit_trail set to DB by DBCA):

SQL> audit policy ora_logon_failures;

Audit succeeded.

SQL> select count(*) from unified_audit_trail 
     where unified_audit_policies = 'ORA_LOGON_FAILURES' and return_code = 1017;


SQL> connect 1/1
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
SQL> select count(*) from unified_audit_trail
     where unified_audit_policies = 'ORA_LOGON_FAILURES' and return_code = 1017;


Also, Unified Audit entires can be buffered in the SGA and be written to disk later and not immediately, in that case, flush manually before querying:

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

Still, if you configured everything properly, you may have hit:



Ok, so I had to test it myself, else I would not have believed it. Opposing to what the documentation states (AUDIT_TRAIL), setting the audit_trail to none does have an effect even when using pure Unified Auditing. But this is not intended, it is a bug. The fix is not included in the latest PSU (, but installing the above one-off patch (19383839) indeed resolved the issue. The problem is, this patch is not available to Windows platform. (I have tested this both on Linux and Windows, because Windows is always a factor you need to consider when working with Oracle.)

Setting audit_trail to DB does not reenable mixed-mode, relinking (renaming the DLL) enables pure Unified Auditing and that overrides this, you can confirm this by selecting from AUD$ or DBA_AUDIT_SESSION. So I suggest that you set audit_trail to DB.

  • You have enabled ORA_LOGON_FAILURES . +1 Mar 5, 2017 at 13:57
  • If I got you right, I have to enable mix-mode auditing with AUDIT_TRAIL='db,extended'. Right? Mar 5, 2017 at 13:57
  • @Denis Lolik Not necessarily, audit_trail=db works as well. Mar 5, 2017 at 14:09
  • @Balazs Papp I mean what if I do not activate audit_trail? Mix-mode decreases performance. Mar 5, 2017 at 14:14
  • @Denis Lolik Mixed-mode is the default. You can enable pure Unified Auditing by relinking the binaries with the Unified Auditing option. In a pure Unified Auditing environment, audit_trail has no effect. Still, you have not told us what you did besides activating the ORA_LOGON_FAILURES policy, so I do not know what environment you use. Mar 5, 2017 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.