Re: Real life implementation of 7 year data retention requirement

From: Paresh Yadav <>
Date: Thu, 13 Feb 2014 22:55:50 -0500
Message-ID: <>

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)?
Received on Fri Feb 14 2014 - 04:55:50 CET

Original text of this message