Re: Simple backup questions

I think what you're saying is that the only time you should overwrite your redo logs with backup copies is when you mess up a recovery attempt and the backup must be from the point in time just prior to the botched recovery attempt. I can't think of any other situation where overwriting redo logs has any benefit.

In thinking this over, the only other reason to restore redo logs from backup is in the case where they have been completely lost (e.g. drive has crashed and has been replaced and log file groups or other mirroring wasn't used). In this case, the contents of the redo log file restored from backup are irrelevant. If the current redo log was lost then at best the committed transactions in that log would be lost and at worst database recovery may not be possible. This just reinforces the need to use log file groups or other mirroring.

I still think it's important to back up redo log files. You wouldn't exclude the redo log files from a cold backup, right? I agree that extreme caution must be exercised when using backup files for database recovery. The entire recovery process must be completely thought through beforehand. I would also reiterate the need to backup everything prior to starting any kind of database recovery, just in case.

Oddly misnamed thread eh?

John P. Higgins wrote:

> 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 - 15:02:23 CDT