Why we want Pure Mode?
The first is the audit trails are no longer written to their traditional pre-12c audit locations. Auditing is consolidated into the Unified Audit views and stored using Oracle SecureFiles. Oracle Secured Files use a proprietary format which means that Unified Audit logs cannot be viewed using editors such vi and may preclude or affect the use of third party logging solutions such as Splunk or HP ArcSight.
Operations done by SYS are also recorded.
Unified Auditing comes standard with Oracle Enterprise Edition; no additional license is required. It is installed by default, but not fully enabled by default.
In Command window
rename %ORACLE_HOME%/bin/orauniaud12.dll.dbl file %ORACLE_HOME%/bin/orauniaud12.dll
In sqlplus as SYSDBA
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
In Command window as Administrator
sc stop OracleService<sid>
sc start OracleService<sid>
Now pure mode unified auditing is enabled. Let's check.
In sqlplus as SYSDBA
SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 01:03:46 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options
SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
----------------------------------------------------------------
TRUE
Check out the audit output
SELECT * FROM unified_audit_trail
ORDER BY event_timestamp DESC;
For performance reason, you may want to use queued-write method
In sqlplus as SYSDBA
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_property(
audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_unified,
audit_trail_property => DBMS_AUDIT_MGMT.audit_trail_write_mode,
audit_trail_property_value => DBMS_AUDIT_MGMT.audit_trail_queued_write
);
END;
/
Check configuration
SELECT * FROM dba_audit_mgmt_config_params
order by audit_trail, parameter_name;
Check what is being auditing out of the box
SELECT * FROM audit_unified_policies
ORDER BY policy_name,
audit_option;
SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;
Setup how many days of audit records we want to keep
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_unified,
last_archive_time => SYSTIMESTAMP-90
);
END;
/
Check archive setting
SELECT audit_trail,
last_archive_ts
FROM dba_audit_mgmt_last_arch_ts;
We have to run this for the very first time
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.audit_trail_all,
DEFAULT_CLEANUP_INTERVAL => 24 /*hours*/
);
END;
/
Let's purge manually
SELECT COUNT(*) FROM unified_audit_trail;
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_unified,
use_last_arch_timestamp => TRUE);
END;
/
SELECT COUNT(*) FROM unified_audit_trail;
Let's automate this purging process by setting up scheduled job
DBMS_SCHEDULER.create_job (
job_name => 'audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
l_days NUMBER := 90;
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_unified, TRUNC(SYSTIMESTAMP)-l_days);
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_unified,
use_last_arch_timestamp => TRUE);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=1; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Automatically set audit last archive time.');
END;
/
