Martin,
Thanks for the doc reference - I wasn't aware of that backport of 12.2 functionality - it needs it due to Bug 21119008 (which stated "Development have investigated the issue and state that the bug is not feasible to fix in 12.1, but it will be fixed in 12.2")
The "issue" with Unified Auditing in 12.1.0.2 is that its unbelivably slow to query the data. In my experience, trying to get the last 30 minutes worth of records from a small UNIFIED_AUDIT_TRAIL (few million rows) is a 20 minute query at best, and renders Unified Auditing effectively unusable. I have disabled unified auditing it in all of my 12.1 instances. It is worth noting that Unified Auditing is on by default. You may find that performing a "select count(*) from unified_audit_trail" returns quite a few records.
To disable it:
select * from audit_unified_enabled_policies;
noaudit policy <name>
(by default we get ORA_SECURECONFIG and ORA_LOGON_FAILURES enabled - you can find out exactly what they audit in table AUDIT_UNIFIED_POLICIES)
In 12.2, Unified Auditing is now using a interval range partitioned table AUDSYS.AUD$UNIFIED (monthly partitions) to store the data, rather than whatever hokum nonsense they came up with in 12.1
Neil Chandler.
________________________________
From: oracle-l-***@freelists.org <oracle-l-***@freelists.org> on behalf of Martin Berger <***@gmail.com>
Sent: 26 February 2017 16:46
To: ***@gmail.com
Cc: ***@gmail.com; Oracle-l Digest Users
Subject: Re: enq: HW contention..
If you consider going Unified Audit Trail in 12.1, please invest some time to read How To Transfer Unified Audit Records To An Internal Relational Table (Doc ID 2212196.1) and understand what's going on behind.
I did not have time to investigate all the details, But there is a reason why Oracle changed Unified Audit Trail from 12.1 to 12.2 and provides this backport.
hth
berx
2017-02-26 15:49 GMT+01:00 Franky Weber Faust <***@gmail.com<mailto:***@gmail.com>>:
If you are running 12c you probably want to use Unified Audit Trail with queued writes, which stores auditing records in memory first, then flushes them persistently to disk:
SQL> EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,-
DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);
Be aware that you might lose some auditing data if the instance crashes and some records were not yet flushed to disk.
To enable Unified Auditing in Pure Mode check the necessary steps in the documentation.
Kind regards / Cordialmente / Saludos cordiales / Sincères amitiés / Mit freundlichen Grüßen / Cordiali saluti,
Franky Weber Faust
Oracle DBA
Skype: franky.faust
[X][X][X][X]
2017-02-24 11:53 GMT-03:00 Carlos Sierra <***@gmail.com<mailto:***@gmail.com>>:
there are some bugs and some notes on this area. i suggest you browse MoS. You may want to start here:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1434614.1
https://support.oracle.com/epmos/faces/DocumentDisplay?id=2120366.1
https://support.oracle.com/epmos/faces/BugDisplay?id=20448588
On Feb 24, 2017, at 6:48 AM, Tim Gorman <***@gmail.com<mailto:***@gmail.com>> wrote:
Balwanth,
That table is the one underlying views such as DBA_AUDIT_TRAIL, so high INSERT volumes indicate that someone has used the AUDIT command to initiate database auditing to a level which may be growing intrusive.
You may want to examine the DBA_AUDIT_TRAIL view and find what types of auditing are being generated, and decide if it is necessary or not.
Be aware that people sometimes use the AUDIT command for temporary debugging or troubleshooting purposes, and they may forget to NOAUDIT after they find what they are seeking.
Good luck,
-Tim
On 2/24/17 07:26, Balwanth B wrote:
Query is
insert into sys.aud$( sessionid, entryid, statement, ntimestamp#, userid, userhost, terminal, action#, returncode, logoff$lread, logoff$pread, logoff$lwrite, logoff$dead, logoff$time, comment$text, spare1, clientid, sessioncpu, proxy$sid, user$guid, instance#, process#, auditid, dbid) values(:1, :2, :3, SYS_EXTRACT_UTC(SYSTIMESTAMP), :4, :5, :6, :7, :8, :9, :10, :11, :12, cast(SYS_EXTRACT_UTC(systimestamp) as date), :13, :14, :15, :16, :17, :18, :19, :20, :21, :22)
On Fri, Feb 24, 2017 at 9:24 AM, Balwanth B <***@gmail.com<mailto:***@gmail.com>> wrote:
Hello everyone,
we are seeing high enq: HW contention causing by SYS TABLE AUD$ (Table under sys user names AUD..
Any ideas how to resolve this?
Thanks,
Balwanth