--
"quarkman" <quarkman_at_myrealbox.com> wrote in message
news:oprtr4ppeozkogxn_at_haydn...
> On Mon, 11 Aug 2003 20:47:37 -0500, Burton Peltier
> <burttemp1REMOVE_THIS_at_bellsouth.net> wrote:
>
First, let me say I went "researching" a little and actually did find Oracle
documentation supporting your recommendation - not mine :(
That clean shutdown still bothers me, but I must admit our cold/offline
backups are out of sync with what Oracle recommends. Then again, the backup
scripts we use were created and kept up to date for the last 12 years. I
have used the hot/online backups so many times to recover , I can't remember
how many times (maybe 20 times). See below, but NO - these hot/online
backups do NOT include REDO logs. I have used the cold backups maybe once
to recover to a clone of the database - on an entirely different server with
the same OS and Oracle versions. Yes, I know this may not be the best way to
do a clone, but it worked great.
Once again, I never have said that REDO logs should be backed up for
hot/online backups.
Anyway, Oracle actually said 1 other thing I don't think has been mentioned
in this thread yet.
The doc I read said if a cold backup is recovered using the REDO logs backed
up at the same time as the datafiles for the cold backup AND the database is
in archivelog mode, then there is a risk of creating "archived REDO logs"
with the same name because the sequence was not reset (which is what the
open resetlogs will also do).
But, I still don't see (maybe because I haven't been burned yet) why backing
up the REDO logs during a cold backup is that big of a deal. We never back
them up at any other time and these cold backups are stored in an entirely
different location than any other backup - would be hard to imagine
recovering these backed up REDO logs for anything other than a recover from
the cold backup.
At least I have the option of recovering them if I know the database did not
have a clean shutdown. I could then do a clean shutdown and open reset logs
to reset the sequence number.
And, I like options at recover time - the more the better. Complicated for a
junior DBA ? Maybe so.
>
> First off, you're still on about backup temperature, and that's got
nothing
> to do with it. You can do cold backups in archivelog mode.
>
I know. We run in archivelog mode for 99% of our databases. We do cold
backups, hot backups, archive log only backups, and full export backups.
Of course, the hot is the primary recover source for things like lost disk
drives. The cold is a backup to the hot backup.
> Second, I was not advocating blindly doing open resetlogs, and thus
blowing
> away the contents of the online logs.
>
I thought I said under a specific conditions - never meant to imply
"blindly".
> I'll try and make this simple.
>
I'll try not to be sarcastic :)
> Noarchivelog means any recovery can only ever be to the last clean, whole
> database backup (barring one or two quirks). Therefore, you will lose all
> data entered since that last backup. Therefore, surviving online redo logs
> are useless anyway. Therefore, you don't need them. An open resetlogs will
> blow them away and re-create them. Have you lost anything you wouldn't
> otherwise have lost? No. Does this mean I would do resetlogs with gay
> abandon whenever the mood toook me? No, of course not, because in
> archivelog mode, a resetlogs is a very serious business indeed. But in
> noarchivelog mode, it's trivial.
>
Trivial? I agree, as long as you had a "clean shutdown" for the backup.
I have had several times where a "clean shutdown" was not possible and this
is not trivial.
> Alternative scenario: noarchivelog, and your complete backup includes redo
> logs. Recovery is simply to restore everything. You lose every transaction
Simple to recover and simple to perform the backup - don't have to worry
about clean shutdowns :)
> entered since the time of the last backup (ie, exactly the same as in the
> previous scenario). You avoid a resetlogs, but as I say that's really only
> an issue when you're in archivelog mode anyway. Costs? Depends on the size
> of your redo logs. But restoring 3 or 4 500Mb files when you didn't
> actually gain anything by doing so, and wouldn't have lost anything by not
> doing so, seems to me to be a waste of time.
>
At 22 Meg per second transfer rate on our current hardware, I don't see
where backing up and restoring 2 Gigs is an issue .
> Next scenario: you're in archivelog mode. Recovery consists of restoring
> only the faulty data file, and then rolling all the way forward, using as
> you do so, the transactions in the current online redo log. Did you need
> the online logs to be included in the backup? No, because you only need to
> restore the faulty data files.
>
Agree.
> Final scenario: archivelog mode, but you take copies of your online redo
> logs. A couple of data files go wrong... your junior DBA tries to take a
> short cut, and restores the entire last night's backup. You've just over-
> written the fresh online logs with two very stale ones. You therefore
can't
> roll completely forward, and you've lost data as a result (at least all
the
> transactions that were in the current log). Do you regret backing the logs
> up now? Yes you do, because you've just lost data in a scenario that
should
> have been completely recoverable.
>
This is exactly why we never backup the REDO logs for this type
(online/hot/archivelog) backup. We only backup the REDO logs during an
offline/cold backup and they are stored in an entirely different location
than any other backups.
> Point is: it's dangerous to back up the logs in archivelog mode. Mistakes
> happen, and they're never needed anyway. It's pointless backing them up in
Never needed ? Yes, as long as your cold/offline/noarchivelog backups do a
"clean shutdown".
> noarchivelog mode, because you don't need them in order to be able to
> recover as completely as its ever possible to recover in noarchivelog mode
> anyway.
>
> But, as has been pointed out, a dirty shutdown prior to backup would mean
> the current redo log would be needed to achieve a consistent (and
openable)
Current? You mean the REDO logs at the time of the backup, right?
> database. Therefore, don't do dirty shutdowns.
>
As I have said several times, clean shutdowns are not trivial.
> I hope that clarifies what I was trying to say, anyway!
>
> Regards
> HJR
>
>
>
>
Received on Tue Aug 12 2003 - 22:14:56 CDT
