2

I'm going to implement asynchronous audit trails functionality for highly loaded system with using of Oracle Streams (for log mining on redo and archive logs). Audit trails in my case mustn't slow down any DML operations over set of my tables. Also audits must contain additional information about end user identity and date and time of modification.

Does someone have experience in implementing audit trails with using of Oracle Streams? Is it good idea to move this way?

Is there any tutorials exist with tips and tricks about implementing audit trails with using of Oracle Streams?

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
Volodymyr Frolov
  • 1,256
  • 5
  • 16
  • 25
  • Do you mean you're using Oracle Streams to replicate your primary database, and you want to add an audit trail to the destination DB so as to not add overhead to the source DB? – kurosch Nov 18 '10 at 18:00
  • @kurosch, no, in my case source and destination DB will be the same (I will just have separate table for audits). I just going to implement audit trails functionality with using of Oracle Streams or with using of any other way, but without adding overhead to the DB. Oracle Streams looks like suitable tool to achieve that goal, but I'm not sure. – Volodymyr Frolov Nov 18 '10 at 18:19
  • Oracle Streams is for replicating DML, I don't see how it would apply here. One way or another you'll need a trigger on a table to capture any changes to it and write the audit information somewhere. Whether you push that into an AQ to be consumed and written by a separate process, or you simply insert a record into a log table, you're going to end up adding some (however small) amount of overhead to every DML – kurosch Nov 18 '10 at 20:02
  • Oh, wait, you're saying you want to build your audit trail right from the redo logs? – kurosch Nov 18 '10 at 20:27
  • @kurosch, yes I want to build audit trails right from redo logs. In my case consumer will be asynchronous, or even moved to separate database, so it shouldn't make any impact on DML operations over audited tables. The idea is to avoid any kind of triggers over these tables. – Volodymyr Frolov Nov 19 '10 at 09:29
  • Ok, reading redo logs is deeper than my understanding. My *guess* is that there's no session context stored in them, so you're probably looking only at the actual data changes and won't have any information about *who* made the change unless you capture that in the data itself, which will require either a trigger or the original DML operation to include the user identification itself, and none of that would help you on DELETEs. – kurosch Nov 19 '10 at 19:04
  • try looking into fine grained audit – janbom Nov 15 '11 at 08:05

2 Answers2

1

Oracle claims the auditing features in the database create an insignificant amount of overhead. Have you tried those as a test case to see how it performs? It doesn't require any DML triggers on the tables. I've used them and got no noticeable difference but the system resources weren't maxed out either.

Using streams for auditing sounds possible but I think it's an overly complicated solution. I supposes you could use streams to replicate transactions to another database and then use the auditing in that database. You're still going to add I/O load to wherever you store your redo logs.

JOTN
  • 6,120
  • 2
  • 26
  • 31
0

Agree with @JOTN. One more thing to add wrt Oracles streams, it is deprecated in 12c and being packaged/offered as 'Golden gate' with a separate license cost.

pahariayogi
  • 1,073
  • 1
  • 7
  • 18