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 184.108.40.206.
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 220.127.116.11:
- 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 18.104.22.168 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 22.214.171.124 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/126.96.36.199/22782757 $ /u01/app/oracle/product/188.8.131.52/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 184.108.40.206.12 Copyright (c) 2017, Oracle Corporation. All rights reserved. PREREQ session 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-28-09PM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded. [DB12] oracle@localhost:/u01/app/oracle/product/184.108.40.206/22782757
- Apply the patch:
$ /u01/app/oracle/product/220.127.116.11/OPatch/opatch apply Oracle Interim Patch Installer version 18.104.22.168.12 Copyright (c) 2017, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/22.214.171.124 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/126.96.36.199/oraInst.loc OPatch version : 188.8.131.52.12 OUI version : 184.108.40.206.0 Log file location : /u01/app/oracle/product/220.127.116.11/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/18.104.22.168') 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/22.214.171.124' Patching component oracle.rdbms, 126.96.36.199.0... Patching component oracle.rdbms.dbscripts, 188.8.131.52.0... Patch 22782757 successfully applied. Log file location: /u01/app/oracle/product/184.108.40.206/cfgtoollogs/opatch/opatch2017-04-18_14-29-49PM_1.log OPatch succeeded. [DB12] oracle@localhost:/u01/app/oracle/product/220.127.116.11/22782757
- Start the database(s) and run datapatch to apply the required SQL changes:
[DB12] oracle@localhost:/u01/app/oracle/product/18.104.22.168/22782757 $ /u01/app/oracle/product/22.214.171.124/OPatch/datapatch -verbose SQL Patching tool version 126.96.36.199.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 188.8.131.52.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/184.108.40.206/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
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
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
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.
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?
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?
thanks for the hint. These are the news right now:
Bug 26080410: ORA-00600 [KZAN_SET_INTSQL] EXECUTING DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
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 220.127.116.11.
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
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 ?.
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.
Can you do this on RAC one node at a time?
No – that’s a global setting. Every instance will follow the same rules.
Hi there, I just did the UA enabling procedure in a rolling fashion on an Exadata Half Rack nodes, worked well. What we noticed is each node will start generating UA entries only when enabled in the OH on that node. OHs are not shared among cluster nodes in ExaCS.
it needs to be enabled in the kernel with UNIAUD_ON – and if the homes aren’t shared … well, yes … then you will have to do this on each home/node.
Thanks for mentioning this!!!
We have installed the following patch 28186466 (since the patch mentioned on the article is obsolete according to Oracle Support).
This Patch is Obsolete. It cannot be Downloaded.
Bug 22782757 has been replaced by new bug 26080410
The most recent replacement for this patch is 26080410.
26080410 ORA-00600 [KZAN_SET_INTSQL] EXECUTING DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL Patch
Unfortunalty, the audsys.aud$unified table not created on Oracle 12.1 after applying patch 28186466.
(It seems there is an SR on this Topic).
We have an SR opened on Oracle Support…so wait and see….
If someone have any feedack….
sorry but I can’t diagnose this here on the blog. And honestly, I would STRONGLY recommend you to go to 19c (or at least 18.104.22.168) when you want to use Unified Auditing for many reasons.