Re: Recover when Online Redo Log lost.

On 8 Aug 2004 20:49:20 -0700, wy <wy_at_fudan.edu> wrote:

> Hi,all.
>
> How do I recover the db while all the online redo log files were
> lost.

You perform an incomplete recovery, probably using the 'until cancel' form of the recovery command.

Incomplete recoveries will result in data loss. So backup everything you have right now, cold and complete. Tuck that backup out of the way somewhere for emergency use (it's not, in other words, part of your usual backup cycle).

They are performed by restoring ALL data files, but not the control files or redo logs. You then say 'recover automatic database until cancel' and the command applies a bunch of archived redo, but then errors out when it gets to what would be the online logs. You then simply fire up the recovery process once more, this time manually:

recover database until cancel;
cancel

Immediately the system prompts you to supply the online log, you type the word cancel. That halts recovery and you then do:

alter database open resetlogs;

...which gets your database open and happens to re-create any missing online logs at the same time.

> The database is running in archive mode and all the archived redo
> log files were saved well.
> But since last hot backup,I have done some table inserting with
> nologging.
> Now the database can not be opened.
> Who can tell me how to recover the db to the recent state.
> I remember the db was shutdown immediate.
> (Do it make any difference to online redo log file between shutdown
> immediate and abort?)

None whatsoever. There is nothing dangerous at all about a shutdown abort, and it won't have damaged any logs or other files. The only problem with it is one that's relevant to your current situation: a shutdown abort counts as a 'dirty' shutdown. Therefore it needs a subsequent instance recovery. And all instance recoveries take the redo they need from the current redo log. So if you lose your current redo log, then what should have been a no-data-loss shutdown turns into a cause for an incomplete recovery, and hence committed data loss. If one does not lose the current redo log, however, then a shutdown abort is no more dangerous or weird than a shutdown immediate and has precisely the same effect.

Not mentioned anywhere in your post, though it probably should have been: were your online logs multiplexed? Multiplexing means having multiple copies on separate drives, so that there are two or three sources for required online redo, not just one. It is generally considered rather unlikely that all copies of multiplexed redo logs would simultaneously die together! If you haven't multiplexed, please consider it. Urgently.

Regards
HJR Received on Mon Aug 09 2004 - 01:26:20 CDT