Unified Auditing – Performance Improvements in Oracle

Unified Auditing got introduced in Oracle Database 12.1.

Unified Auditing - Performance Improvements in Oracle 12.1The 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

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:

What happens when you apply patch 22782757?

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

  • 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 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
  • 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:

How to migrate to Unified Auditing?

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

----------------  ---------- 
Unified Auditing  TRUE

Apply the patch – step-by-step

  1. Download patch 22782757 for the January 2017 Bundle Patch for Oracle Database 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/
    $ /u01/app/oracle/product/ prereq CheckConflictAgainstOHWithDetail -ph ./
    Oracle Interim Patch Installer version
    Copyright (c) 2017, Oracle Corporation.  All rights reserved.
    PREREQ session
    Oracle Home       : /u01/app/oracle/product/
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/
    OPatch version    :
    OUI version       :
    Log file location : /u01/app/oracle/product/
    Invoking prereq "checkconflictagainstohwithdetail"
    Prereq "checkConflictAgainstOHWithDetail" passed.
    OPatch succeeded.
    [DB12] oracle@localhost:/u01/app/oracle/product/
  5. Apply the patch:
    $ /u01/app/oracle/product/ apply
    Oracle Interim Patch Installer version
    Copyright (c) 2017, Oracle Corporation.  All rights reserved.
    Oracle Home       : /u01/app/oracle/product/
    Central Inventory : /u01/app/oraInventory
       from           : /u01/app/oracle/product/
    OPatch version    :
    OUI version       :
    Log file location : /u01/app/oracle/product/
    Verifying environment and performing prerequisite checks...
    OPatch continues with these patches:   22782757  
    Do you want to proceed? [y|n]
    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/')
    Is the local system ready for patching? [y|n]
    User Responded with: Y
    Backing up files...
    Applying interim patch '22782757' to OH '/u01/app/oracle/product/'
    Patching component oracle.rdbms,
    Patching component oracle.rdbms.dbscripts,
    Patch 22782757 successfully applied.
    Log file location: /u01/app/oracle/product/
    OPatch succeeded.
    [DB12] oracle@localhost:/u01/app/oracle/product/
  6. Start the database(s) and run datapatch to apply the required SQL changes:
    [DB12] oracle@localhost:/u01/app/oracle/product/
    $ /u01/app/oracle/product/ -verbose
    SQL Patching tool version 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 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/

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;


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


Therefore the transfer procedures must be initiated:


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:

    (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:

    while 1=1 loop
    end loop;

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


Elapsed: 00:00:07.43

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


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;

-------- --------------------
SYS      oracle

Elapsed: 00:00:06.98

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

-------- --------------------
SYS      oracle
SYS      oracle 

Elapsed: 00:00:00.20


Share this:

8 thoughts on “Unified Auditing – Performance Improvements in Oracle

  1. Hi Mike,
    I have to jump in on this blog, as we invested some time here.
    I don’t think the backport 22782757 is very mature.
    The first thing we identified:
    (or the system is quite busy and generating audit records between them) you will be rewarded by an
    ORA-00600: internal error code, arguments: [kzan_set_intsql]

    In addition, when you run
    while there is a transaction open (which caused insert in unified audit)
    you will create duplicate rows,
    is doing a
    insert /*+ APPEND */ into audsys.aud$unified

    from gv$unified_audit_trail
    but it deletes
    delete from “CLI_SWP$20628f0a$1$1” partition(“HIGH_PART”)
    max_scn <= :1
    so everything above max_scn (which is any open transaction in our testcase) is not deleted, and will be inserted next time.

    As you can see, Patch 22782757 sounds very promising, but I'm expecting a patch on top of the patch until it's ready for serious production.

    my .02€

    • Martin,

      thanks a lot for your comment. That is the reason why I write this blog – I can learn from your experience. And in this case it’s especially very important as I’ll see a customer in a few weeks who has to take a decision. Did you test with 12.2 already?


  2. Hi, Mike! Suddenly found this article and I wanted to say that patch-approach will fit my current objective perfectly. Need to save historical audit data for reports and if I can move it to convenient table, at the same time, it will be simply perfect solution, because it solves all problems with boringly long quries on unified audit view in 12.1. However, I cannot find patch that you mentioned in this post (22782757). There are links in MOS note that you also generously provide here but they don’t work for me. Is it still available or removed from access?


    • Andrey,

      thanks for the hint. These are the news right now:

      got filed as a regression of the patch.

      This 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. I see that there are backports done already (or at least filed) for

      The actual ORA-600 exists in 12.2 as well (this is the release where the fix has been done for now).

      Cheers and sorry for the inconvenience

  3. Hi Mike ,

    You did a great job by exploring more on unified audit.

    How much time it has taken for you to transfer the unified audit records.

    In my case, since 1 week i am running the Transfer procedure but unfortunately didn’t complete yet.
    Total records to transfer : 23 Millions+
    Transferred : 1 Million+

    Any workaround ?.

    • Hi Erfan,

      this is very unusual. I did transfer a million records within a few minutes in my test env. Did you open an SR?
      Did you check AWR reports while it is running? Any unexpected wait events? Any hangs?

      If opening an SR please prepare AWR reports – and maybe add some systemstate dumps (search on MOS for “systemstate dump”) to it.

      Sorry 🙁

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required


I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.