Differences

This shows you the differences between two versions of the page.

--- databaseauditing [2015/07/21 08:24] – z0hpvk
+++ databaseauditing [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 ===== Database Auditing =====
-==== Turn Off Create Session Auditing ====
+==== Disabling Create Session Auditing ====
 By default, from 11gR1, when a database is created using DBCA, the database auditing parameter will be turned on.
@@ Line 19: / Line 19: @@
 This still means that failed logins are audited.
-==== View Audit Trail ====
+==== Viewing Audit Trail ====
 SQL below will show everything that has been audited today
@@ Line 25: / Line 25: @@
 <code>alter session set nls_date_format = 'DD-MON-YYYY HH24:MI';
 set linesize 160
-col os_username format a20
+col os_username format a15
-col userhost format a25
+col userhost format a45
-select os_username, username, action, userhost, timestamp
+select os_username, username, returncode, userhost, timestamp
 from dba_audit_trail
 where timestamp > trunc(sysdate)
@@ Line 34: / Line 34: @@
 ==== Purging Audit Trail ====
+Copy sys.aud$ table to the SYSAUX tablespace
+<code>BEGIN
+  DBMS_AUDIT_MGMT.INIT_CLEANUP(
+    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
+    DEFAULT_CLEANUP_INTERVAL => 24
+  );
+END;
+/</code>
+Create a job to set the Archive Timestamp. In this case it will be over 30 days
+<code>BEGIN
+  DBMS_SCHEDULER.CREATE_JOB (
+    job_name   => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
+    job_type   => 'PLSQL_BLOCK',
+    job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
+                   DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => sysdate-30); END;',
+    start_date => sysdate,
+    repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
+    enabled    =>  TRUE,
+    comments   => 'Create an archive timestamp'
+  );
+END;
+/</code>
+Create a purge job which will delete all audit entries older than the Archive Timestamp.
+<code>BEGIN
+  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
+    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
+    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
+    AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job',
+    USE_LAST_ARCH_TIMESTAMP    => TRUE
+  );
+END;
+/</code>