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:

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:

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

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

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

2 thoughts on “Unified Auditing – Performance Improvements in Oracle 12.1.0.2

  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:
    if you call DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS;
    without a previous DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
    (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
    DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS
    while there is a transaction open (which caused insert in unified audit)
    you will create duplicate rows,
    as the DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS
    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”)
    where
    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

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

      Cheers
      Mike

Leave a Reply

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