Unified Auditing – Performance Improvements in Oracle 12.1.0.2

Unified Auditing got introduced in Oracle Database 12.1.

The downsides of the “old” auditing facilities became obvious when too many users had activities or transactions at the same time leading to audit records being written into AUD$. Contention was a typical issue. The same thing happened when too many users tried to login at the same time. Furthermore protecting the auditing information required Database Vault as there was no default protection available.

This – and some other things – should be remedied by Unified Auditing which is available since Oracle Database 12c. It gets enabled in sort of a “mixed mode” where Unified Auditing is not linked into the Oracle kernel by default. The same applied to Oracle Database 12.2.

At last year’s DOAG Conference in November in Nürnberg a customer approached me being really angry about Unified Auditing performance in Oracle 12.1.0.2.

How does Unified Auditing write audit records?

With Unified Auditing, audit records get written into an in-memory SGA queue. The writes will be very fast. That is the good part. The not so good aspect gets visible when you compare a million audit records in the old AUD$ table vs. querying from UNIFIED_AUDIT_TRAIL (which uses the underlying (G)V$UNIFIED_AUDIT_TRAIL), a massive view with over 90 columns. Furthermore there are other issues when querying the (G)V$UNIFIED_AUDIT_TRAIL, for instance no parallelism, no indexes on fixed tables, and some others. And as the underlying data is in a SecureFile LOB the data needs to be transformed in order to query it efficiently.

This architecture got changed significantly in Oracle Database 12.2.

But part of the changes done for Oracle Database 12.2 can be made available in Oracle Database 12.1 by applying patch 22782757.

See also:

• MOS Note 2063340.1
  Performance Issues While Monitoring the Unified Audit Trail of an Oracle12c Database

What happens when you apply patch 22782757?

With this patch the following things will be introduced into Oracle Database 12.1.0.2:

• A new relational partitioned table AUDSYS.AUD$UNIFIED gets created. Default interval of 1 month, partition key is column EVENT_TIMESTAMP
• A new procedure TRANSFER_UNIFIED_AUDIT_RECORDS in package SYS.DBMS_AUDIT_MGMT  gets created to transfer the unified audit records from the CLI backend table to AUDSYS.AUD$UNIFIED. Please see MOS Note: 2212196.1
  How To Transfer Unified Audit Records To An Internal Relational Table
• The way how audit records get written in Oracle Database 12.1.0.2 does not get changed by the patch
• A new ORA-46385 (DML and DDL operations are not allowed on table “string”.”string”.  Cause: A DML or DDL operation was attempted on a unified auditing internal table) gets introduced and raised when one attempt to modify an entry in the new AUDSYS.AUD$UNIFIED table
• All attempts to modify entries/metadata in AUDSYS.AUD$UNIFIED will be audited as well
• UNIFIED_AUDIT_TRAIL view is becoming a UNION ALL of existing GV$UNIFIED_AUDIT_TRAIL and new  AUDSYS.AUD$UNIFIED
• Users wanting to get access to AUDSYS will need the SELECT ANY DICTIONARY privilege. SELECT ANY TABLE is not sufficient anymore
• Some minor changes will happen as well

How to link Unified Auditing into the oracle kernel

Not sure why enabling/disabling Unified Auditing via chopt is not available – in fact you’ll have to link it in manually if you’d like to use Unified Auditing. See here for some information and how to link it:

Basically the steps are:

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

Then check afterwards that the option is visible:

$ sqlplus / as sysdba

SQL> SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
PARAMETER         VALUE
----------------  ---------- 
Unified Auditing  TRUE

Apply the patch – step-by-step

1. Download patch 22782757 for the January 2017 Bundle Patch for Oracle Database 12.1.0.2 from Support.oracle.com and copy it to the patch directory
2. Unzip it with:
   unzip -d $ORACLE_HOME  p22782757_12102170117ProactiveBP_Linux-x86-64.zip 
3. Shutdown database and all services
4. Do a conflict check:

   [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
   $ /u01/app/oracle/product/12.1.0.2/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
   Oracle Interim Patch Installer version 12.1.0.1.12
   Copyright (c) 2017, Oracle Corporation.  All rights reserved.
   
   PREREQ session
   
   Oracle Home       : /u01/app/oracle/product/12.1.0.2
   Central Inventory : /u01/app/oraInventory
      from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
   OPatch version    : 12.1.0.1.12
   OUI version       : 12.1.0.2.0
   Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2017-04-18_14-28-09PM_1.log
   
   Invoking prereq "checkconflictagainstohwithdetail"
   
   Prereq "checkConflictAgainstOHWithDetail" passed.
   
   OPatch succeeded.
   [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
   

5. Apply the patch:

   $ /u01/app/oracle/product/12.1.0.2/OPatch/opatch apply
   Oracle Interim Patch Installer version 12.1.0.1.12
   Copyright (c) 2017, Oracle Corporation.  All rights reserved.
   
   
   Oracle Home       : /u01/app/oracle/product/12.1.0.2
   Central Inventory : /u01/app/oraInventory
      from           : /u01/app/oracle/product/12.1.0.2/oraInst.loc
   OPatch version    : 12.1.0.1.12
   OUI version       : 12.1.0.2.0
   Log file location : /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2017-04-18_14-29-49PM_1.log
   
   Verifying environment and performing prerequisite checks...
   OPatch continues with these patches:   22782757  
   
   Do you want to proceed? [y|n]
   y
   User Responded with: Y
   All checks passed.
   Provide your email address to be informed of security issues, install and
   initiate Oracle Configuration Manager. Easier for you if you use your My
   Oracle Support Email address/User Name.
   Visit http://www.oracle.com/support/policies.html for details.
   Email address/User Name: 
   
   You have not provided an email address for notification of security issues.
   Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
   
   Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
   (Oracle Home = '/u01/app/oracle/product/12.1.0.2')
   
   Is the local system ready for patching? [y|n]
   y
   User Responded with: Y
   Backing up files...
   Applying interim patch '22782757' to OH '/u01/app/oracle/product/12.1.0.2'
   
   Patching component oracle.rdbms, 12.1.0.2.0...
   
   Patching component oracle.rdbms.dbscripts, 12.1.0.2.0...
   Patch 22782757 successfully applied.
   Log file location: /u01/app/oracle/product/12.1.0.2/cfgtoollogs/opatch/opatch2017-04-18_14-29-49PM_1.log
   
   OPatch succeeded.
   [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
   

6. Start the database(s) and run datapatch to apply the required SQL changes:

   [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
   $ /u01/app/oracle/product/12.1.0.2/OPatch/datapatch -verbose
   SQL Patching tool version 12.1.0.2.0 Production on Tue Apr 18 14:36:13 2017
   Copyright (c) 2012, 2017, Oracle.  All rights reserved.
   
   Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_11812_2017_04_18_14_36_13/sqlpatch_invocation.log
   
   Connecting to database...OK
   Bootstrapping registry and package to current versions...done
   Determining current state...done
   
   Current state of SQL patches:
   Patch 22782757 (ER : BACKPORT PROJ #46892 TO 12.1):
     Installed in the binary registry only
   Patch 25355562 (MERGE REQUEST ON TOP OF DATABASE BP 12.1.0.2.170117 FOR BUGS 20602794 20807398):
     Installed in the binary registry and the SQL registry
   Bundle series DBBP:
     ID 170117 in the binary registry and ID 170117 in the SQL registry
   
   Adding patches to installation queue and performing prereq checks...
   Installation queue:
     Nothing to roll back
     The following patches will be applied:
       22782757 (ER : BACKPORT PROJ #46892 TO 12.1)
   
   Installing patches...
   Patch installation complete.  Total patches installed: 1
   
   Validating logfiles...
   Patch 22782757 apply: SUCCESS
     logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/22782757/20980464/22782757_apply_DB12_2017Apr18_14_36_27.log (no errors)
   SQL Patching tool complete on Tue Apr 18 14:36:28 2017
   [DB12] oracle@localhost:/u01/app/oracle/product/12.1.0.2/22782757
   

Transfer Audit Records

Now as the patch is applied the new table does exist – but it has no rows.

SQL> select count(*) from AUDSYS.AUD$UNIFIED;

  COUNT(*)
----------
	 0

SQL> select count(*) from v$unified_audit_trail;

  COUNT(*)
----------
    726823

Therefore the transfer procedures must be initiated:

SQL> exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
SQL> exec DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;

Once this is done (in a loop for the first time) the contents of the views above have changed – V$UNIFIED_AUDIT_TRAIL is empty whereas AUDSYS.AUD$UNIFIED has now all the information. While MOS Note: 2212196.1: How To Transfer Unified Audit Records To An Internal Relational Table recommends to do the transfer within a WHILE … LOOP I received more predictable results by doing this as a recurring job once every 5 minutes or so. And it’s important to do it only on one node on a RAC system as otherwise you may get duplicates.

Furthermore my first attempt resulted in an ORA-600 – and in an non-CDB so even though the bug indicated CDB this is independent of non-CDB/CDB setups:

• Bug 25851605: ORA-00600 [KZAN_SET_INTSQL] WHEN PURGING UNIFIED AUDIT DATA IN CDB
• Bug 26080410: ORA-00600 [KZAN_SET_INTSQL] EXECUTING DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
  (added on July 15, 2017)

The latter bug lead to a (temporary) removal of Patch 22782757 – and MOS does not give you any indication why the patch has been removed. Please check with Support for availability of Patch 22782757 – and mention bug 26080410.

Interesting note on the side:
The DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS procedure is designed to deal with only a predefined number of rows from CLI back end table per execution. So initially you may have to run the procedure in a WHILE … LOOP to move all the records over:

 begin
    DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
    while 1=1 loop
       DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;
    end loop;
 end;
/

But once this is completed (check with “select count(*) from v$unified_audit_trail” and then CTRL-C the program) I had a job kicking off every 5 or 10 minutes simply doing:

SQL> begin
  2   DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
  3   DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;
  4   end;
  5  /

As a result of this action the audit information can be now queried much more efficient from AUDSYS.AUD$UNIFIED than from V$UNIFIED_AUDIT_TRAIL.

Two simple comparisons between the two tables, each of them filled with roughly 800,000 audit records. At first a simple COUNT(*):

SQL> select count(*) from v$unified_audit_trail;

  COUNT(*)
----------
    800057

Elapsed: 00:00:07.43

SQL> select count(*) from audsys.aud$unified;

  COUNT(*)
----------
    833122

Elapsed: 00:00:00.15

And then a lookup of USERID and OS_USER based on a STATEMENT_ID:

SQL> select userid, os_user from v$unified_audit_trail where statement_id=769436;

USERID	 OS_USER
-------- --------------------
SYS      oracle

Elapsed: 00:00:06.98


SQL> select userid, os_user from audsys.aud$unified where statement_id=178528;

USERID   OS_USER
-------- --------------------
SYS      oracle
SYS      oracle 

Elapsed: 00:00:00.20

–Mike