Re: Redo Log Question

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<sHCN9.8808$jM5.25161_at_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.

Today has been a busy day, but I had to comment one more time...

You still have an attitude problem. And, you don't consider the above comment an insult ?

If in your opinion, I said 1 thing in error, then does that mean I do my job badly ? You have no idea.

I don't care how much you know about Oracle, I wouldn't want to work with you.

Just because you aren't face-to-face, you think you can insult anyone? You don't sound like you work too well in this environment.

>
> > 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.
>

I lost all faith in Oracle doing the 2nd write better than the OS when I had to do the recover mentioned below. Maybe your experience is better. But, my experienced recover in version 7.3.2.3 shows that Oracle does/did indeed mirror software corruption that Oracle introduced.

My first choice "with unlimited budget" would be to both multiplex and OS mirror.

Now, I cannot find that paper. I know I have a hardcopy in my office, but I won't be there for a couple of weeks.

But, I see now in a quick check on Metalink that there is a consensus that the 1st choice is multiplexing. Interesting... I know I didn't imagine the paper from Oracle recommending OS mirroring first.

Anyway, I have had some good luck with OS mirroring and the above mentioned bad luck with Oracle multiplexing.

> >
> > > 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.

Apparently not impossible since that IS what occurred in my experience noted below.

>
> >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?
>

See above comment.

> >
> > > 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.
>

Maybe my attitude on Oracle mirroring/multiplexing could use an update. As I said above, I lost all faith in Oracle mirroring during the recover mentioned above.

> > 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.
>

You mentioned first "Thus speaks an Oracle instructor" . I was only pointing out that doesn't always guarantee it.

> > 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?
>

Oracle6 Introduction
Oracle6 DBA I
Oracle6 DBA II
Oracle6 CASE Tailored Class
Oracle7 DBA I
Oracle7 DBA II
Oracle7 Application Tuning
Oracle7 Server Tuning
Oracle8+8i New Features for DBAs

Ok, so only 9. I suppose I was thinking of all the Unix type classes like Bourne programming, Pro*C, etc. .

> >
> > > 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:
>

Yeah, this appears to be the latest.

> "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.
>

Yeah, don't make the log too small right ? Of course, clean up after backing up twice too.

>
> > 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.
>

Seems easier to just size the REDO at a good time for checkpointing, since checkpoints occur when the log switches.

Why complicate things when you can make them simpler?

Does anyone really tweak these parameters? I usually just set the interval real high to force checkpointing at log switch time.

> >
> > > 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.
>

We skipped 8.0 .

> 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.
>

Again, you mentioned "such archives as Google". I was only pointing out that I don't use it normally.

> 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.
>

I might have spoke too soon with mentioning the phrase "Oracle recommends" , although I do have a paper from Oracle stating that . And, yes I lost faith in Oracle multiplexing back when I had to do the recover mentioned above. Oracle put corruption in the REDO and mirrored it. I was multiplexing and it was useless.

> Sorry you don't like my attitude, but those are just the facts.
>
> HJR
Received on Mon Dec 23 2002 - 21:08:49 CST