How To Track Failed Logon Attempts using Oracle Unified Auditing

If you enable the unified audit feature announced with 12c, all audit records will be in the UNIFIED_AUDIT_TRAIL table. Logon failure records will not be in this table because the corresponding policy is not enabled by default. The policy ORA_LOGON_FAILURES must be enabled to audit the Failed Logon attempts.

Enable ORA_LOGON_FAILURES to track failed logon attempts

You can enable the ORA_LOGON_FAILURES policy as follows.

Query Failed Logon Attempts

Records will then be created. You can see the number of records related to this policy with a query like the one below.

Cleaning SYSAUX Tablespace Reqularly

If the ORA_LOGON_FAILURES policy is enabled, your SYSAUX tablespace can be full in a short time, as too many records will be created. You can learn more about SYSAUX tablespace in the article named “SYSAUX Tablespace Usage”.

You can create a job to automatically remove old records. The following command can be used to create a job that will delete every 24 hours. The first deletion will take place 24 hours after you run the command.

The following command can be used to change the job’s schedule.