Monthly Archives: January 2015

Mashing up audit parameters

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.