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.