By Chanaka June 2, 2024

Unified Auditing Housekeeping

Intro

Data is the new currency. It is one of the most valuable organizational
assets, however, if that data is not well protected, it can quickly become a
liability. At an alarming rate we are seeing more and more data breaches that
impact an organization’s brand, their ability to continue to deliver services
and do business, and of course they are impacted financially. We know that
most sensitive data is stored and managed within databases.

Organizations spend colossal sums of money to keep data within compliance
standards. Also, it is crucial to have a centralized and consolidated
auditing mechanism for databases. Oracle Unified Auditing (OUA) is a
comprehensive auditing solution introduced in Oracle Database 12c that
consolidates database-level auditing into a single audit trail. Before
Oracle 12c, auditing in Oracle databases was fragmented, with different
audit trails for different types of activities (e.g., DML, DDL, system
privileges). Unified Auditing streamlines this process by providing a
centralized repository for all audit records, offering a more efficient and
secure auditing mechanism.

For production environments auditing generates a large amount of data,
housekeeping in Oracle Unified Auditing is crucial for maintaining the
integrity, performance, and security of your database environment.

Here are some reasons why housekeeping is important:

Performance Optimization:

Regular housekeeping tasks such as purging old audit records can help
optimize the performance of your database by reducing the size of audit
trails. This can lead to faster query execution and overall improved
database performance.

Compliance Requirements:

Many industries and organizations have regulatory compliance requirements that
mandate the retention and management of audit data. Proper housekeeping
ensures that you meet these compliance standards and avoid potential penalties
or legal issues.

Resource Management:

Auditing can consume significant disk space and system resources if not
managed properly. Housekeeping activities such as archiving or purging old
audit data help free up resources and prevent unnecessary strain on your
database infrastructure.

Security Enhancement:

Regularly reviewing and managing audit data can help identify suspicious
activities or security breaches in your database environment. By maintaining
an organized and up-to-date audit trail, you can quickly detect and respond to
security incidents.

Cost Reduction:

Storing large volumes of audit data for extended periods can incur
additional storage costs. Proper housekeeping practices help control storage
expenses by removing obsolete or unnecessary audit records.

In this article, I will elaborate on how to perform the housekeeping for
unified auditing.

This is the main meta link note from Oracle to perform unified auditing
housekeeping

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

Understand the partition range :

It’s better to get a clear idea of the current partition range interval.
Execute below mentioned query to get the partition interval.

Query


set lines 600
col owner for a10
col table_name for a20 
col INTERVAL for a30 
select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS';


set lines 600
col owner for a10
col table_name for a20 
col INTERVAL for a30 
select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS'
SQL> /

OWNER      TABLE_NAME           INTERVAL                       PARTITION PARTITION_COUNT DEF_TABLESPACE_NAME
---------- -------------------- ------------------------------ --------- --------------- ------------------------------
AUDSYS     AUD$UNIFIED          INTERVAL '1' MONTH             RANGE             1048575 SYSAUX

SQL>

Change partition range

If you need to change the partition range the day , use this below mentioned procedure change the partition range ,
current example is to change partition range to day.


 
 begin
DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL
(INTERVAL_NUMBER  => 1,
INTERVAL_FREQUENCY => 'DAY');
end;
/

Manual Purging

Audit records can be validated from the gv$unified_audit_trail table.

DBSM_AUDIT_TYPE.CLEAN_AUDIT_TRAIL procedure can used to purge data manually.


SQL> select count(*) from gv$unified_audit_trail;

  COUNT(*)
----------
     81134

This is the below mentioned procedure to housekeep manually.

Sample output after running the procedure



BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  FALSE);
END;
/

SQL> select count(*) from gv$unified_audit_trail;

  COUNT(*)
----------
     81134

SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  FALSE);
END;
/  2    3    4    5    6


PL/SQL procedure successfully completed.

SQL> SQL> select count(*) from gv$unified_audit_trail;

  COUNT(*)
----------
         4

SQL>

File location

This is sample location where audit file are located , Once you run the precedure these files automatically maintain.



[oracle@ebs-12-2-12 ~]$ cd /u01/install/APPS/audit/ebscdb/2809223196EC2AF8E053A740D20A4DB6
[oracle@ebs-12-2-12 2809223196EC2AF8E053A740D20A4DB6]$ ls -lrth
total 12K
-rw-------. 1 oracle oinstall 1.5K Apr 11 14:14 ora_audit_03249.bin
-rw-------. 1 oracle oinstall 1.5K Apr 11 14:14 ora_audit_01282.bin
-rw-------. 1 oracle oinstall 1.5K Apr 11 14:14 ora_audit_01233.bin
[oracle@ebs-12-2-12 2809223196EC2AF8E053A740D20A4DB6]$

Schedule a unified audit housekeeping job

You can use this procedure to create a schedular job.


BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'PURGE_UNIFIED_AUDIT_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, SYSTIMESTAMP-31);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  TRUE);
END;',
start_date => '',
repeat_interval => 'freq=daily; byhour=3; byminute=10; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Purge unified audit trail older than 31 days.');
END;
/

Sample output


 
 SQL> BEGIN
  2  DBMS_SCHEDULER.create_job (
job_name => 'PURGE_UNIFIED_AUDIT_JOB',
  3    4  job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, SYSTIMESTAMP-31);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,  5
use_last_arch_timestamp  =>  TRUE);
END;',
start_date => '',
repeat_interval => 'freq=daily; byhour=3; byminute=10; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Purge unified audit trail older than 31 days.');
END;  6
/  7    8    9   10   11   12   13   14   15   16   17

PL/SQL procedure successfully completed.

SQL>

Validate schedular job



set lines 600
COLUMN owner FORMAT A20
COLUMN job_name FORMAT A30
COLUMN job_class FORMAT A30
COLUMN next_run_date FORMAT A36

SELECT owner,
       job_name,
       enabled,
       job_class,
       next_run_date
FROM   dba_scheduler_jobs
where job_name='PURGE_UNIFIED_AUDIT_JOB'
ORDER BY owner, job_name;

OWNER                JOB_NAME                       ENABL JOB_CLASS                      NEXT_RUN_DATE
-------------------- ------------------------------ ----- ------------------------------ ------------------------------------
SYS                  PURGE_UNIFIED_AUDIT_JOB        TRUE  DEFAULT_JOB_CLASS              12-APR-24 03.10.00.826286 AM ETC/UTC

Conclusion

In summary, housekeeping in Oracle Unified Auditing is essential for
ensuring compliance, optimizing performance, enhancing security, and
reducing operational costs in your database environment. By implementing
regular housekeeping tasks, you can maintain a healthy and efficient
auditing system that supports your organization’s goals and objectives.