This is a short follow-up on my earlier post Don’t ditch AUDIT_TRAIL prematurely.
As it turns out AUDIT_TRAIL is not the only parameter that still affects the behaviour when Unified Auditing is enabled.
Since we have reset all “old” audit parameters except AUDIT_TRAIL the configuration looks like this:

SQL> show parameter audit
NAME                      TYPE        VALUE
------------------------- ----------- --------------------------------
audit_file_dest           string      /u01/app/oracle/admin/DEV1/adump
audit_sys_operations      boolean     TRUE
audit_syslog_level        string
audit_trail               string      DB

Although AUDIT_SYS_OPERATIONS defaults to TRUE we wouldn’t expect audit files being written – we’re using Unified Auditing after all.
To my surprise the Oracle database still writes OS audit files. E.g. here on my playground VM I see MMON slave process writing a file.

ll /u01/app/oracle/admin/DEV1/adump/
-rw-r-----.  1 oracle oinstall 2637 Jan 25 12:59 DEV1_m001_4546_20150125125939459040143795.aud

On customer systems I’ve also seen audit files from Scheduler jobs Jnnn processes. I haven’t figured a pattern yet and it seems to be different on various platforms. On Windows there’s a lot more recorded in the Windows Event Log (mainly AWR activity) than there’s written to ADUMP on Linux.
Solaris is a different story again. What I’ve found reproducible on Linux is connecting with a JDBC client (SQL Developer) and run any query that fails at parse time. Strangely enough, this does not happen when using SQL*Plus (OCI client). E.g.:

Sun Jan 25 14:15:33 2015 +01:00
LENGTH : '175'
ACTION :[18] 'select K from dual'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[3] 'btr'
CLIENT TERMINAL:[7] 'unknown'
STATUS:[3] '904'
DBID:[10] '3434795880'

What gets written to the audit files is not what we have specified in the unified audit policies, so setting AUDIT_SYS_OPERATIONS to FALSE to prevent all the extra auditing appears to be safe. So far, I haven’t seen any adverse effect on the unified audit trail, but you may want to check the proper functioning on your platform and audit configuration yourself.

Foot note 1: If $ORACLE_BASE/admin/$ORACLE_SID/adump directory does not exists AUDIT_FILE_DEST is defaulting to $ORACLE_HOME/rdbms/audit.

Foot note 2: If AUDIT_FILE_DEST is set explicitly to a non-existing directory, you’ll see alert.log entries indicating that the OS audit trail could not be written: “OS Audit file could not be created; failing after 6 retries”

Foot note 3: All test have been run on Oracle 12.1.0.2

Update 10-May-2016: The issue is documented on MOS as bug 21133343 (see MOS note: 21133343.8). Patches are available for Linux x86-64 and AIX on Power.

When migrating to unified auditing in Oracle 12c I faced a strange behaviour in the recording of LOGON / LOGOFF actions.

As the Oracle 12c documentation points out, the AUDIT_* parameters have no effect once you enable unified auditing. Guess what I did in the good spirit of cleaning up obsolete settings:
I reset those parameters in the spfile after enabling unified auditing.

Enable unified auditing by relinking the oracle binary.

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

SQL> show parameter audit_trail
NAME          TYPE        VALUE
------------- ----------- ----------
audit_trail   string      NONE

I disabled all the Oracle built-in audit policies and created my own to record LOGON / LOGOFF actions.

SQL> create audit policy audpol_connection
     actions logon, logoff;

Audit policy created.

SQL> audit policy audpol_connection;

Audit succeeded.

SQL> select * from audit_unified_enabled_policies;

USER_NAME       POLICY_NAME          ENABL SUC FAI
--------------- -------------------- ----- --- ---
ALL USERS       AUDPOL_CONNECTION    BY    YES YES

SQL> select * from audit_unified_policies
     where policy_name = 'AUDPOL_CONNECTION'
     and audit_option like '%LOGO%';

POLICY_NAME          AUDIT CONDI AUDIT_OPTION    AUDIT_OPTION_TY OBJEC OBJEC OBJEC COMMO
-------------------- ----- ----- --------------- --------------- ----- ----- ----- -----
AUDPOL_CONNECTION    NONE  NONE  LOGOFF          STANDARD ACTION NONE  NONE  NONE
AUDPOL_CONNECTION    NONE  NONE  LOGON           STANDARD ACTION NONE  NONE  NONE

This shows, that my audit policy is enabled and should record LOGON and LOGOFF actions.

I also want the audit trail to be written immediately so it becomes visible in UNIFIED_AUDIT_TRAIL view without waiting for the flush to happen (or explicitly flushing it every time).

SQL> exec dbms_audit_mgmt.set_audit_trail_property(
         dbms_audit_mgmt.audit_trail_unified
       , dbms_audit_mgmt.audit_trail_write_mode
       , dbms_audit_mgmt.audit_trail_immediate_write
     )

PL/SQL procedure successfully completed.

For the sake of clean output let’s purge the existing audit trail.

SQL> exec dbms_audit_mgmt.clean_audit_trail(
         dbms_audit_mgmt.audit_trail_unified
       , false
     )

PL/SQL procedure successfully completed.

So far so good. Now begins the actual test case by connecting to the database from a different terminal.

$ sqlplus system/manager@dev1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Tue Oct 21 2014 17:38:49 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

Note that I have not logged out yet…

Checking in my original session we cannot find any LOGON event recorded. I most definitively expected to see a LOGON event.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

no rows selected

After disconnecting from the other session the LOGOFF event becomes visible.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   2253237941   SYSTEM        21-OCT-14 05.13.38.712564 PM    LOGOFF          0

I have run multiple different test with different clients, connection methods, etc. all ending with the same result: Oracle just wont record LOGON actions! We only get to see the LOGOFF event.

Against all odds and feeling a little desperate I eventually decided to set “AUDIT_TRAIL = DB” and give it a go.

SQL> show parameter audit_trail

NAME          TYPE        VALUE
------------- ----------- ----------
audit_trail   string      DB

I purged the audit trail and ran the same test as before again.

Connect from a differen terminal to the database.

$ sqlplus system/manager@dev1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 21 17:39:02 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Tue Oct 21 2014 17:38:49 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

Check the unified audit trail – and there it is.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   1706589952   SYSTEM        21-OCT-14 07.17.46.063184 PM    LOGON           0

After disconnecting from the other session I can again see the LOGOFF action.

SQL> select audit_type, sessionid, dbusername, event_timestamp, action_name, return_code
     from unified_audit_trail
     where action_name like '%LOG%'
     order by event_timestamp;

AUDIT_TYPE SESSIONID    DBUSERNAME    EVENT_TIMESTAMP                 ACTION_NAME     RETURN_CODE
---------- ------------ ------------- ------------------------------- --------------- -----------
Standard   1706589952   SYSTEM        21-OCT-14 07.17.46.063184 PM    LOGON           0
Standard   1706589952   SYSTEM        21-OCT-14 07.18.02.661384 PM    LOGOFF          0

In my opinion this does not work as designed as I believe Oracle actually wanted the AUDIT_* parameters to have no effect on unified auditing. I haven’t tested exhaustively but I imagine there might be other problems with unified auditing when AUDIT_TRAIL is set to NONE. So, test carefully whether all your auditing is working as expected.

Foot note: All test have been run on Oracle 12.1.0.2