Re: database could not be started

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 14 Aug 2003 06:40:27 +1000
Message-Id: <pan.2003.08.13.20.40.26.857440@yahoo.com.au>

> On Mon, 11 Aug 2003 20:47:37 -0500, Burton Peltier
> <burttemp1REMOVE_THIS_at_bellsouth.net> wrote:

[snip]

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.

Fair enough -but ¨would be hard to imagine¨ is not bullet-proof. Not backing them up in the first place means ¨it would be impossible to¨. That´s all.

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.

Having seen it done for real, this is one particular option I´d like *not* to have available. But each to his own. ---------

> 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.

That one I can´t fathom. Shutdown abort, startup restrict, shutdown immediate should do it every time.

> 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 .

See, this is where this forum is sometimes not the best place to discuss these things. *Your* hardware might be up to the task, but not everyone else´s. What works for you, because you know the issues, isn´t necessarily what should be publicised as ´best practice´, because there are a lot of newbies out there who won´t know the issues. And whereaas 2GB to you is trivial, how about the guy I had a while back in class who has 60 online logs, each of 2GB? And then multiplexed. (He swore this to be true, so I can only take him at his word). -------------

> 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".

I think we´re all agreed that clean shutdowns are or ought to be a prerequisite of any robustly safe noarchivelog backup (again, temperature has nothing to do with it).

> 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?

No I mean the current log, singuular. If you do a shutdown abort, it will be transactions in the current log that need to be replayed. A non-active log is never required to make a database consistent.

> database. Therefore, don't do dirty shutdowns.
>

As I have said several times, clean shutdowns are not trivial.

Abort-restrict-immediate should be fairly trivial. And then there´s no possibilities of anything nasty happening at recovery time, which is usually much more of a stressful situation where mistakes happen.

Anyway: you keep on doing what has worked for you, because you know what you´re doing. I´d just like to think we would be a bit careful about what advice we leave out there for newbies by way of general advice: it ought to be safe as houses, and reliable.

Regards
HJR Received on Wed Aug 13 2003 - 15:40:27 CDT