Re: Recover when Online Redo Log lost.

On 9 Aug 2004 18:55:30 -0700, wy <wy_at_fudan.edu> wrote:

> Thanks for the posting!
> But I still have some questions to confirm.
> 1) Can all these data insert with nologging(between last hot backup
> and crach) can recover?

Come on... think! What is the mechanism used to recover data? Answer: redo  from the redo logs. What doesn't a 'nologging' operation generate? Answer: redo.

Now, what is the logical conclusion to be drawn from those two answers? And can you now see why the recommendation is to perform a fresh back up anytime you perform a nologging operation?

> 2) If the db was closed with shutdown abort,all online redo logs
> were lost,and no valid hot backup exists,can I recover the
> database?Must I reinstall the database?

Again, think please. What guarantees recovery? A backup taken before a catastrophe, and the redo generated after that backup. If you start from the premise that you have no valid backup, then you will not be able to recover, regardless of whether it's your online redo logs that have been lost, the control files, SYSTEM or whatever. No backups=no recovery.

The temperature of your backup is irrelevant to that equation. Hot, cold... makes no difference.

And the shutdown abort has got nothing to do with it, either. The problem in this scenario is that you have nothing to restore, therefore you have nothing to recover.

If you are, however, asking: is the current redo log needed to recover a hot backup, then the answer is: it might be. It is why people who take hot backups usually end their backup activities with an 'alter system switch logfile'... it means the current log gets archived, and hence the loss of the current logs would not render that last backup useless. Is it actually necessary to do this? No, because if your last hot backup is rendered useless by the loss of the current redo log, there is presumably a prior hot backup to fall back on.

But again, none of that actually has anything to do with the question you've actually asked, because that's all about (as far as I can tell): what do I do if I have no valid backups? Panic, is the short answer.

> 3) If the db was closed cleanly with shutdown immediate,all online
> redo logs were lost,can I recover the database?(I think redo logs are
> not necessary to recover a clean shutdown database,is it right?But I
> must reopen the db with resetlogs)

Again, there's no logical connection between any of these premises. You have a backup? You have redo (in archives)? Then you can recover. Can you recover completely? You can *never* recover completely if you've lost your current redo log.

If you shutdown immediate, and then immediately delete the online logs, "recovery" would be required (to reconstuct some working redo logs). But it's a fake recovery, as you imply: recover database until cancel, immediately cancel, alter database open resetlogs. I suppose this is an example of an incomplete recovery that actually doesn't lose any data, and some people might therefore call it a complete recovery -but they'd be wrong. You've still just performed an incomplete recovery, despite not having lost any committed data. There should be no need to do a prior restore of anything, because the data files and control files are consistent with each other.

I am however going to suggest that you simply stop worrying about any distinction between shutdown abort and shutdown immediate. Unless you are in the habit of deleting all your online redo logs, and unless you have neglected to properly multiplex your online logs, it's not an issue.

Regards
HJR Received on Mon Aug 09 2004 - 21:21:30 CDT