Re: Recovery of DB -> online redo log ?

Rob wrote:

> Hi there,
>
>
> I have a question about recovery.
> I have a 8.1.7.4 DB running on Windows NT (i know, it's the boss who
> like's it).
> I have 3 redolog group with 2 files each. Both files are local.
> These are archived to the local server.
> A job checks for new files and copies one to a remote server. From there
> on tape.
>
> So in case of a crash / data loss, etc i have copies of all my archived
> redolog files on tape.
> Now for the ONLINE redolog files at the moment of crash ??
> Lets say i logswitch every 30 minutes.
> In a bad example we loose 29 minutes of work. Because it isn't archived
> yet and lost in the crash.

Correct. If you lost every copy of your current online redo log, you would have to do an incomplete receovery until cancel, and cancel when that log was prompted for. And you would have lost all the work that was covered by that log.

But then that's why you have two files per redo log group (ie, multiplexing). With one of those files on, say, drive D: and the other on drive E: (and with D: and E: being two completely separate drives and not just partitions of the one hard disk), the chances of you losing both drives simultaneously (and hence *all* copies of your current online log) are rather small.

>
> Can i do something about this ?

Not really. The current redo log is Oracle's Achilles heel. But multiplexing across multiple drives is there for a reason, and should help prevent you get into the situation of total loss of the current redo log in the first place.

> In unix / linux i imagine you mount some drive and set a second archive
> dir in the pfile.

No, that's not correct. Because now you're suddenly talking about archived redo logs, not the current one.

> Under windows you cannot mount "MAP" a remote drive without being logged
> on. Could i create a third redolog file with filename
> '\\\remoteserver\sharename\redo_1_c.log' ??

If it were event possible (which it isn't), then it would be a performance nightmare. Anything that slows down LGWR writing to the current log is immediately noticed by your users (because a commit's not a commit until LGWR confirms it's written the redo the transaction's produced to the online log. And also because when you slow LGWR down, you slow the rate at which you empty the log buffer down, which means every new transaction faces a redo allocation retry wait event: it would be an absolute nightmare).

The only thing you can do to protect your online logs is to multiplex them... which you say you have done by having two files per group. I would suggest, perhaps, 3-way multiplexing, though it depends on your hardware capabilities (because that slows down LGWR too, though not as much as asking it to write over the wire). Provided those multiple files are all on multiple hard disks, and using separate controllers, that's the best protection you can offer your online logs.

You should also investigate hardware mirroring (RAID 1).

And you should do both: multiplexing (multiple files per group) protects you against LGWR and user errors; hardware mirroring protects you against hardware failure. Put both together, and you have the highest level of protection of your current redo logs that you can obtain.

>
> When i ask oracle about this they simply say "Install RAC".
> We cannot afford it (yet).

That has to be one of the silliest things I've heard in a long time, and Oracle support should know better. In the first place, RAC has issues of its own, and its a ridiculously complex and expensive solution to a very simple problem. No-one in their right minds would ever decide to adopt RAC just because they are worried about the safety of their online redo logs.

In the second place, RAC provides absolutely no higher protection for your online logs than you have at the moment. In RAC, although you have two (or more) machines, you have ONE database. Meaning one set of redo logs, on a shared disk (OK, each instance has its own thread of redo, but at the end of the day, you still have 4 or 6 redo log groups residing on the one hard disk). And the current redo log for each instance is just as vulnerable to catastrophic loss as it would be in a single instance environment.

So that particular Oracle support representative was talking complete and utter rot, and must have been chasing some sort of month-end sales bonus.

Forget all that stuff about RAC, until you need scalability and high-availability. If you are merely worried about safeguarding your online redo logs, then multiplexing (multiple files per group), and mirroring (RAID1) or striping+mirroring is all you need, and as good as it gets.

Regards
HJR
>
> If you have any other suggestions, please post !!
>
>
> Thanks in advance,
>
> Robert
Received on Sat Oct 04 2003 - 06:35:55 CDT