Real life implementation of 7 year data retention requirement
- From: Paresh Yadav <yparesh@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 13 Feb 2014 11:55:02 -0500
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
Other related posts:
