Re: Simple backup questions

I have been researching this question. This my current understanding.

When should you restore the redo log files from a backup?

• Media recovery (1,2,,,,, all datafiles) to current status: o If you restore redo log files (overwrite the current redo log files), you lose the transactions: + from the active redo log group; + from any other online redo log group that was not archived before it was overwritten. + This means you cannot recover to current if you overwrite the redo log files! o If you restore redo log files (somewhere else), roll forward to current will succeed because the active redo log(s) is(are) still available. Note that this recovery will NOT use the restored redo log files.
• Media recovery (all datafiles) to a prior point-in-time: o to a point-in-time prior to any un-archived redo log group: + If you restore redo log files (overwrite the current redo log files), the roll forward to the prior point-in-time will succeed because the active redo log(s) was(were) not needed. Note that this recovery will NOT use the restored redo log files. + If you restore redo log files (somewhere else), the roll forward to to the prior point-in-time will succeed because the active redo log(s) was(were) not needed. Note that this recovery will NOT use the restored redo log files. o to a point-in-time within the scope of the active redo log group (or any other online redo log group that had not been archived: + If you restore redo log files (overwrite the current redo log files), you lose the transactions: + from the active redo log group; + from any other online redo log group that was not archived before it was overwritten. + This means you cannot recover to a prior point-in-time within the scope of any un-archived redo group if you overwrite the redo log files! + If you restore redo log files (somewhere else), the roll forward to the prior point-in-time will succeed because the active redo log(s) is(are) still available. Note that this recovery will NOT use the restored redo log files.
• Repeat (after OPEN RESETLOGS) media recovery (all datafiles) to a prior point-in-time (Note: this happens when you guess wrong as to the exact point-in-time): o to a point-in-time originally prior to any un-archived redo log group: + If you restore redo log files (overwrite the current redo log files), the roll forward to the prior point-in-time will succeed because the reset redo logs were not needed. Note that this recovery will NOT use the restored redo log files. + If you restore redo log files (somewhere else), the roll forward to to the prior point-in-time will succeed because the reset redo logs were not needed. Note that this recovery will NOT use the restored redo log files. o to a point-in-time originally within the scope of the active redo log group (or any other online redo log group that had not been archived: + If you restore redo log files from the original backup (overwrite the current redo log files), you lose the transactions: + from the active redo log group; + from any other online redo log group that was not archived before it was overwritten. + This means you cannot recover to a prior point-in-time within the scope of any un-archived redo group if you overwrite the redo log files! + If you restore redo log files from the original backup (somewhere else), the roll forward to the prior point-in-time will fail because the active redo logs were reset. Note that this recovery will NOT use the restored redo log files. + If you restore the redo log files from a backup taken before the OPEN RESETLOGS (overwrite the current redo log files), the roll forward to to the prior point-in-time will succeed because the reset redo logs were replaced with the original redo logs.

So the answer to my original question is:

     Only restore the redo logs when you must repeat a point-in-time recovery
     within the scope of the original un-archived redo logs. This restore must be
     from a backup of the redo log files just prior to the previous OPEN RESETLOGS.

Since any other restore of the redo log files may actually cause loss of data, it is dangerous to routinely backup the redo log files.

Allan Nelson wrote:

> jhy wrote:
>
> > Yes, you need to back up your redo logs.
> >
> > When attempting to recover your database, you should only restore any deleted or
> > damaged files from your last backup. Leave any intact files in place. Do not
> > replace your control files. When you attempt to open the database Oracle will
> > realize that some of the files are out of synch and need recovery, and will prompt
> > you for the archive logs.
> > If you restore all the files from your cold backup they will all be in synch and
> > there will be no way for Oracle to know that recovery is needed.
> >
> > Before doing anything, it's always a good idea to backup everything first. If you
> > make a mistake, you can always go back to where you started.
> >
> > Good luck!
> >
> > poohland_at_hotmail.com wrote:
> >
> > > Just two very simple backup questions,
> > >
> > > 1. I've read several books talking about whether we should backup the online
> > > redo log files. Oracle Press book say yes but I remember some one post in this
> > > newsgroup earlier saying that we don't need to backup the online redo log
> > > files. Can some one verify that for me please?
> > >
> > > 2. When I use the cold backup to recover the database to the point of failure
> > > (database in archivelog mode), I think that I only have to restore all the
> > > datafiles to its original location but definitely not the controlfiles? I
> > > think if you restore all the datafiles to its original location and leave the
> > > controlfiles as current. when we try to open the database, Oracle should
> > > discover that the header of the datafile and the controlfile is not in sync
> > > and should start the recovery process and ask for archive logs, right? (I
> > > hope I am right, but I am not sure any more!) What if the backup utility that
> > > we use force me to restore the controlfiles and replace those current
> > > controlfiles with those old controlfiles? If I still have all the archive
> > > logs from that cold backup onwards, can I force Oracle to start the recovery
> > > process even though the controlfiles(restored one) and the datafiles
> > > (restored one) are in sync already?
> > >
> > > It's really urgent, please help me with that!
> > >
> > > Winnie Liu
> > >
> > > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > > http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
> The problem with backing up the on-line redo log is that as their name suggests
> they are open by the instance at the time you do the backup. I would suggest that
> you do an ALTER SYSTEM SWITCH LOG FILE, let the archiver get the old logs backed up
> and then just pick up the archived redo logs.
Received on Sat Aug 01 1998 - 13:20:51 CDT