Tuesday, February 4, 2020

Why is Oracle producing .aud files for internal sys-statements?



I have recently been in contact with Oracle support regarding an issue where my Oracle 18c database instance is sending audit information for internal statements, much similar to this:


Sun Jan 26 10:25:41 2020 +01:00
LENGTH : '401'
ACTION :[147] 'select /*+ opt_param('parallel_execution_enabled',
'false') EXEC_FROM_DBMS_XPLAN */ * from gv$sql_plan where 1=0'
DATABASE USER:[1] '/'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[0] ''
CLIENT TERMINAL:[7] 'UNKNOWN'
STATUS:[1] '0'
DBID:[10] '1325844924'
SESSIONID:[1] '0'
USERHOST:[26] 'myhost.mydomain.com'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'

Sun Jan 26 10:25:41 2020 +01:00
LENGTH : '375'
ACTION :[121] 'SELECT * FROM gv$sql_plan where sql_id = 'a0f1h9d5muwa6' and inst_id = 1 and child_address = hextoraw('00000004FFF16130')'
DATABASE USER:[1] '/'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[0] ''
CLIENT TERMINAL:[7] 'UNKNOWN'
STATUS:[1] '0'
DBID:[10] '1325844924'
SESSIONID:[1] '0'
USERHOST:[26] 'myhost.mydomain.com'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'

If you have migrated to Unified Auditing, Oracle states that "audit records are only expected to be generated in database tables and OS spillover files (*.bin) under audit destination path."

However, dynamic SQL statements parsed or executed using DBMS_SQL package are being audited in the conventional *.aud type OS files.

To get rid of these messages piling up in your audit_dump_dir:
alter system set audit_sys_operations=FALSE scope=spfile;
shutdown immediate
startup

If setting audit_sys_operations to FALSE is not desirable, Oracle states that you can request a patch through the following bug number:


Bug 21133343 *.aud file is generated though unified auditing=true and audit_trail=none


Note that you will see the same phenomenon under the mixed-mode or classic auditing.
Oracle does not explisitly say they will provide a patch in this case though.

Documentation from Oracle support: Doc ID 2020881.1: "OS Audit Files *.aud are Still Generated After Migrating to Unified Audit"


2 comments:

  1. Hi, Would you know what this action means ?
    ACTION NUMBER:[1] '3'

    ReplyDelete
  2. Action number [1] indicates a SELECT statement. Documented here: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/AUDIT_ACTIONS.html#GUID-4EC2B658-F2A4-4E38-9906-A89D5861364C

    ReplyDelete