Re: SOX Reporting Requirement

Please forgive the shameless plug as I work for Delphix (full disclosure), but...

Delphix takes a copy of a source (usually production) database and stores it within the Delphix Appliance as a "dSource". The dSource provides you the ability to provision virtual databases starting from any point-in-time along a "TimeFlow". Virtual databases (VDBs) can be quickly generated at any point along the TimeFlow. For Oracle databases, a TimeFlow is defined by the configured retention of Oracle level-1 incremental backups and redo.

With the default TimeFlow duration of two weeks, the size of a dSource within Delphix is generally 40-50% of the size of the Oracle database from which is was created, depending on the volume of activity. Increase the TimeFlow, and the size of the dSource increases. So, it would not be out of the question to have a TimeFlow of a year or more, if the requirement is to recall the data for a year (or more).

When a request is made to view data at any point along the TimeFlow, one could generate a VDB in minutes, point a query tool at it, and look at the state of the database at that point in time. Numerous VDBs at different points along the TimeFlow could be spun up as needed, and destroyed after we're done with them.

The big benefit of virtual databases is that they take up practically no space of their own, despite the size of the source database.

The other huge benefit is more apparent to end-users than to techies like us. Individual tables are generally not a complete "entity" in the application data model. Consider that an "invoice" in Oracle E-Business Suites, for example, is comprised of at least 10 (or more) individual tables, if I recall correctly. Trapping deltas to each of those 10 or more individual tables is a long way from re-materializing an "invoice" so that end-users can read it.

It is a whole lot easier if you could just point the application software at a database rewound to the desired point-in-time, and use the application's logic to re-materialize the state of the "invoice" at that point-in-time.

Capturing the deltas from component tables and displaying them using TOAD or SQL*Plus is the equivalent of asking someone to look at a binary dump. They may piece together what they want to see once or twice, but they'll be loathe to ever do it again.

Just some facets of the problem to consider, outside of the usual audit-trail thought process.

This whole scenario -- SOX compliance -- is one of the basic use-cases for Delphix, alongside the more usual data agility scenarios to accelerate DevOps, etc.

Hope this helps!

Thanks!

-Tim

On 8/28/14, 11:28, David Barbour wrote:
> I was trying to avoid triggers by using the built-in features.
> Fine-Grained Auditing provides the same functionality as db, extended
> on a table basis which gives me when and what the changes are, but not
> what the prior values were. Reading up on Mark B.'s suggestion
> involving Total Recall, it looks like that might fit the bill and
> alleviate concerns regarding undo retention as well.
>
> I'll give it a try and let you know what happens.
>
>
> On Thu, Aug 28, 2014 at 11:21 AM, Powell, Mark <mark.powell2_at_hp.com
> <mailto:mark.powell2_at_hp.com>> wrote:
>
> Yes, I would think that the built-in audit feature would be one
> option as would using table row triggers to write all changes to a
> history table. With a history table you can capture the complete
> row or just change data as works best for your needs along with
> the change date and username making the change. Then you can
> purge it using a different retention time criteria than used with
> your audit trail if desired.
>
> *From:*oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *Andrew Kerber
> *Sent:* Thursday, August 28, 2014 11:13 AM
> *To:* David Barbour
> *Cc:* oracle-l mailing list
> *Subject:* Re: SOX Reporting Requirement
>
> I believe you can get the information by just turning on extended
> auditing on the table, As I recall it will put all of that
> information on the aud$ table.
>
> On Thu, Aug 28, 2014 at 10:05 AM, David Barbour
> <david.barbour1_at_gmail.com <mailto:david.barbour1_at_gmail.com>> wrote:
>
> Morning,
>
> I was wondering how others might be handling the SOX
> reporting/auditing issue we've been assigned.
>
> The audit folks want to know when DML occurs on a particular table
> and the original and new value(s). I've implemented FGA on the
> table and can capture the change. Using the transaction ID, I can
> then go back to the flashback_transaction_query and get the
> original values. Of course, the only guarantee of being able to
> pull the undo sql containing the original values is that the query
> is performed before the undo retention expires. Pre-supposing I
> have a job that queries dba_fga_audit_trail and grabs the undo in
> time, what might happen next? I was thinking of storing the
> values in a table created specifically for this purpose. Then I'd
> probably create a view to generate the report.
>
> I'd appreciate any other ideas or refinements. This is a pretty
> busy database and I've got to be careful bumping undo retention
> too high. I'm undoubtedly missing something .............
>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
>

--
http://www.freelists.org/webpage/oracle-l