Audit Trail Cleanup

Some Oracle Database Security Central plug-ins include audit trail cleanup utilities.

Oracle Database Audit Trail Cleanup

Oracle Database provides the ability to purge audit trails both manually and with scheduled jobs.

About Purging the Oracle Database Target Audit Trail

You can use the DBMS_AUDIT_MGMT PL/SQL package to purge the database audit trail.

The DBMS_AUDIT_MGMT package lets you perform audit trail cleanup tasks such as scheduling purge jobs, moving the audit trail to a different tablespace, setting archive timestamps in the audit trail, and so on. The target database user must have the EXECUTE privilege on DBMS_AUDIT_MGMT to use it.

Oracle Database 11g release 2 ( 11.2) or later includes the DBMS_AUDIT_MGMT package and its associated data dictionary views installed by default. If your target database does not have this package installed, then you can download the package and data dictionary views from My Oracle Support.

Search for Article ID 731908.1.

For details about using the DBMS_AUDIT_MGMT PL/SQL package and views, refer to the following Oracle Database documentation:

The section “Purging Audit Trail Records” in Oracle Database Security Guide for conceptual and procedural information
Oracle Database PL/SQL Packages and Types Reference for reference information about the DBMS_AUDIT_MGMT PL/SQL package
Oracle AI Database Reference for information about the DBA_AUDIT_MGMT_* data dictionary views

Scheduling Automated Purge Jobs

Simplify maintenance by scheduling automated jobs to purge unneeded audit data.

Oracle Database Security Central is integrated with the DBMS_AUDIT_MGMT package on an Oracle Database. This integration automates the purging of audit records from the UNIFIED_AUDIT_TRAIL, AUD$, and FGA_LOG$ tables, and from the operating system .aud and .xml files after they have been successfully inserted into the Audit Vault Server repository.

After the purge is completed, the Audit Vault Agent automatically sets a timestamp on audit data that has been collected. Therefore, you must set the USE_LAST_ARCH_TIMESTAMP property to TRUE to ensure that the right set of audit records are purged. You do not need to manually set a purge job interval.

To schedule an automated purge job for an Oracle Database target:

Log in to SQL*Plus on the target database as a user who has been granted the EXECUTE privilege for the DBMS_AUDIT_MGMT PL/SQL package.

For example:
```
sqlplus tjones
Enter password: <password>
```

Initialize the audit trail cleanup operation.

In the following example, the DEFAULT_CLEANUP_INTERVAL setting runs the job every two hours:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  DEFAULT_CLEANUP_INTERVAL    => 2 );
END;
/

Note:

In case you are collecting audit data from CDB, then execute this step every time there is any change in the PDB instance.
In case you are using a CDB unified audit trail, then use CONTAINER_ALL parameter in the above command.

Verify that the audit trail is initialized for cleanup.

For example:

SET SERVEROUTPUT ON
BEGIN
 IF
   DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL)
 THEN
   DBMS_OUTPUT.PUT_LINE('Database and OS audit are initialized for cleanup');
 ELSE
   DBMS_OUTPUT.PUT_LINE('Database and OS audit are not initialized for cleanup.');
 END IF;
END;
/

Use the DBMS_AUDIT_MGMT.CREATE_PURGE_JOB procedure to create and schedule the purge job.

In this procedure, ensure that you set the USE_LAST_ARCH_TIMESTAMP property to TRUE, so all records older than the timestamp can be deleted.

The following procedure creates a purge job called CLEANUP_OS_DB_AUDIT_RECORDS that will run every two hours to purge the audit records.

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
   AUDIT_TRAIL_PURGE_INTERVAL  => 2,
   AUDIT_TRAIL_PURGE_NAME      => 'CLEANUP_OS_DB_AUDIT_RECORDS',
   USE_LAST_ARCH_TIMESTAMP     => TRUE );
END;
/

How to Prevent Duplication Collection of Audit Trail Data From a Secure Target

Learn how to configure audit trails on Audit Vault Server to collect audit data from registered secure targets while avoiding duplicate collection of data.

AVSYS.CHECKPOINT table stores CHECKPOINT_TIME for each audit trail. It indicates time stamp, up to which, audit records are collected from secure targets audit trail and inserted/committed to AVSYS.EVENT_LOG table.

LAST_ARCHIVE_TS column of DBA_AUDIT_MGMT_LAST_ARCH_TS view is also updated to indicate time stamp, up to which, the audit data has been collected by audit trail. This helps in deciding the purge operation to prevent deleting those records which are yet to be collected by Audit Trails.

However LAST_ARCHIVE_TS column value does not play any role for an Audit trail to decide from where it has to read audit data during next read operation. As Audit Trail will always refer AVSYS.CHECKPOINT table when collector restarts, it will resume collection from CHECKPOINT_TIME. So Audit Trail will not read any record which has a time stamp lesser than CHECKPOINT_TIME.

So it clarifies that Audit Trail is not dependent on value stored in database last archive time stamp at secure target side to decide the point from which it had to collect. Rather it is just an indication for secure target to know that till this time stamp audit data has been collected hence it can be purged.

Note: As it is evident that LAST_ARCHIVE_TS column can be modified manually whereas CHECKPOINT_TIME column AVSYS.CHECKPOINT table in Audit Vault server is manged automatically and not supposed to be modified manually. Therefore these two columns need not necessarily be in sync with each other.

Oracle GoldenGate Extract Cleanup

Learn how to use Oracle GoldenGate extract cleanup and simply maintenance.

Use the Oracle GoldenGate extract cleanup utility to simplify maintenance.

To run the Oracle GoldenGate extract cleanup utility:

Navigate to the following directory on the host machine:

AGENT_HOME\av\plugins\com.oracle.av.plugin.oracle\bin
Run the following command:
```
OracleGoldenGateExtractCleanupHandler <target name> <Agent deployed location>
```
The above command has the following variables:

<target name> is the name of the registered target.

<Agent deployed location> is the full path of the directory where the Agent is deployed.

Note: Ensure to specify the timezone offset when creating the target, using the target attribute av.collector.timezoneoffset. Also ensure the Agent machine and Oracle Database target are in the same timezone.

Microsoft SQL Server Audit Trail Cleanup

If the SQL Server audit trail has collected data from a trace, extended events, or sqlaudit file and that file is inactive, then you can clean up this file. The SQL Server audit trail writes the names of the SQL Server audit text files to a plain text file with the .atc extension. The .atc file resides in the AGENT_HOME\av\atc directory on the computer on which the agent is installed.

To manually clean up files that Oracle Oracle Database Security Central has completed extracting audit records from:

Go to the AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\bin directory of the computer where the Audit Vault Agent is installed.

Ensure that the AGENT_HOME environment variable is correctly set to the directory path where the agent.jar file is extracted.
Run the following utility:
```
SQLServerCleanupHandler <secured_target_name>
```
For example:
```
SQLServerCleanupHandler mssqldb4
```
If you do not set the AGENT_HOME environment variable, you can provide the agent home location in the command line using the following syntax:
```
SQLServerCleanupHandler -securedtargetname <secured_target_name> <agent_home_location>
```
For example:
```
SQLServerCleanupHandler mssqldb4 c:\AV_agent_installation
```
Important: If the name of the Audit Vault Agent installation directory contains spaces, enclose the name in double quotes, for example "C:\Agent Directory".

To automate the cleanup of SQL Server trace files, you can use the Windows Scheduler.

Note:

If the SQL Server trace definition is redefined or reinitialized, then you must ensure that the file names of the trace files do not overlap with trace files that were created earlier.

For example, suppose you start SQL Server with a trace definition in which the trace files names use the following format:

c:\serversidetraces.trc

c:\serversidetraces_1.trc

c:\serversidetraces_2.trc

...

c:\serversidetraces_259.trc

Then you restart the SQL Server with a new trace definition. This new trace definition must use a different file name from the current trace files (for example, the current one named c:\serversidetraces.trc). If you do not, then when you purge the audit trail, the new trace files that have same names as the old ones will be deleted.

Related Topics

Behavior Changes, Deprecated, and Desupported Platforms and Features

Cleaning Up Oracle GoldenGate Extracts

Use the Oracle GoldenGate Extract cleanup utility to simplify maintenance.

Note: To purge collected audit data from a remote collection, you need to set the rollover file number and size. These values are set on the Microsoft SQL Server.

Navigate to the following directory on the host machine:

AGENT_HOME\av\plugins\com.oracle.av.plugin.mssql\bin
Run the following command:
```
SQLServerGoldenGateExtractCleanupHandler.bat <target name> <agent deployed location>
```
\<target name> is the name of the registered target.

\<agent deployed location> is the full path of the directory where the Audit Vault Agent is deployed.

MySQL Audit Trail Cleanup

Use the MySQL audit trail cleanup utility to simplify maintenance.

To run the MySQL audit trail cleanup utility:

On the host machine, go to the directory AGENT_HOME\av\plugins\com.oracle.av.plugin.mysql\bin
Run the following command:

MySQLServerCleanupHandler.bat secured_target_name AGENT_HOME

The above command has the following variables:
- secured_target_name - the name of the MySQL target
- AGENT_HOME - the path to the directory where the Audit Vault Agent is deployed.

Cleaning Up Oracle GoldenGate Extracts

Use the Oracle GoldenGate Extract cleanup utility to simplify maintenance.

Navigate to the following directory on the host machine: AGENT_HOME\av\plugins\com.oracle.av.plugin.mysql\bin
Run the following command:
```
MySQLGoldenGateCleanupHandler <target name> <Agent deployed location>
```
<target name> is the name of the registered target.

<Agent deployed location> is the full path of the directory where the Audit Vault Agent is deployed.

IBM DB2 Audit Trail Cleanup

Learn about using the IBM DB2 scripts to cleanup records.

Refer to Converting Binary Audit Files to ASCII Format for IBM DB2 for information regarding DB2 records cleanup.

PostgreSQL Audit Trail Cleanup

Learn about cleaning up your PostgreSQL Server audit trail.

If PostgreSQL DIRECTORY audit trail has collected data from the *.csv files or if the PostgreSQL TRANSACTION LOG audit trail has collected data from the *.xml files, and if that file is inactive, you can clean up this file.

Clean Up PostgreSQL Directory Trail

To clean up files collected by PostgreSQL DIRECTORY trail follow the below steps:

Navigate to the following directory on the host machine: AGENT_HOME\av\plugins\com.oracle.av.plugin.postgresql\bin
Run the following command:
```
PostgreSQLDirectoryCleanupHandler <target name> <Agent deployed location>
```
<target name> is the name of the registered target.

<Agent deployed location> is the full path of the directory where the Audit Vault Agent is deployed.

Clean Up PostgreSQL Transaction Log Trail

To clean up files collected by PostgreSQL TRANSACTION LOG trail follow the below steps:

Navigate to the following directory on the host machine: AGENT_HOME\av\plugins\com.oracle.av.plugin.postgresql\bin
Run the following command:
```
PostgreSQLGoldenGateCleanupHandler <target name> <Agent deployed location>
```
<target name> is the name of the registered target.

<Agent deployed location> is the full path of the directory where the Audit Vault Agent is deployed.