Re: Real life implementation of 7 year data retention requirement
- From: Kevin Jernigan <kevin.jernigan@xxxxxxxxxx>
- To: "mark.powell2@xxxxxx" <mark.powell2@xxxxxx>
- Date: Fri, 14 Feb 2014 10:18:44 -0800
Those restrictions were removed in 11gR2; with the exception of a few more
extreme operations such as "split table", FDA is completely transparent to
almost all DDL, including add column, drop column, etc.
We also added the ability to temporarily disassociate a table from FDA, then
make structural changes (to both the table and its associated FDA history
tables), then reassociate the table with FDA.
Bottom line: in 11gR2, FDA is almost completely transparent to applications and
end users, and very easy to use.
-KJ
Sent from my iPhone
> On Feb 14, 2014, at 10:03 AM, "Powell, Mark" <mark.powell2@xxxxxx> wrote:
>
> The problem I see with creating and using archive tablespaces is that the
> tables that you set up archiving for can no longer be structurally changed.
> We add new columns to support new application functionality on a regular
> basis. To change the table requires dropping the archive.
>
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Paresh Yadav
> Sent: Thursday, February 13, 2014 10:56 PM
> To: Kevin Jernigan
> Cc: Ruel, Chris; freek.dhooge@xxxxxxxxx; frits.hoogland@xxxxxxxxx;
> WLJohnson@xxxxxx; rjoralist3@xxxxxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: Re: Real life implementation of 7 year data retention requirement
>
> Wow fantastic information as usual, thank you all! If it is Oracle database
> related question and this group can't provide answers/information/pointers I
> believe none can.
>
> Thank you Peter, Frits, Dennis, Freek, Fergal, Rich, Kevin and Ruel.
>
> Need to look into FDA as I have no idea about it. Sounds like a solution
> developed to address shortcomings/complexity of some options mentioned below
> e.g. database audit trail, maintaining row history etc.
>
> Spoiler alert, I think when all is said and done, this project will be
> scrapped :(.
>
> Hi Peter, Hurdle to resurrect the older environment (OS, Oracle database
> software version etc. is an excellent point. This is also a client in the
> banking sector.
>
> As pointed out by others, the most recent backup doesn't have all the
> "history" of DML operations unless DML trail (in form of row history) is
> maintained inside the database.
>
> May be this is a question for the lawyers but anyone wants to share their non
> binding opinion :); what does 7 year retention requirement mean? Is
> history/trail of all DMLs maintained inside the database or an audit trail is
> enough or the database has to be resurrected to a point in time? The latter
> option seems like a crazy and practically impossible idea without breaking
> the bank (and overcoming all the short comings mentioned by others so that we
> don`t run into issues like compatibility of tape with tape drive, database
> version etc. etc.).
>
> Assuming keeping trail of DML is enough to meet the 7 year records retention
> requirements:
> For applications like Peoplesoft which keeps (at least as of ver 8.1 when I
> last worked with it) track of all DMLs done through the application, what do
> you do with DMLs done outside of the application? Implement Database
> auditing? If yes, it will also audit DMLs done through the application, can
> it be excluded?
> Why do many places have backup tape retention set to 7 years (usually for
> monthly full backups) even when the database contains 7 years history? May be
> an oversight/overkill?
> If the application doesn't maintain trail of DML, is it feasible to implement
> database auditing and keep it for 7 years? Are there tools available to use
> this audit trail and provide snapshot of data as of a given time without
> having to revert to writing many SQLs which is time consuming and prone to
> human errors. What about maintaining integrity of the audit trail? Use
> Database Vault to assure the integrity of audit trail? (is FDA = working form
> of this solution)?
Other related posts:
