Re: database could not be started

From: quarkman <quarkman_at_myrealbox.com>
Date: Sun, 10 Aug 2003 16:28:05 +1000
Message-ID: <oprtoew3bhzkogxn@haydn>

Sorry about the formatting of this one... God knows what is going on!

"quarkman" <quarkman_at_myrealbox.com> wrote in message news:oprtnqk2wfzkogxn_at_haydn...
> On Sat, 9 Aug 2003 12:39:31 -0500, Burton Peltier
> <burttemp1REMOVE_THIS_at_bellsouth.net> wrote:
>
> > "Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message
> > news:3f3510b5$0$49108$e4fe514c_at_news.xs4all.nl...
>
> >
> > This is the main reason where you would need the REDO logs. I thought
> > that
> > was what I said? After re-reading my post, it IS what I said.
> >
> > Just to be clear... I did not mean to imply and I know you do NOT
recover
> > REDO logs when recovering from a hot backup. The entire context of the
> > OP
> > was a problem with a cold backup. All of the comments in my post were
> > about
> > cold backups ONLY.
> >
> > Hot backups are different, of course.
>
>
> The temperature of your backups makes no difference. You should never
> backup online redo logs, period. Archivelog or noarchivelog, hot or cold.
>
> The subtler point is that's it's fine to do cold backups, even in
> archivelog mode. Indeed, cold backups with archivelog mode is quite
> possibily the nicest combination of options available to the dba. But
> herein lies the source of possible confusion: I'm in archivelog mode, I
> take cold backups... is it therefore OK for me to backup my online redo
> logs? No it isn't, because I risk data loss at the time of restore.
I suppose I am missing something here still... please explain.... If there is some strange occurence in your cold backup "clean shutdown" and
you don't have REDO logs, don't you take a risk with open resetlogs?

Yes, you do. Hence, shutdown abort, startup restrict, shutdown immediate. But one would hope, of course, for an initial clean shutdown to obviate the numerous bounces. You raise a valuable cautionary, however, so thank you for reminding me.

And, in the above scenario, what does the archivelog or noarchivelog have to
do with it?

The difference is that in archivelog mode, I can expect to perform a complete recovery... but not if in the process of restoring things I also happen to wipe my perfectly usable online redo logs by copying stale copies on top of them. In noarchivelog, I generally expect merely to restore from my last complete backup, and that's it. No roll forward. Intentional data loss anyway: the loss of the online logs doesn't compound the issue... I'm already stuffed, so restoring old online logs ontop of the present ones would stuff me no more. In archivelog mode, I need the contents of at least one of those online logs to perform a complete recovery.

My point, however, is that under neither set of circumstances do you actually need to restore your online logs. Doing so in noarchivelog mode doesn't make things a heap worse, but is unnecessary. Doing so in archivelog mode is a disaster. But in either case, doing so is pointless. Therefore, don't back the things up in the first place, and you won't have to worry come the time to restore.

Incidentally: when does the DBA's stress level go through the roof, such that "obvious" things such as "I would never restore the online logs when I shouldn't" tend to go out the window?? Er, when the production database is down, and everyone is screaming at you to get it back up. I confess to having done a 'copy *.*' in the past when I shouldn't have done, and paid the price (management Spanish Inquisition). I got away with it because I started mentioning technical things that they weren't quite sure of, and I kept my job (and learnt a lesson). But it isn't nice when it happens, so why encourage practices which tends, however rarely, to make it happen??

Note: We usually run in archivelog mode for prod and test and do cold, hot, and export backups . I think I am covered :) hopefully I am (don't want ot
jinx myself).

I find it helps to cross ones legs as well as assorted digits.

;-)

>

[snip]
>
>
> I don't know why suddenly 'resetlogs' is considered so evil! You would
have
> to type 'recover database until cancel'...'cancel'...'alter database open
> resetlogs'. I make that a massive 9 words of typing!
>
> Why backup something which you can never safely restore in archivelog
mode?
>
> And why make your noarchivelog cold backup backup something (possibly
> taking many extra minutes to do so, depending on their size) which you
> would then have to expend more minutes restoring... when the alternative
> re-creates them in a blink of an eye? I dispute the reasoning that a full
> restore would be quicker than an open resetlogs, particularly if you're
> restoring from tape.

None of our REDO logs are more than 100Meg ... x 4 x 2 = 800Meg. We backup to disk and typically get about a 20 Meg per second transfer rate. So, it would take me something like 40 seconds to restore all the logs and their multi-plex copy.

That's fine for you. Indeed, it's fine for anyone who knows what they're doing (I try not to be didatically proscriptive if I can avoid it). But this is a forum where all sorts of people congregate, including a lot of people who don't actually have a lot of time to learn all the subtleties. So what if you have 1GB logs, 16 of them, multiplexed (been there, done that)? And your backup is to tape? We're talking about extra minutes at best. Possibly many of them.

Who cares? Management does, so I do too. Every extra minute of unplanned downtime may cost money. Possibly a lot of it.

Unnecessary restores and recovers are (I would say) one of the leading contributing factors to excessive additional unplanned downtime.

And as I said, to assist that multitude of people who post here saying "I'm not a proper DBA, but they made the proper DBA redundant, and I'm expected to pick up the pieces", I like to come up, wherever I can, with a simple set of instructions that work in all circumstances. Not backing up the online logs, and doing clean shutdowns prior to backing anything up, is one such 'simple rule' that works under all circumstances.

I've spent my professional career fighting this idea that somehow there is a high priesthood of the big white box: these things should be obvious and easy. To those in the know, it therefore seems irritating to see things said which they themselves don't do *because they know the risks*. I guess I just have a different audience in mind.

~QM

Received on Sun Aug 10 2003 - 01:28:05 CDT