Kevin Kempf's Blog

December 21, 2010

Making Oracle RDBMS play nice with 11i

Filed under: 11g, 11i, Oracleisms — kkempf @ 2:25 pm

Fighting with Oracle Support

Determining a Version of the Truth

As often happens, I’m writing this post as a result of getting burned, badly, by the various versions of the truth the world’s slowest support site, My Oracle Support, can provide.  In my case, as a result of applying PSU 11.1.0.7.4 to the database and being a user of advanced compression, I was in a situation where my database, while running, was virtually unrecoverable as the archive logs it was writing were all corrupt.  After resolving the issue (which I’ll detail below) it got me thinking about how, as a DBA, you have to go to so many different, disparate sources within Oracle’s own support site to get a complete answer.

Without thinking about it too hard, I came up with 6 critical, disparate sources of information I need to cross check for my environment.  I stumbled upon the 7th during the course of writing this entry.  For the record, I run RDBMS 11.1 on 64-bit Linux, using Advanced Compression and Dataguard, against an 11i Oracle Applications Environment

  • Interoperability Patches
  • Patch Set Updates (PSUs)
  • Post-PSU Patch Set Overlays
  • Recommended Performance Patches
  • Critical Patches for Advanced Compression
  • Dataguard Recommended Patch Bundle
  • Oracle Recommended Patches – – Oracle Database

Interoperability Patches

Any DBA who has been working in an 11i environment for long is familiar with this one.  It’s a fundamental road map of how to get your database certified against 11i, and should always be your starting point.  In my case, this would be document 452783.1, and it requires 9 RDBMS patches:

Patch Set Updates

Lets say I’ve decided to be a good DBA and apply Oracle RDBMS patch set updates (PSU’s).  These are cumulative, quarterly patch sets described by Oracle as “proactive cumulative patches containing recommended bug fixes”.   I have to admit, I like the concept, except I very naively assumed that these would contain some reasonable approximation of what  I would come up with if I independently researched all the patches I thought I needed.  You can find all of this information under Doc 854428.1, and each PSU conveniently bundles all the RDBMS fixes into one opatch bundle (in the case of 11.1.0.7.4, opatch tells me this bundle contained 408 bug fixes).  In my case, this amounted to only one patch:

Patch Set Update Overlays for 11i

I didn’t know these existed until recently (thanks, Steven), but if you’re running 11i and apply a PSU, you need to go to Doc 1147107.1, and in my case, this amounted to 6 more patches.

Recommended Performance Patches

This is where it gets a lot dicier, and in a hurry.  To be honest, this document is such a hodge-podge it makes the rest of My Oracle Support look downright usable.  This set of patches, outlined in Note 244040.1, are “a list of performance-related patches that Oracle E-Business Suite Development recommends should be applied”.

After scrolling through hundreds of 10g patches, I come to what I believe to be a list of applicable patches, but I’m stumped.  Some are labeled 11.1.0.7, and some 11.1.0.7.0.  I’d be inclined to say that 11.1.0.7.0 is a specific version (as opposed to 11.1.0.7), except the ones listed there say the bugs are fixed in 11.2.  So lets go on the conservative side, and assume I should apply anything with 11.1.0.7 or 11.1.0.7.0, unless I specifically see some contrary information.  I’m not going to get too picky, but it would be nice if 11.1.0.6 patches didn’t follow 11.1.0.7 patches.  Makes the whole process just take longer.

10g and Later Oracle Database (Performance Related)

  • 7533387
  • 8303523
  • 8508056
  • 8580883
  • 8447623
  • 8602840
  • 7522002
  • 7533387
  • 8303523
  • 8800514
  • 9320786
  • 8792821

10g and Later Oracle Database (Not Performance Related) What does this even mean “Not Performance Related”?

  • 7627743
  • 8199107
  • 7639602
  • 8874285
  • 7291739
  • 7684818

Oracle Database 11g Advanced Compression Option

  • 8409203 (this patch, and a half dozen others, says it’s for 11.1.0.7 and “fixed in 11.1.0.7”)
  • 8483871
  • 8608377
  • 8599477
  • 8364676
  • 8857905
  • 8983266
  • 8914197
  • 8876094
  • 8834636
  • 8980613
  • 9166322
  • 9275072
  • 9341448

It’s worth noting that after the RDBMS patches, this document goes on for days with recommended functional 11i patches.  I’m not including them in my list here for two reasons.  First, they were mostly sub 4000000 series patches.  That means they’ve existed for about a thousand years, and you’ve probably already got them by now.  Second, it doesn’t really flow well with this posting, which is specifically addressing RDBMS patching.

List of Critical Patches Required For Oracle 11g Table Compression

Following Note 1061366.1, I find only one (bundle) patch required for Advanced Compression.  Of note is that the applicable patch is dependent upon which version of the Patch Set Update you’re on (11.1.0.7.4 in my case).

Dataguard Recommended Patch Bundle

According to Note 7628357.8 there’s a bundle for Dataguard.  I’m not entirely certain if I’m supposed to apply patch 7676737 or 7628357, so I’ll list them both

  • 7676737
  • 7628357

Oracle Recommended Patches – – Oracle Database

I stumbled on document 756671.1, and noticed it had yet more patches (and some duplicates) to add to the list under the category “EBusiness Suite R11i Certification”

  • 7295298
  • 7253531
  • 6530141

My Advanced Compression Bug

As hinted at earlier, the bug I was hitting required RDBMS patches 9019113 and 9294495 to resolve the issue.  While un-addressed, the database was writing logs which were corrupt.  This meant that my database was effectively irrecoverable.  If you’re running advanced compression with 11i and apply PSU’s, I would urge you to apply these two patches.  Note that these patches don’t fall under PSU’s or recommended performance patches (advanced compression).  I could see where they wouldn’t be that important.  A recoverable database isn’t that important.  This issue manifested itself in the fact that my physical standby started choking on redo logs after I put in PSU 11.1.0.7.4; otherwise it would have been undetected until I tried to run my next clone.

Adding up the Score

For anyone who made it this far, congratulations!  The point of this exercise is to state, in the most obvious possible terms, how convoluted Oracle’s approach to RDBMS patchsets is.  Would anyone in their right mind pull all 55 patches listed above, and apply them blindly?  Why aren’t these 54 patches (all but the PSU itself) just included in the PSU’s, with the option to not apply them if you’re not an 11i user?  Barring that, why not have an 11i RDBMS PSU which contains these other 54+ patches (I’m sure there would be more, as CRS and RAC seem to add dozens of patches as well).  How long does it take to research this, how do I know how often the documents get updated, and which of these 55 are in conflict with each other?  How do I know I haven’t missed some other, seemingly random note, advising me to apply some other patches?

In the Real World

If you’re like me, you apply what looks relevant, seems safe, has been regression tested, or fixes a bug.  Then you get on a stable version of the RDBMS and you try not to screw with it too much.  The CPU/PSU model really screws that up, and I don’t know exactly how to make it fit in.  It’s certainly not a science so much as an art, at least the way Oracle makes it so hard to keep up with now.

For What It’s Worth

If I were making a Christmas wish list for Oracle Support, it would be simple.  Take all the time and effort you spend using Flash to make your support site so completely unusable, and instead come up with a new tab.  Call it “Compatibility” or “Interoperability” and give me a web page where I can use drop down boxes and check boxes to enter in a version of the database, with whatever options I might be using, and show me every patch I need to look at related to it.  For example, in my case, I’d love to be able to ask, via MOS, “What are the relevant patches for RDBMS 11.1.0.7 with Dataguard and Advanced Compression options, running in split tier configuration on 64-bit Linux against Oracle Applications 11i?”.   One can always hope..

Advertisements

1 Comment

  1. […] the RDBMS.  After my most recent debacle with overlays, recommended patches, performance patches, and advanced compression patches, […]

    Pingback by January, 2011 Critical Patch Update is out « Kevin Kempf's Blog — January 21, 2011 @ 9:44 am


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Blog at WordPress.com.