Re: long term archivelog retention
Date: Mon, 22 Apr 2013 20:41:13 -0300
Message-ID: <CAJ2dSGR=eATg3jnEEfXSys3B-jL_ProuVHD347fdTANBqOnCLQ_at_mail.gmail.com>
well that came out all wrong.
a one year backup retention is ridiculous. You are either in a data warehouse situation where you need to see how the data evolved from a point in time until now, which, btw, is usually the case for most FDA, PCI and PII requirements, or you are in a situation where if you lose the productive database you may want to recover to a status a year ago... which is pretty much useless for most business scenarios. IMO you are using the backups for the wrong purpose.
However, since you asked the question I think I should give you an answer to the best of my ability:
I've faced such ridiculous requirements as well, where they wanted to keep backups for several years, but not the archivelogs (which oracle won't delete if they are still needed unless you specify the range).
Our solution was to manage the backup retention at the tape level. So basically we backed up the database to tape every day, and archivelogs every hour. HOWEVER we would use different tags (actually different tdpo.conf) for each type of backup, so the B&R team had the "archivelog" tapes on a 15 day retention (still way too much IMO) and the level 0 backup tapes on a 1 year retention. Or course controlfiles were always backed up to the level 0 tapes. RMAN was set to a recovery window of 14 days.
The catch with that is that you can't rely on autobackup of the spfile and controlfile, so your scripts need to take that into account. After that it was just a matter of restoring the apropriate CTL. We only did two DRE where we actually recovered the database to a point six months before, and both times nobody could confirm whether the data was ok because.. well it was an OLTP DB... data was there until the target date but that's it, nobody knew what the data was supposed to look like. The biggest issue is that you need to trust someone else with the retention of your backups... with no way of checking.
This was back in the 10g days, so no nifty keep until time possible.
I'm sorry if I came out a bit confrontational... I've been dealing with requests like that for several years...
Cheers
Alan.-
On Mon, Apr 22, 2013 at 8:28 PM, Guillermo Alan Bort <cicciuxdba_at_gmail.com>wrote:
> a 1 year backup retention is riddiculous.
>
> Alan.-
>
>
> On Mon, Apr 22, 2013 at 7:21 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
>
>> Hi
>> I have a database that has a requirement of retaining archivelogs for 1
>> year. The database must be able to recover to ay point in time in 1 year
>> period.
>>
>> So I have thought of using keep until time option for archivelogs (new in
>> 11g). The strategy will be daily database backup with be retained 31 days
>> using normal recovery windows retention policy, monthly 12 months and
>> anual
>> backups 5 years using keep until time. Archivelogs will also use keep
>> until
>> time sysdate + 365.
>>
>> Anyone have used keep until time for archivelogs and know any gotchas? So
>> far the gotcha I have found is that we cannot use DELETE ARCHIVELOG ALL
>> BACKED UP N TIMES TO DISK.
>>
>> Thanks
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 23 2013 - 01:41:13 CEST