Cleaning Up older records in AUD$ table

Auditing refers to the process of monitoring and collecting specific user database actions and storing this information in designated tables. It is a feature that is consistently enabled in Oracle Database. During or after the execution of audited SQL statements, Oracle Database generates audit records. However, it’s important to note that the growth of the AUD$ table can have an impact on the overall performance of the database.

The AUD$ table is primarily used for the purpose of database auditing, including:

Storing audit records: The AUD$ table serves as the repository for storing the audit records generated by Oracle Database during or after the execution of audited SQL statements.
Tracking user actions: The table records information about various user actions, such as logins, logouts, data modifications, privilege changes, and other database activities.
Compliance and security: The audit records stored in the AUD$ table are essential for meeting regulatory compliance requirements and ensuring the security of the database. They provide an audit trail that can be reviewed for investigation, analysis, and forensic purposes.
Monitoring user activity: By analyzing the data in the AUD$ table, administrators can monitor and analyze user activity to identify potential security breaches, suspicious behavior, or policy violations.
Reporting and analysis: The AUD$ table data can be queried and analyzed to generate reports on user actions, system usage, and overall database activity, enabling administrators to gain insights and make informed decisions regarding database performance, security, and compliance.

While the AUD$ table is critical for auditing purposes, it’s important to manage its growth effectively to avoid performance impacts on the database. This can involve implementing retention policies, archiving old audit data, or utilizing other auditing features available in Oracle Database to strike a balance between auditing requirements and performance considerations.

Starting from Oracle Database 11g and onwards, when the AUDIT_TRAIL parameter is set to either the “DB” or “DB_EXTENDED” value, the auditing feature is activated by default. This means that the database will automatically generate audit records for audited SQL statements and store them in the AUD$ table. The default value for AUDIT_TRAIL is “DB”, indicating that the database auditing is enabled.

By setting AUDIT_TRAIL to “DB”, the database ensures that basic auditing is performed, capturing essential information about user actions and database activities. The “DB_EXTENDED” value provides more detailed auditing, capturing additional information such as bind variables and other specific details related to the SQL statements.

It’s important to note that while the AUDIT_TRAIL parameter is set to “DB” by default, it can be modified to other values based on specific auditing requirements. This allows administrators to customize the auditing configuration according to their needs, whether it involves more detailed auditing, sending audit records to the operating system audit trail, or redirecting them to a different destination.

Overall, starting from Oracle Database 11g, AUDIT_TRAIL is activated by default, set to “DB”, ensuring that the database generates audit records for audited SQL statements, unless it is explicitly disabled or modified to a different value.

To maintain the AUD$ table and manage its growth effectively, you can follow these step-by-step actions:

1. First, Check your current parameters

SQL> show parameter                                                                 

    NAME           TYPE    VALUE
--------------   -------   -----  
audit_trail       string    DB

2. Let us check which tablespace is using by AUD$ table. Default tablespace is in SYSTEM tablespace.

SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024/1024) as SizeinGB from dba_segments where segment_name=’AUD$’;

OWNER SEGMENT_NAME     SEGMENT_TYPE TABLESPACE_NAME SizeinGB
------ -------------- ------------ ---------------- ----------
SYS         AUD$        TABLE          SYSTEM          115

3. In In this case, our Auditing table AUD$ is in SYSTEM tablespace so we will create a new tablespace named “AUDIT_TS” and then move the AUD$ to our newly created Tablespace

CREATE TABLESPACE AUDIT_TS DATAFILE ‘/path/to/audit_ts.dbf’ SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED;

Note: Add Datafiles More Datafiles to the Tablespace created according to the size of your AUD$ Table

4. After creating a new tablespace run the below command as sysdba user.

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'AUDIT_TS');
END;
/

5. Now check AUD$ table is moved to our new tablespace with the query given below:

SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024/1024) as SizeinGB from dba_segments where segment_name=’AUD$’;

OWNER SEGMENT_NAME     SEGMENT_TYPE TABLESPACE_NAME SizeinGB
------ -------------- ------------ ---------------- ----------
SYS         AUD$        TABLE          AUDIT_TS       115

6. Now we can use some options to maintain for SYS.AUD$ table.,

Here are Some options that can be used to Maintain and Purge the Audit Records:

1: Regularly run the truncate command for SYS.AUD$ table by Syntax given below:

TRUNCATE TABLE SYS.AUD$;

2: Purge the audit trail records by using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure by Syntax given below:

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

3: Initialize the clean job. If you haven’t moved the AUD$ table out of the SYSTEM tablespace, then the below script will move the AUD$ to the SYSAUX tablespace by default. Syntax is:

BEGIN
DBMS_AUDIT_MGMT.init_cleanup(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,default_cleanup_interval => 12 /* set_hours */);
END;
/

Check whether initialization is a success or not by the Procedure Given Below:

SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/