Oracle Database 23ai introduces several enhancements to unified auditing, focused on flexibility, and effectiveness. Some of the new key features are:

Desupport of Traditional Auditing: Traditional auditing is desupported in Oracle 23ai, which means all auditing must now be done using unified auditing. Unified auditing consolidates audit records into a single, secure trail and supports conditional policies for selective auditing.

Column-Level Audit Policies: One of the most notable features is the ability to create audit policies on individual columns of tables and views. This granularity control allows organizations to focus their audit efforts on the most sensitive data elements, reducing the volume of audit data and enhancing security by targeting specific actions like SELECT, INSERT, UPDATE, or DELETE on specified columns​. It can be implemented like:

conn / as sysdba
noaudit policy test_audit_policy;
drop audit policy test_audit_policy;
create audit policy test_audit_policy
	actions update(col1, col2) on userx.audit_test_tab,
	select(col2) on userx.audit_test_tab
	container = current;
audit policy test_audit_policy;

Enhanced Integration with IAM and Cloud Services: Oracle 23ai includes improved authentication and authorization capabilities for IAM users, supporting Azure AD OAuth2 tokens and allowing seamless integration with Oracle Autonomous Database on Dedicated Exadata Infrastructure.

New Cryptographic Algorithms and Security Enhancements: The release includes support for new cryptographic algorithms such as SM2, SM3, SM4, and SHA-3, along with enhancements to the DBMS_CRYPTO package and orapki utility.

Consolidation of FIPS 140 Parameter: Oracle 23c introduces a unified FIPS_140 parameter for configuring FIPS across various database environments, including Transparent Data Encryption (TDE) and network encryption, streamlining the compliance process and improving security management​.

Cleanup of audit trail

Also for the reason that unified audit is now standard, I would like to summarize the management and cleanup of the audit trail in detail here:

First of all you should check the size of the existing audit tables (AUD$ and FGA_LOG$), create a new audit-tablespace for the audit tables:

set lin 999
set pages 999
col tablespace_name for a30
col segment_name for a30
col owner for a20
select owner, segment_name, tablespace_name, bytes/1024/1024 MB 
from dba_segments 
where segment_name in ('AUD$', 'FGA_LOG$');
-- create audit tablespace
create tablespace audit_ts;
select file_name, file_id, tablespace_name, autoextensible, maxbytes/1024/1024/1024 GB 
from dba_data_files 
where tablespace_name like 'AUD$';
-- alter database datafile … autoextend on next 100M maxsize 1G;
-- eventually create an audit history table for further use
create table AUD_HIST tablespace audit_ts as select * from AUD$;
-- truncate table sys.aud$;

Now you can move the audit tables from sysaux to the audit-tablespace:

--move AUD$
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;
/
--move FGA_LOG$
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDIT_TS');
END;
/
--moving additional audit tables
alter table FGACOL$ move tablespace AUDIT_TS;

Short check of audit-parameters:

-- check audit parameters
col parameter_name for a30
col parameter_value for a20
col audit_trail for a20
show parameter audit
SELECT * FROM dba_audit_mgmt_config_params;

Before starting any cleanup you should be sure that no old jobs exist:

-- deinit all purge jobs
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_all );
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_os );
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_xml );
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_aud_std );
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_fga_std )

Now you can set up an audit-management, e.g. every 24 hours, which purges audit-entries older than 7 months:

-- init cleanup for 24 hours
exec DBMS_AUDIT_MGMT.INIT_CLEANUP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_all, 24 );
-- set retention (last_archive_timestamp, sysdate -210 days) =7 months
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, to_timestamp(sysdate-210));
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, to_timestamp(sysdate-210));
exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( dbms_audit_mgmt.audit_trail_unified, sysdate-210);
-- 1 means RAC-node 1
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_OS, to_timestamp(sysdate-210), 1);
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_XML, to_timestamp(sysdate-210), 1);

Short check of parameter settings:

-- getting parameters
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
col days_back  for 9999  head "days|back"
select audit_trail, rac_instance, last_archive_ts, extract(day from(systimestamp-last_archive_ts))days_back from DBA_AUDIT_MGMT_LAST_ARCH_TS;

Now you can set up and initiate the purge-jobs:

-- creating purge job for all audit trails
BEGIN 
dbms_audit_mgmt.create_purge_job(
audit_trail_type => dbms_audit_mgmt.audit_trail_all,
audit_trail_purge_interval => 24  /* hours */,
audit_trail_purge_name => 'DAILY_AUDIT_PURGE',
use_last_arch_timestamp => TRUE);
END;
/
-- create job for automatic updating last_archive_timestamp
create or replace procedure AUDIT_UPDATE_RETENTION( m_purge_retention  IN  number  DEFAULT 210
) AS
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TO_TIMESTAMP(SYSDATE-m_purge_retention));
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TO_TIMESTAMP(SYSDATE-m_purge_retention), 1);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TO_TIMESTAMP(SYSDATE-m_purge_retention), 1);
END;
/

You can test it now:

-- test run
exec audit_update_retention(210);

Before we invoke the new job, we get sure that no old bodies are left:

-- drop job if exists
set serveroutput on
begin
dbms_scheduler.drop_job(job_name => 'DAILY_AUDIT_UPDATE_RETENTION');
end;
/
-- create job for last_archive_timestamp
set serveroutput on
declare
m_purge_retention number DEFAULT 210;
ex_job_doesnt_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_job_doesnt_exist, -27475);
begin
--dbms_scheduler.drop_job(job_name => 'DAILY_AUDIT_UPDATE_RETENTION');
--dbms_output.put_line(chr(13)||'= 60;
DBMS_SCHEDULER.create_job (
job_name => 'DAILY_AUDIT_UPDATE_RETENTION',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TO_TIMESTAMP(SYSDATE-m_purge_retention));
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TO_TIMESTAMP(SYSDATE-m_purge_retention), 1);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TO_TIMESTAMP(SYSDATE-m_purge_retention), 1);
END;', 
start_date => SYSTIMESTAMP, 
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;', 
end_date => NULL, 
enabled => TRUE, 
auto_drop => FALSE, 
comments => 'every day, update last_archive_timestamp (which DAILY_AUDIT_PURGE uses) to '||m_purge_retention||' days back.'
);
--Exception
--when ex_must_be_declared then
--dbms_output.put_line('DAILY_AUDIT_UPDATE_RETENTION: component missing?'||chr(13));
END;
/

An other option would be to start the jon manually or to purge the audit-trail on demand:

-- manual start of purge jobs
select distinct job_name, owner from dba_scheduler_jobs;
exec dbms_scheduler.run_job('DAILY_AUDIT_UPDATE_RETENTION',use_current_session=>false);
exec dbms_scheduler.run_job('audsys.DAILY_AUDIT_PURGE',use_current_session=>false);
-- other options
exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
-- or
begin
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
for i in 1..10 loop
DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;
end loop;
end;
/
begin
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => FALSE);
end;
/

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2904294_1.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/AUDIT-Unified-Auditing.html

https://oracle-base.com/articles/23/auditing-enhancements-23

Purging Unified Audit Trail in 12cR2
From Oracle Standard Auditing to Oracle Unified Auditing
Upgrade to Oracle 19c – performance issue
Post Views: 900
Thumbnail [60x60]
by
Christian Weinfurtner