RE: Real life implementation of 7 year data retention requirement
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Feb 2014 18:03:09 +0000
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:
