Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 | SQL> audit policy ORA_LOGON_FAILURES; Audit succeeded. |
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.
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT audit_type, unified_audit_policies, action_name, return_code, COUNT (*) FROM unified_audit_trail WHERE unified_audit_policies = 'ORA_LOGON_FAILURES' GROUP BY audit_type, unified_audit_policies, action_name, return_code ORDER BY COUNT (*); |
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.
1 2 3 4 5 6 7 8 9 | BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB ( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_PURGE_INTERVAL => 24, AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_Purge_Job', USE_LAST_ARCH_TIMESTAMP => TRUE, CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT); END; / |
The following command can be used to change the job’s schedule.
1 2 3 4 5 6 | BEGIN DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL( AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_Purge_Job', AUDIT_TRAIL_INTERVAL_VALUE => 48); END; / |
