Real life implementation of 7 year data retention requirement

From: Paresh Yadav <yparesh_at_gmail.com>
Date: Thu, 13 Feb 2014 11:55:02 -0500
Message-ID: <CAPXEL0J=0kETaNmg-OsRf7oGMMg7zBMxGDOrceAy-F85jAHUhQ_at_mail.gmail.com>



Hi Group,

We are asked to implement a database backup policy so that the state of database can be resurrected to any given point in time up to past 7 years. I have implemented the classic approaches listed below at multiple places and multiple times but god only knows what actually happens in real life as never had to recover to a point older than a month at the most. AFAIK this is the approach used "everywhere".

Can you share your actual experience from a real life situation having performed or asked to perform recover to a very edge of the retention period (say 6 or 7 years from "now") (e.g. challenges in locating the tapes (physical or virtual), rman catalog not having record of backup pieces for the time period etc.). Does magnetic tape remain good for 7 years in a climate controlled environment or you do copy them after 3 years or so to a new tape? If yes, is this automated as manual process will be too much cumbersome and prone to errors.

Are there any better/new approach available other then the classic "retain the backups for 7 years" approach?

Is it possible to practically implement a better strategy where retain only backups so as to be able to recover to state as of say "Sundays" and use backups of flashback logs for flash back queries for any intermediate days between "Sundays"? If you have done so, can you please share the information. If possible, is this approach worth the trouble?

Of the classic approaches listed below can the experts add their comments. init.ora, listener.ora, control files, OCR, Voting disks will be backed-up along with any of the options below, what else should be backed up?

 *Sr. #*

*Backup method*

*Storage Overhead*

*Retain backups*

*Time to recover*

*Risks of not able to resurrect state of database to a specific point in
time*

*Comments?*

1

Daily full datafile plus archivelog backup

Max

7 year plus 1 day

Min

At the max 1 day from target recovery point as the information required for recovery is contained in one backup

2

Weekly Level 0 datafile backup plus archivelog backups on say Sunday and daily Level 1 incremental datafile backup plus archive log backup on other days

Min (relative to #1 above)

7 year plus 7 days

Max (relative to #1 above)

At the max 7 days from target recovery point (say on Saturday) due to loss of Level 1 backup from Monday or necessary level 0 backup

3

Weekly Level 0 datafile backup plus archivelog backups on say Sunday and daily Level 1 cumulative datafile backup plus archive log backup on other days

Medium (relative to #1 above)

7 year plus 7 days

Medium (relative to #1 above)

At the max 7 days from target recovery point (say on Saturday) due to loss of necessary level 0 backup

4

Other backup cycles say biweekly or monthly similar to #2 or # 3 above

Will be less relative to #2 and # 3 above

7 year + cycle period

Will be more relative to #2 and # 3 above

At the max cycle period from target recovery point

Thanks
Paresh
416-688-1003

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 13 2014 - 17:55:02 CET

Original text of this message