All Apps and Add-ons

Oracle audit .aud files - props settings?

Runals
Motivator

We have multiple Oracle audit databases writing out audit files to the local OS (vs syslog). The way these files work is when a session is opened a log file is generated with a bunch of header and session info. Then when the session is closed Oracle updates that specific audit log. What I'm running into is because the header info is largely redundant, when the session takes longer than 3 seconds Splunk ends up re-indexing the entire contents (which changes the file mod time and by then Splunk has released the file descriptor). I've tried crcSalt but that simply adds overhead to processing and doesn't actually address the re-indexing issue. The example below falls into that 3 second window so wasn't reindexed but the content/formatting is the same as those whose sessions are longer.

My question is outside of coming up with a process to move these file to a separate directory once the session is closed, have folks solved for this problem with particular props settings? BTW if the audit file is deleted after the session is opened (ala batch import) it will not create a new file that contains the session closed info.

Header

Audit file /oracle/audit/dbname/blahblah.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/product/11g203
System name:    Linux
Node name:  jimbo
Release:    2.6.18-371.4.1.el5

Session Opening

Tue Sep  2 06:43:26 2014 -04:00
LENGTH: "326"
SESSIONID:[7] "7154433" ENTRYID:[1] "1" STATEMENT:[1] "1" USERID:[6] "SYSADM" USERHOST:[10] "jimbo" ACTION:[3] "100" RETURNCODE:[1] "0" COMMENT$TEXT:[101] "Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.10)(PORT=56734))" OS$USERID:[5] "psoft" DBID:[9] "495679193" PRIV$USED:[1] "5"

Session Closed

Tue Sep 2 06:43:29 2014 -04:00

LENGTH: "223"

SESSIONID:[7] "7154433" ENTRYID:[1] "1" USERID:[6] "SYSADM" ACTION:[3] "101" RETURNCODE:[1] "0" LOGOFF$PREAD:[1] "0" LOGOFF$LREAD:[3] "350" LOGOFF$LWRITE:[1] "0" LOGOFF$DEAD:[1] "0" DBID:[9] "495679193" SESSIONCPU:[1] "0"

Tags (1)
0 Karma

pmdba
Builder

The white paper Real-Time Oracle 11g Log File Analysis has a section on this (see page 10). Have Oracle write the audit entries to the actual syslog utility on your server and not just to the default "OS" destination; this way all the entries go to the same file, which you can monitor just like any other running log. You can also use the logrotate utility to maintain the file and keep as much history as you need.

I've used this configuration for years and never had a problem with re-indexing.

0 Karma

pmdba
Builder

.aud files should work just as well as a single audit trail file. they're created one for each dedicated server process. if you don't delete them you won't have to deal with new headers all the time; Oracle will just keep appending to existing files as the process ids are re-used and Splunk can tail them just like any other logs.

If possible, push harder on the DBAs. I'm a DBA and I've used this config for years. The audit trail is external to the DB, doesn't impact performance, can be maintained automatically with logrotate, and is easily searchable with Splunk. What's not to like? 😉

0 Karma

pmdba
Builder

Each DB instance on a host should feed to a different syslog level (local3.info, local3.debug, etc.) syslog or rsyslog should be configured to send each feed to an audit file unique to each instance, with entries like this:

local3.=debug /oracle/diag/syslog/oradb1/audit_oradb1.log
local3.=info /oracle/diag/syslog/oradb2/audit_oradb2.log

Splunk can be configured to monitor all available audit logs with wildcards in the monitor path definition, so no updates are required when a new database is created:

[monitor:///oracle/diag/syslog/*/*.log]
disabled = false
followTail = 0
...

0 Karma

Runals
Motivator

Thanks for the link and while I'd like for my DBAs to move to syslog they haven't. Looking for a solution for the actual .aud files.

Also in our tests the syslog out doesn't track (well) which database instance the logs are coming from. Will have to review the document to see if this is addressed - unless you know offhand 😃

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...