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 126.96.36.199.
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 (
V$UNIFIED_AUDIT_TRAIL), a massive view with over 90 columns. Furthermore there are other issues when querying the (
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.
- 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 188.8.131.52:
- A new relational partitioned table
AUDSYS.AUD$UNIFIEDgets created. Default interval of 1 month, partition key is column
- A new procedure
SYS.DBMS_AUDIT_MGMTgets 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 184.108.40.206 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
- All attempts to modify entries/metadata in
AUDSYS.AUD$UNIFIEDwill be audited as well
UNIFIED_AUDIT_TRAILview is becoming a
UNION ALLof existing
- Users wanting to get access to
AUDSYSwill need the
SELECT ANY DICTIONARYprivilege.
SELECT ANY TABLEis 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
- Download patch 22782757 for the January 2017 Bundle Patch for Oracle Database 220.127.116.11 from Support.oracle.com and copy it to the patch directory
- Unzip it with:
unzip -d $ORACLE_HOME p22782757_12102170117ProactiveBP_Linux-x86-64.zip
- Shutdown database and all services
- Do a conflict check:
[DB12] oracle@localhost:/u01/app/oracle/product/18.104.22.168/22782757 $ /u01/app/oracle/product/22.214.171.124/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 126.96.36.199.12 Copyright (c) 2017, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/188.8.131.52 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/184.108.40.206/oraInst.loc OPatch version : 220.127.116.11.12 OUI version : 18.104.22.168.0 Log file location : /u01/app/oracle/product/22.214.171.124/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/126.96.36.199/22782757
- Apply the patch:
$ /u01/app/oracle/product/188.8.131.52/OPatch/opatch apply Oracle Interim Patch Installer version 184.108.40.206.12 Copyright (c) 2017, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/220.127.116.11 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/18.104.22.168/oraInst.loc OPatch version : 22.214.171.124.12 OUI version : 126.96.36.199.0 Log file location : /u01/app/oracle/product/188.8.131.52/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/184.108.40.206') 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/220.127.116.11' Patching component oracle.rdbms, 18.104.22.168.0... Patching component oracle.rdbms.dbscripts, 22.214.171.124.0... Patch 22782757 successfully applied. Log file location: /u01/app/oracle/product/126.96.36.199/cfgtoollogs/opatch/opatch2017-04-18_14-29-49PM_1.log OPatch succeeded. [DB12] oracle@localhost:/u01/app/oracle/product/188.8.131.52/22782757
- Start the database(s) and run datapatch to apply the required SQL changes:
[DB12] oracle@localhost:/u01/app/oracle/product/184.108.40.206/22782757 $ /u01/app/oracle/product/220.127.116.11/OPatch/datapatch -verbose SQL Patching tool version 18.104.22.168.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 22.214.171.124.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/126.96.36.199/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:
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
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
OS_USER based on a
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