Enable Unified Auditing in the Oracle Database

About Unified Auditing:
It is new with Oracle 12c. A unified audit trail ensures that the audit information is consistently formatted and contains consistent fields. Database auditing in 12c can be integrated with the Oracle Audit Vault and Database Firewall, used to control and monitor SQL network activity.

Click here to read more...

Enable Unified audit for Exiting Database:

Step: 1 - Login
Login as "oracle" from OS user

Step: 2 - Export Oracle Home and Path

$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
$ export PATH=/bin:/usr/bin

Step: 3 - Shutdown the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step: 4 - Relink the oracle binaries to turn pure Unified Auditing on

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

Step: 5 - Start the Database:

SQL> startup
ORACLE instance started.

Step:6 - Verify

SQL> select VALUE from V$OPTION where PARAMETER='Unified Auditing';

VALUE
--------------------
TRUE

Performance Impact on Unified audit enable:

Write Mode:

For performance reasons unified auditing uses a queued-write mechanism, so audited actions are written to the SGA and periodically pushed out to disk. This can result in loss of audit records during an instance crash, since the records may not be pushed out to disk before the instance fails. If this represents a problem, the write mode can be altered to immediate-write, so the audit actions are pushed directly to disk, but this will result in auditing having a greater overhead. The following code shows how to switch between these two modes. In a multitenant environment, these setting are container-specific.

-- Switch to immediate-write.

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_immediate_write
  );
END;
/

-- Switched to queued-write

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;
/

The size of the queue in the SGA is controlled by the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter, which can be set to values between 1-30MB, with the default being 1MB. Each RAC instance can have a different value.

As a consequence of running in queued-write mode, in some cases, especially while testing or before maintenance, it may be necessary to flush audit records to the audit trail. This can be done using the FLUSH_UNIFIED_AUDIT_TRAIL procedure. It accepts the FLUSH_TYPE and CONTAINER parameters, which allow the following variations.

-- Flush records to audit trail for the current container in the current instance

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(flush_type => DBMS_AUDIT_MGMT.flush_current_instance);

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(container => DBMS_AUDIT_MGMT.container_current);

BEGIN
  DBMS_AUDIT_MGMT.flush_unified_audit_trail(
    flush_type => DBMS_AUDIT_MGMT.flush_current_instance,
    container  => DBMS_AUDIT_MGMT.container_current);
END;
/

-- Flush records to audit trail for all containers in the current instance.
-- Must be run from root container.

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(container => DBMS_AUDIT_MGMT.container_all);

BEGIN
  DBMS_AUDIT_MGMT.flush_unified_audit_trail(
    flush_type => DBMS_AUDIT_MGMT.flush_current_instance,
    container  => DBMS_AUDIT_MGMT.container_all);
END;
/

-- Flush records to audit trail for the all RAC instances. Current Container.
EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail(flush_type => DBMS_AUDIT_MGMT.flush_all_instances);

BEGIN
  DBMS_AUDIT_MGMT.flush_unified_audit_trail(
    flush_type => DBMS_AUDIT_MGMT.flush_all_instances,
    container  => DBMS_AUDIT_MGMT.container_current);
END;
/

-- Flush records to audit trail for the all RAC instances. All Containers.
-- Must be run from root container.
BEGIN
  DBMS_AUDIT_MGMT.flush_unified_audit_trail(
    flush_type => DBMS_AUDIT_MGMT.flush_all_instances,
    container  => DBMS_AUDIT_MGMT.container_all);
END;
/

Purging the Unified Audit Trail;

Oracle 11g Release 2 introduced the DBMS_AUDIT_MGMT package to manage the audit trail. Oracle 12c uses the same functionality to archive and purge the unified audit trail, described here, with the addition of constants to identify the unified audit trail. For mixed mode auditing environments, remember to manage both the conventional audit trails as well as the unified audit trail.

The following example shows how to set the last archived time for the unified audit trail and manually purge audit records older than that time. Remember to archive records before purging them if you need to store them long term.

-- Set the last archive timestamp.
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type     => DBMS_AUDIT_MGMT.audit_trail_unified,
    last_archive_time    => SYSTIMESTAMP-5,
    --rac_instance_number  =>  1,
    container            => DBMS_AUDIT_MGMT.container_current
  );
END;
/

-- Check the new setting.
COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT audit_trail,last_archive_ts
FROM   dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL          LAST_ARCHIVE_TS
-------------------- ----------------------------------------
UNIFIED AUDIT TRAIL  17-AUG-18 12.15.16.000000 +00:00

SQL>

-- Manually purge the audit trail to the last archive timestamp.

SQL> SELECT COUNT(*) FROM unified_audit_trail;

  COUNT(*)
----------
        6757

SQL>

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;

  COUNT(*)
----------
         3

SQL>