Kevin Kempf's Blog

June 21, 2010

Patch 6856379 & PLS-00801

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

11.5.10.3?  Not quite

Oracle announced a new baseline beyond 11.5.10.2 required for extended support (although they refuse to call it 11.5.10.3, it’s about as ambitious in scope/size of patching).  I’ve spent my last 3 maintenance windows applying patches relevant to my environment to conform to this requirement, for a rough total of about 12 hours of adpatching.  The last phase involved applying an update to Order Managment (Dec 2008 Cumulative Patch 6856379).

OE Objects Invalid

After applying this patch, along with ATG_PFH_RUP7 (6241631), a handful of objects in the OE (order management) schema would not compile through any means. After reviewing document 785179.1, I attempted adadmin, changing the order of compiles, even command line compiles, but continued to see:

PLS-00801 Internal Error (1401)

Eventually, I found my way to bug 8209917, which said, in essence, that some customers report that after applying patch 6856379, certain OE objects would become invalid, unable to recompile, and cause a PLS-00801 error.  Bugger.

The Fix

This is really where it fell apart for me.  The only known fix was to run utlirp.sql (invalidate all objects) followed by utlrp (recompile all invalids).  Hugely, massively disruptive process there, especially for a 24/7 production system.   By definition, utlirp has to be performed with the database in upgrade mode (startup upgrade) which means it might as well be down as far as users are concerned.  We decided to undertake this process during a slow business time (lunch!) and the process took over an hour (23 mins to invalidate, 49 minutes to recompile about 97,400 invalid objects in parallel 12 (job_queue_processes=12)).

In the end, it did work.  We undertook the emergency maintenance because the loss of service was so impactful; shipping couldn’t get items out the door, and sales had trouble with certain order entry screens.  They were really verbose errors, all to the extent of “OE_DEFAULT_LINE has errors”  or the like.

Afterthought

Last November, when the “rate this patch” feature became available, I laughed at it and said “why would I ever do that?”.  Now I know.  Maybe I’ll go rate that patch as 0 stars.  Or maybe I won’t, and let Oracle do their own work while I try to do mine?

Advertisements

Create a free website or blog at WordPress.com.