Re: Redo Log Question

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Mon, 23 Dec 2002 22:55:22 +1100
Message-ID: <sHCN9.8808$jM5.25161@newsfeeds.bigpond.com>

"Burt" <burtpelt_at_bellsouth.net> wrote in message news:98b09e2b.0212230057.6926d977_at_posting.google.com... > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<zArN9.8420$jM5.23904_at_newsfeeds.bigpond.com>...
> > "Burt" <burtpelt_at_bellsouth.net> wrote in message
> > news:98b09e2b.0212221105.68ab19e8_at_posting.google.com...
> > > I just thought I would mention a few other points ...
> > >
> > > 1) Oracle's 1st recommendation is to use OS mirroring for REDO logs. I
> > could
> > > see debating this one and using the 2nd choice.
> >
> > That's simply not true. Oracle, like others, clearly understands that OS
> > mirroring protects you only from hardware failure. But it does not
protect
> > you from user stuff-ups or LGWR wobblies. If LGWR introduces some
corruption
> > into a redo log as it writes (which it has been known to do from time to
> > time) then OS mirroring will simply and faithfully replicate that
corruption
> > onto the hardware mirror. Result: no good logs, but multiple copies of a
> > stuffed one.
> >

>
> First of all, your attitude sucks big time. You need to lighten up!

Funny. The only time I get comments about by attitude and needing to change it is when somebody posts complete and utter nonsense, and I point it out to them.

Plus ça change.

>
> I have been doing Oracle server DBA support for about 10 years.
>

Badly, by the sounds of it.

> The comment about "Oracle recommends" comes out of a white paper from
> Oracle or from the docs . I don't have the time to check it. But, it
> is there. Search Metalink and docs if you have the time.

Please see below.

> So, if the OS can mirror corruption, why wouldn't Oracle mirror it
> too? I have SEEN BOTH (how about you?) the OS AND Oracle mirror
> "software" corruption.

Because with OS mirroring, you have LGWR only writing once, and then that being copied by the OS to the mirror. One write. One stuff up. One corruption. Mirrored. With multiplexing, LGWR writes twice (or three times, if you do 3-way multiplexing). With two (or more) writes, it is highly unlikely that LGWR would introduce the same corruption at the same point in the redo stream. Therefore, multiplexing protects you against software corruption.

>
> > If Junior DBA is practising his Unix skills, and issues an 'rm *'
command,
> > then the OS faithfully deletes the mirrored copy of the redo log as well
as
> > the original. Result: a totally missing redo log group.
> >

>
> I don't let junior DBAs touch my systems :)
>
> BTW, did you notice my comment about "debating this one". Hmmm ,
> probably not.
>

Yes, and you missed the point: there's nothing to debate. Oracle does not recommend OS mirroring as its first line of defence. Period.

>
> > Oracle multiplexing, by contrast, protects you from both these
scenarios,

>
>
> Uhh ?  Why wouldn't Oracle put the same damn corruption in both
> copies?

Because, with multiplexing, LGWR has to write twice. With O/S mirroring, it writes once and then the O/S is responsible for making the copy.

The chances of LGWR introducing corruption at exactly the same spot with two different writes is pretty small.

>It did it to me once in version 7.3.2.3 and I spent the next
> 27 hours recovering. Corruption got in the datafile and REDO logs and
> was also archived. We (myself and an Oracle analyst at Oracle support)
> didn't know for sure when the corruption occurred. Got lucky doing a
> point-in-time recovery with a guess.
>

Fascinating stuff. But not relevant, is it?

>
> > and can also protect you from hardware (disk) failure, since each member
is
> > supposed to be housed on a different disk.
> >
> > >
> > > 2) The 2nd choice is to use Oracle's "mirroring" by specifying a 2nd
> > member
> > > in each REDO group.
> > >
> > > 3) If you use OS mirroring, you shouldn't need the Oracle mirroring
and
> > the
> > > opposite is true too.
> > >
> >
> > Total and complete bollocks, I'm sad to say. They are complementary, but
> > your first choice should be Oracle multiplexing. By all means then
hardware
> > mirror, too. But multiplexing should be regarded as compulsory.
> >

>
> I appreciate your paranoid attitude. You have to be a little paranoid
> to be a server DBA :)  I would admit in a "perfect" world, I would
> want 4 disks for REDO logs for EVERY database. Really, though, not all
> installations have the option of using 4 disks for REDO logs.
> Sometimes, you are limited .
>

Then compromise by combining data files on what few disks you have and suffering a performance hit. But don't compromise the safety of your data by not multiplexing.

> We also have a lot of SysAdmin types who think all Oracle files
> should/could go on 1 RAID disk ... nuts. Anyway, purchases are made
> without talking to DBAs sometimes too. Stuff happens.
>
>

> > Thus speaks an Oracle instructor, incidentally, so where you get the
idea

>
> I once had an Oracle instructor tell me in an Oracle7 application
> tuning class that the CBO would ALWAYS make a better performance
> choice over the RBO.
>
> YEAH, sure.
>

I smell a strawman. What some instructor told you in years gone by about optimization of SQL has nothing to do with the issue at hand.

> This goes to show you all instructors don't always know what the heck
> they are talking about.
>

This goes to show you that *an* instructor bought the marketing spiel of Oracle 7. It says nothing about the merits or otherwise of multiplexing and/or mirroring.

> Of course, that is just one in my 10 to 15 classes on Oracle, so that
> isn't bad for Oracle.
>

It took you that many?

>
> > the "Oracle" as an entity recommends one approach over the other, I have
no
> > idea.
> >

>
> In a white paper off Metalink ... maybe I'll have time another day to
> dig it up for you.
>

Don't bother. Here's the documentation:

"Oracle provides the capability to multiplex an instance's online redo log files to safeguard against damage. When multiplexing online redo log files, LGWR concurrently writes the same information to multiple identical online redo log files, thereby eliminating a single point of failure. You can also mirror redo logs at the operating system level, but in so doing you run the risk of operating system or hardware induced corruption. In most cases, multiplexing of online logs is best."

(From Backup and Recovery Guide, 8.1.7. It hasn't changed since 8.0, and its the same in 9i).

By the way, the link is:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76993/data stru.htm#7922

And you might notice the last sentence there. "In most cases, multiplexing of online logs is best."

Goodnight Gracie.

>


> > > 4) These REDO logs are the "hottest" (I/O) files in Oracle .

> > >

> > > 5) You shouldn't put REDO logs on the same disk as tablespace files.

> > >
> > > 6) Oracle writes and reads(if in archivelog mode) these files a lot
but
> > > sequentially and so the "best" choice is to put them on a OS mirrored
file
> > > system, not a RAID5 disk (which has a write "penalty").
> > >
> > > 7) Specify more than just 1 or 2 groups if in archivelog mode. This
allows
> > > the LGWR (Log Writer) process time to copy the log before it is
reused.
> > >
> >
> > Uh huh. And how in God's name do you propose that anyone specifies just
1
> > group in the first place? You are required to have a minimum of two.
> >

>
> Got me there. Since I never create less than 3 or 4, I wasn't sure

So, you've been a DBA for 10 years, and have Oracle training back to version 7, but you don't know the minimum number of required online redo logs.

Uh huh.

> (hence the "1 or 2" comment).
>
>

> > > 8) REDO log sizes I have seen used are anywhere from 8Meg to 100Meg.
In
> > > media recovery, Oracle might take a little longer to apply the most
recent
> > > REDO log if large. But, a small size might generate too many archived
logs
> > .
> >
> >
> > You actually haven't got a clue, have you? In MEDIA recovery, Oracle
must
> > apply all redo generated since the time of the last backup, which
probably
> > means that a multiplicity of logs will have to be applied. In INSTANCE
> > recovery, Oracle has only to apply the contents of the current redo log,
or
> > the redo which was generated since the time of the last checkpoint,
> > whichever is the lesser.
> >
> > Therefore, the size of the logs is immaterial to INSTANCE recovery time,
if
> > you have induced additional checkpointing to compensate.
> >
> > And as for the comment that 'a small size might generate too many
archived
> > logs', what's that supposed to mean? What number do you think is "too
many"?
> > Oracle couldn't give a damn if you have 1000 1M archived logs, 100 10M
> > archived logs or 10 100M archived logs. It makes no difference to it
because
> > as soon as ARCH has written the archived log, it's finished with it, and
> > doesn't care what happens to it. And there's no discernible increase in
the
> > time it takes to apply 1000 1M logs compared to 10 100M logs.
> >

>
> You ever tried to do an "ls" command on a directory with 1000's of
> files ... pipe it to grep and you get strange errors... scripts start
> failing ... etc.
>

So, manage the archive destination properly.

> And too large, impacts INSTANCE recovery. >

No. Too long an interval since your last checkpoint affects the time it takes to perform an Instance recovery, not the size of your redo logs per se. It is perfectly possible to have enormous online redo logs, and have a perfectly respectable instance recovery time. That's what LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT, FAST_START_IO_TARGET, DB_BLOCK_MAX_DIRTY_TARGET and FAST_START_MTTR_TARGET are all about. Depending on your version.

>
> > DBAs forever have to worry about the AMOUNT of redo generated, because
an
> > archive destination that uses up all its available space is a problem
> > waiting to happen: but that's a function of size, not of number.
> >

>
> And you didn't even mention since 8i you can have multiple
> destinations :)
>

You can't even get this correct, either. Multiple destinations was actually introduced as a feature of 8.0, not 8i. LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST. In 8i, the LOG_ARCHIVE_DEST_n parameter was introduced, permitting you to have up to 5 archiving destinations, some of which could be remote databases for the benefit of automated transfer of redo to a Standby Database. But only if you used the Enterprise Edition of the software. If you used the Standard Edition, you had ARCHIVE_DEST and DUPLEX_DEST to fall back on.

In 9i, you are permitted to have up to 10 DEST_n destinations. But only if you have the Enterprise Edition of the software. If you used the Standard Edition, you had ARCHIVE_DEST and DUPLEX_DEST to fall back on.

And what any of that has to do with the appropriate size of your online redo logs, I haven't the foggiest.

>
> > Honestly. I wish you a Merry Christmas and an enlightening New Year. But
I
> > pray that when next you post, you actually know what you're talking
about.
> >
> > (And yes, I've been harsh here, because I don't want to see your
complete
> > distortion of the truth and half-baked misunderstandings go unchallenged
in
> > such archives as Google... newbies use that as a resource a lot, and
> > shouldn't have to wade through this sort of tripe).
> >

>
> My current newsgroup is not allowing me to post for some stupid
> reason. So, I use Google to post and my normal Bellsouth newsgroup to
> read.

Another total non sequitur. All newsgroup posts, made from wherever, are archived at Google.

Putting it bluntly, you may have been doing this stuff for 10 years, but your knowledge and understanding are demonstrably weak, and your initial statement that "Oracle's 1st recommendation is to use OS mirroring for REDO logs" is totally untrue, as a cursory glance at the documentation would have shown you.

Sorry you don't like my attitude, but those are just the facts.

HJR Received on Mon Dec 23 2002 - 05:55:22 CST