E-Business Suite cloning: Disabling auto-start of applications

A little while ago, Yury Velikanov at Pythian blogged about a new feature in the RapidClone tool for EBS 12.1.2. In summary, Apps DBAs finally have the option to disable the startup of application services after cloning is complete. As Yury notes, this is big help, because there's often a long list of post-clone activities to perform prior to releasing a cloned system to users, many of which are best performed before starting the cloned system for the first time.

If you're not on 12.1.2, and would still like to accomplish this feat, it's pretty straightforward, as long as you're comfortable with editing the adcfgclone.pl script in $COMMON_TOP/clone/bin. It's easy and painless, honest! Just locate and comment out the line that executes adstrtal.sh. If you'd rather not do this manually with your favorite editor, here are two perl one-liners that will do the work for you, and preserve the original version of the script for reference:

For Release 11i:
perl -pi.old -e 's/(system.*adstr)/#$1/' adcfgclone.pl

For Release 12:
perl -pi.old -e 's/(runPipedCmd.*adstr)/#$1/' adcfgclone.pl

Depending on your EBS version and AD patch level, the line you're looking for may be slightly different. As with anything else I write about here, if you're foolish enough to follow along, at least do yourself the favor of testing things first.

On that note, it's occurred to me that the functionality Yury discusses in his blog post may be available in earlier EBS versions than 12.1.2, provided you're running a very recent AD Minipack. I'd welcome your comments confirming or disproving this theory, since I'm too lazy at the moment to dig through release notes on My Oracle Support to check. :-)

Oracle OpenWorld 2010 recap: Time to get ready for that Fusion Apps upgrade?

One of the big announcements to come out of Oracle OpenWorld 2010 was the general availability timeframe (not, you'll note, a date) of the long-anticipated Fusion Applications ("coming soon, since 2006!"). The demogrounds were full of people test-driving Fusion Apps; there are tons of new features and a highly improved user experience. Now that we all know it's for real, it's time to ask for a new set of upgrade boots for Christmas, because when Q1 2011 arrives, it's going to be time to download the software, pore through the fresh, pixels-still-wet documentation, and plan for a transformative experience!

Yeah, okay, maybe not.

Whaddya mean, "maybe not?"

Please note: despite my tone above, Fusion Apps look pretty awesome, and really are a big deal. There's no doubt about that. But Oracle is definitely promoting a "dip your toes in the water, don't dive" approach to the implementation of Fusion. Why? Let's start with the obvious: Fusion Apps, despite all the love and care that went into its development, is version 1.0 software. It incorporates the best-of-breed features from all of the applications platforms; it's not a re-skin of E-Business Suite (or PeopleSoft, or JD Edwards) with Frankensteinian bolt-ons from other Applications products. As such, no matter how much testing is done, no matter how hard the early adopter customers beat on their 1.0 and pre-1.0 releases, there are going to be bugs. Possibly big ones, possibly small ones that turn out to be big for the way you do business. If you don't have a relatively strong need for particular features, or a high tolerance for the *ahem* thrilling rush of excitement that comes with the early-adopter experience, you might want to sit out version 1.0 and let the big dogs hammer out some of the still-to-be-discovered issues.

It may also be that version 1.0 of Fusion Apps doesn't cover all of the features that you need to run your business, or even have a corresponding module that maps to that small-but-critical applications product that keeps the lights on in your organization. This is one of the main drivers behind Oracle's "co-existence" strategy for its applications products. They want you to be able to implement pieces of Fusion Apps as it makes sense for your business, and not have to "leave behind" functionality that isn't quite ready yet on the new platform.

Those are two reasons that Fusion Apps may not be ready for you. It's also worth considering that you may not be ready for them. There are technology components in play in the Fusion Apps tech stack (Fusion middleware, or FMW) that are still relatively new on the scene, and it will take time to adjust to using them for reporting, development, business process modeling, and general systems maintenance and administration. If you're not currently using FMW components in your organization, it may make sense to start easing them into your infrastructure before also taking on Fusion Apps. A bit more on that below.

Planning for an "big-bang" upgrade or re-implementation?

If there isn't a one-to-one mapping of features between your current applications deployment, you aren't stuck. In fact, you probably shouldn't plan for a monolithic upgrade. As noted above, and in just about every piece that Oracle writes on the topic of Fusion Apps, it's possible, and generally recommended, to take an approach that results in Fusion Apps and your existing "Applications Unlimited" products existing side-by-side. For example, you could elect to upgrade Financials to Fusion Apps, and wait for Manufacturing components to mature to the point where you feel comfortable taking that upgrade (Note: That's just an example, not an implied judgement of the quality of either product suite. Remember, I'm an Apps DBA, and not particularly qualified to judge the maturity of products from a functional perspective). The impression that I got is that full one-shot moves to Fusion Apps, whether through re-implementation or upgrade, are going to be exceedingly rare.

There are downsides to this approach, of course. Without (or perhaps even in spite of) careful planning, a split configuration is all but guaranteed to involve a broader footprint of installed software: different middle-tier and database software installations, along with multiple databases, to support the deployment Applications Unlimited and Fusion Applications. Hat tip to Floyd Teter for pointing this out to me after one of his presentations, thereby confirming a nagging suspicion. This has implications for the size and complexity of test and development systems as well; I look forward to reviewing the inevitable white papers that address ways to ameliorate these architectural issues.

Fine, then, what do we do instead?

A major theme of the discussion around Fusion Apps at OpenWorld was "Upgrade, Adapt, Extend." Nadia Bendjedou's "10 Things you can do today to prepare for Fusion Applications" presentation goes through this in detail, but here are the high points:

Upgrade: not what you might think. This is "Upgrade" as in, "Upgrade to the latest release of your current Applications Unlimited platform"

Adapt: This was the meat of Nadia's "10 Things" talk, and elements were reinforced throughout other sessions I attended. The idea is that once you're on the most recent version of your current applications platform, you can take advantage of the various components that underpin Fusion Applications, before implementing Fusion Apps themselves. Examples include: BI Publisher, OBIEE, BPEL, SOA (everyone's favorite), WebCenter, Secure Enterprise Search, and UPK. Furthermore, "Adapt" includes taking advantage of new functionality in the more recent releases to eliminate customizations, or convert them to configurations that are more likely to survive an upgrade. The phrases "upgrade-safe" and "future-proof" were tossed around at the conference, but I try to maintain some level of healthy skepticism. ;-)

Extend: This doesn't imply extending applications functionality with external components, but rather extending your applications deployment into Fusion Applications. Find the areas of your business that are ready for and can benefit from Fusion, and take up those portions as it makes sense to do so. Ideally, this comes at a time after your DBAs and app server admins are familiar with supporting the FMW tech stack, your developers are up to speed on Jdeveloper and the latest development frameworks for Apps, you're managing your business process flows with BPEL instead of Workflow, and your business intelligence architects are cranking out BI Publisher reports and OBIEE dashboards like they've been doing it for years.

The big message, then, is: don't sit still. Get to the most recent version of your current applications system, get comfortable with the new functionality and technology components, see what problems they fix for you, and then start looking at Fusion for the problems that it doesn't fix.

What about EBS 11.5.10 customers?

Now that Fusion Apps are genuinely on the horizon, it may be tempting for E-Business Suite customers that are on 11.5.10 to try to ride out the extended and sustaining product support cycles, and make a big jump to Fusion once the dust settles a bit from the initial release. As implied above, though, monolithic upgrades aren't being encouraged, and there are benefits to getting on the more recent version of EBS (some of which I covered earlier). Oracle is strongly recommending that E-Business Suite 11.5.10 customer get to R12 as soon as they can. Furthermore, as Floyd Teter points out, Oracle is has committed to an upgrade path from 11.5.10 to version 1.0 of Fusion, but they haven't made a commitment for anything beyond that (second time I'm linking him in one post, but hey, Floyd's a smart dude. I listen to Floyd). We've already talked about the potential pain of a jump to 1.0. Do you want to inflict that level of change on your organization, from techstack to data model to end-user experience? Sounds like a recipe for massive levels of discontent to me.

So, bottom line: If you're an EBS 11.5.10 customer and thinking about jumping directly to Fusion Apps, rethink that strategy. You have more to gain by upgrading to R12, building organizational competencies with the more modern technical components, and taking up Fusion Apps as the benefit to your business becomes clear. Naturally, this advice also applies to Peoplesoft, JD Edwards, and Siebel customers; I'm just not up-to-date on the versions numbers in the lifecycles of those products. :-)

But I really want to kick the tires on this stuff! I have pain! I think Fusion Apps is the cure!

Well, that's a topic for discussion between you and your Oracle reps; I'm just one blogger trying to synthesize notes from a week's worth of sessions and informal discussion. :-) Obviously, Oracle has an Early Adopter program for Fusion Apps, if you think you're a viable candidate. I also learned that Oracle has "assessment workshop" teams: groups of functional experts that can come onsite to evaluate your fitness/readiness for Fusion Apps. I didn't scribble down the reference quickly enough, but I'll try to dig up a link and post it here.

Okay, I think I've repeated myself enough here. On to the next post!

Openworld 2010: Gratitude

Oracle OpenWorld may be a technology conference, but the people make the week. Otherwise, one might as well stay home to watch webcasts and flip through slide decks. Before I start generating my requisite nerdy recap content, I want to get started on the right foot: thanking the people that made the trek worthwhile. I worry a bit about my tone, since it's easy for public thank-you's to be misinterpreted as self-indulgent name-checking; I guess I'll just have to ask you to trust my sincerity. :)

In no particular order, thanks to...

... the marketing and communications team at Oracle, for the opportunity to attend on a blogger pass, and providing access to the press area for a quiet area to work and escape the hordes of people.

... the OTN team (Justin {blog | twitter}, Vicky, and Lillian in particular), for making me and so many others in the online community feel welcome.

... Pythian, for the fun bloggers' meetup: Alex Gorbachev {blog | twitter} for spearheading, Vanessa Simmons  for behind-the-scenes cat-herding, and Paul Vallee {blog | twitter} for sponsoring.

... Mogens Nørgaard {blog | twitter} for organizing OCW, and to the attendees for not remarking on the precipitous drop in average IQ when I slipped in to catch a few sessions.

... Floyd Teter {blog | twitter}, for dispensing wisdom, interesting stories, and lunch, and for treating me like we'd met before, even though we hadn't.

... Steven Chan, for covering enough information in his Sunday SIG session that I could sleep in a bit on Wednesday, and for clarifying some outstanding questions for me.

... all the people who bought me beer: Ronald  {blog | twitter}, Floyd, Chet  {blog | twitter}, Jake  {blog | twitter}, Dan  {blog | twitter}, Terrance, and others I may have (shamefully) forgotten. I somehow managed to go the entire show without paying for a beer, which is a bit like winning the lottery, even if (as Jake and Chet pointed out once or twice) I wasn't exactly a heavy contributor to the size of the tab.

... Chet, for housing assistance and for making the week far more entertaining than it would have otherwise been, and for knowing how to give and receive abuse in equal measure. ;)

... Jake, for so much stuff: food, beer, introductions, interesting conversation, taxi service. I could be more effusive, but he'd probably tell me to shut up. ;-)

Y'all are awesome. Thanks again!

OOW10 Day 2 mid-day update: Interesting new EBS techstack functionality

The draft of my Day 1 post is not quite complete, but I want to post this before my thoughts from the recent "EBS Vision, Strategy and Roadmap" session fade into an under-caffeinated haze. OpenWorld is a bit draining; I can only assume I will be a husk of my former self by Thursday evening.

The session covered a lot of information at a pretty high level, including information that is already familiar to people that have already migrated to R12. That's not suggesting that the content was not useful; about 67-75% of the very large room indicated by show of hands that they were still running on Oracle Applications 11i, and a significantly smaller percentage indicated that that they were in the middle of an R12 upgrade. The following points really stood out for me, though:

  1. Support for using Active Data Guard for an E-Business Suite reporting instance. The core challenge in using Active Data Guard and E-Business Suite is that you can't write to an Active Data Guard database, and the simple act of authenticating to E-Business Suite requires writing to the Oracle Applications tables. The Applications Technology Group has devised a way to get around (or perhaps more accurately, work with) this limitation, and have begun an early-adopter program for interested customers. Two important restrictions to note are:
    • The functionality is being made available as a patch on version 12.1.3 of Oracle Apps, so you'll need to be pretty current with your tech stack
    • This will only work for "read only" reports, though Oracle can also provide a method to identify which reports can be run against an Active Data Guard database instance.
  2. Lightweight multilingual support, a feature new to version 12.1.3, allows Apps users to perform general data processing (data entry, reporting, etc) in multiple languages without delivering the full applications UI in those languages. This has the advantage of reducing the patching/maintenance burden on Apps DBAs, and that's never a bad thing. :) It was noted in the presentation that traditional full multi-language implementations can coexist with additional "lightweight" language implementations, and that it's possible to migrate lightweight versions to full language implementations.
  3. Use of a staged APPL_TOP for upgrades from 11i to R12. This topic was actually covered by Robert Farrington on Steven Chan's blog last week, but it's worth calling out again. This feature has the potential to save a lot of time during an R12 upgrade; particularly when you consider that an R12 upgrade these days often will require applying a patchset atop the based release, with the accompanying rebuild of a staggering number of forms and reports.
  4. Finally, a "future release" of E-Business Suite (presumably 12.2) will include support for Edition-Based Redefinition. I had a slightly rambling blog post about this possibility earlier this year, and it's apparently going to become a reality. If you're interested in the possibility of near-zero-downtime EBS patching, now's the time to upgrade your database to 11gR2. Or you could wait until EBS Release 12.2 ships and upgrade both at once, but who needs that kind of stress?

As with all really cool Oracle Applications technical news, you'll undoubtedly find more cogent discussion on Steven Chan's blog in the coming days/weeks as the dust settles from Oracle OpenWorld. I'm just calling attention to these topics for people who might be interested, so you know to watch for more information.

Christmas comes early: SQL Developer Data Modeler now free(r)!

The week that encompasses Oracle OpenWorld is a bit like a holiday season for ORACLENERDs: new toys (product announcements, long awaited product updates, exhibitor tchotchkes), mingling with far-flung "family" and friends, and of course unhealthy amounts of food and drink that will take months of exercise to counterbalance.

Today, two days before the official start of the conference, we got an early gift: SQL Developer Overlord Kris Rice (@krisrice) and OTN Grand Admiral Justin Kestelyn (@oracletechnet) tweeted the following:

201009171826.jpg
201009171824.jpg

That's kind of a big deal. The Data Modeler add-on for Oracle SQL Developer is a great tool, but the licensing fee for the full product -- there's always been a free read-only version -- has been cited by some as pretty steep. No longer! The OTN content related to Data Modeler is still being revised, but it no longer appears on the Oracle price list (PDF warning on that link), and Kris Rice graciously followed up with a clarifying tweet expanding on Justin's "...free for Oracle Database customers":

201009171847.jpg

So there you go. If you have licenses for Oracle RDBMS software, then SQL Developer Data Modeler is all yours. There's a risk that this news might get lost in the flood of other stuff that's coming out of OpenWorld this week, since tweets from the Ace Directors briefing can best be summarized as "OMG U GUYS 2 BAD WE'RE UNDER NDA TIL SUNDAY WOW!!1!one!" If you or your team have need of a data modeling tool, but have been put off by the pricing of some of the big players (I know I have, given how infrequently I need to use one), this is definitely worth a look, and Oracle just removed your last excuse to give it a try.

Kudos to Oracle for getting this done!

Oracle OpenWorld 2010 planning

I'm pretty excited to be heading to Oracle OpenWorld this year. My only previous OpenWorld was in 2006, which feels like eons ago. This time around, I'll be going on a blogger pass, which I feel is very generous of Oracle, since my most popular post has been a piece that I wrote for someone else's blog, and the most popular page on this blog is basically a 5-line shell script. *ahem* At the very least, I hope to become worthy of the blogger title by blowing the doors off my 2-posts-per-month lifetime average during the month of September.

With the conference less than 3 weeks away, I'm mostly all set. Plane tickets and hotel were booked long ago. I've learned that getting from SFO to my hotel via BART will be ridiculously easy. I've even managed to arrange a roommate who's certain to take all blame in the vanishingly unlikely event of shenanigans. ;-) There's just one problem: there's way too much to do once I get there! Okay, maybe that's not a problem; call it an abundance of riches.

The challenge: Fitting it all in

When I left OOW 4 years ago, I told myself that I would "do better next time." It felt like I hadn't managed to get as much out of the conference as I could have. This time around, I'm hoping to strike a better balance between sessions, exhibits, keynotes, and, um...

oow10-networking.png

...I'm not sure what Chet means by those first two, but I do hope to work in some networking. I'm looking forward to meeting some people with whom I've mostly interacted 140 characters at a time, or via my longer-winded blog comments. I'm already signed up to attend the Blogger meetup, and am looking for other opportunities to meet people; drop me a comment or an email if you'd like to grab a coffee, beer, or just exchange a quick handshake. :)

But back to that "abundance of riches" problem. I'm having a heck of a time reconciling my general interests (Oracle Apps, "Core" database, virtualization), specific areas of curiosity (this year, GoldenGate and MySQL), and my desire to see presentations by people way smarter than me. Thankfully, there's a fair amount of overlap on that last point, but I'm still looking at almost 40 interesting sessions over 5 days, not counting keynotes and hopefully an Unconference visit or two. It's unclear when I'll be eating lunch, most days. I'm even double- and triple-booking Sunday, for heaven's sake, and that's "just" SIG/User Group day!

The List, so far

Here are the sessions that I'm thinking about. If you have any thoughts about how to make this more sane, or (I can't believe I'm typing this) if you think I might enjoy a session that's not listed here, I'd love your feedback. Thanks to Floyd Teter (@fteter), Steven Chan, and the people who put together the Oracle ACE presentations page on the Oracle Wiki for making my job slightly easier by providing some initial filters for the 2000+ sessions. Now if only I could find some mad scientists to finish the work on these cloning vats...

Apps, EBS, Fusion, Whatchamacallit

I'm probably going a bit overboard on some of the R12 DBA and R12 upgrade stuff, but it's always a good idea to hear what others have done/are doing, and the surest path to stagnation is to assume you already know enough.

Oracle RDBMS

There are some folks in here that I'm really interested in seeing, since they're known to be great presenters. It was hard enough to winnow the database track down to just this list.

General Hodgepodge

And here's the grab-bag, which is not to suggest I have a lower level of interest. I'm just too lazy to subcategorize further. :-)

Resolving a pesky ORA-12545 during EBS patching

I'm working with a client for the next few weeks to help them meet baseline patching requirements for EBS 11i Extended Support. You know what that means:

  1. A bazillion browser tabs
  2. An SR or two
  3. Piles of patch READMEs
  4. Resolving prerequisites until your eyes cross
  5. Memorizing individual patch numbers, whether you like it or not
  6. The reward for all the hard research work: "15 jobs running, 235 ready to run, and 50682 waiting."
  7. Sporadic ORA-12545 errors in the adworker logs

Yeah, okay, #7 caught me a bit by surprise, too. The really tricky bit was that the worker wouldn't usually be marked as "Failed" when it received an ORA-12545. Instead, it would stay in a "Running" state, and eventually the entire patch session would go idle, until I manually restarted the errored worker. There is no shortage of troubleshooting notes for ORA-12545, but the essential condition is that the client cannot resolve the hostname of the database server. Considering how sporadic the errors were, this was more than a bit strange. So we tested client connectivity using the various permutations of the EBS database hostnames, verified that there wasn't any weirdness in the RAC listener setup, and still the problems persisted.

At a high level, it's always disturbing to have sporadic network wonkiness. More practically, it wasn't very much fun to contemplate writing production patching instructions that read, "Babysit the adpatch session very closely. Manually restart any workers that fail with ORA-12545." (I could also note that it's not very much fun to have a multi-hour patch test run grind to a halt 10 minutes after leaving the office for the day, but that would be whining, so I won't do that). I was just starting to consider running SQLNet traces to try to capture more information about this 1-in-1000 chance error condition, when one of the client's DBAs mentioned that the test systems and the DNS server were in separate data centers, connected over a WAN.

Rather than suggesting that we march over to the network admins and claim that DNS requests were being dropped on the floor (what a great way to make new friends!), and lacking the time to put together an easily-reproducible test to back up such a claim, I recommended adding the hostnames and IPs of the database servers to the apps tier's /etc/hosts file. All subsequent patch runs were free of ORA-12545 errors, which left me free to concentrate on other, more interesting errors. I'm not a big fan of solution-by-assumption, but in this case I was able to carry the work forward, and the investigation into potential DNS problems can wait for a quieter time.

Weekend mumblings: Oracle and VMware

I’ve had VMware and Oracle on my mind recently, probably for three reasons:

  1. In the last week or so, I’ve been involved in a two discussions about the viability/supportability of Oracle E-Business Suite on VMware, one on OracleCommunity.net, and the other on LinkedIn.
  2. The IOUG recently sent out a VMware-sponsored email entitled, “Why Oracle DBAs should care about virtualization.”
  3. I am an unreformed, unrepentant nerd.

So, if you're up for some lazy Saturday evening musings about Oracle and VMware, keep reading. I’ll try not to ramble too much.

FUD or misinterpretation? The difference between “supported” and “certified”

The crux of the recent online discussions in which I participated was the age-old question: Will Oracle support customers running on VMware, or not? The confusion arises from a My Oracle Support note stating Oracle’s position on the topic: Support Position for Oracle Products Running on VMWare Virtualized Environments (Doc ID 249212.1). If read too quickly, or too conservatively, it’s possible to conclude that Oracle won’t provide full support for its products in a VMware environment. Coupled with the knowledge that Oracle offers its own virtualization product, Oracle VM, that it does fully certify and support, it’s easy to see where Oracle customers might think twice about considering VMware as a virtualization platform.

It’s important to remember, however, that when it comes to Oracle products, there’s a big difference between certification and support. Certification of Oracle software on specific hardware platforms, for example, is pretty much out of scope for Oracle Support, and VMware is, in effect, providing a virtual hardware platform upon which to run your systems. The My Oracle Support note referenced above actually spells out pretty clearly how Oracle will support you if you’re running on VMware. What the note effectively states is, “If the reported issue looks like a problem with our software, we will support you. If your issue looks like it’s related to VMware, we’re going to send you to VMware for resolution.” While it might seem like this is a less-than-usual level of support, it’s actually entirely fair. If your problem could be traced to the OS, after all, you could expect to be referred to the OS vendor. Similarly, if the conclusion were that you had hardware problems, you could expect to be sent to your hardware vendor.

Incidentally, this illustrates a core benefit of the Oracle-OEL/Solaris-Oracle-VM technology stack: if you have issues that require intervention from Oracle Support, whether they’re with software, hardware, or virtualization layer, there’s no concern that you’ll be left playing “vendor volleyball.” Your issues will be handled entirely by Oracle, and there won’t ever be any finger-pointing between the various product support teams, because everyone is living in peace and harmony. *ahem* :-)

Bottom line: Currently, yes, you’ll be supported if you run your Oracle environment in VMware. Unless your problem turns out to be VMware-related, in which case you probably want VMware’s help anyway.

That’s great and all, but does it work, particularly in production?

DIsclosure time: I’ve had my doubts in the past about the viability of VMware for production E-Business Suite environments, as noted here in a comment on Steven Chan’s blog. I’m just one guy, though. VMware’s products have matured since then, and some of the problems I alluded to were not strictly VMware issues. Even back then (2005-2006), though, I was comfortable running Grid Control and some production Oracle Collaboration Suite application tiers in VMware. From what I’ve read, things have only gotten better with VMware vSphere.

It’s worth noting that VMware itself runs Oracle products, including E-Business Suite, in a VMware virtualization environment. They also have a list of customer references on their web site; if you elect to use VMware in your production Oracle environment, you won’t be in uncharted waters. If you’d like to read about the experiences of an Oracle customer running on VMware without the marketing filter of the vendor’s website, I recommend reading Jay Weinshenker’s blog. That’s twice I’ve linked to him in two blog posts, but I’m not digitally stalking him, I swear. He just writes interesting stuff. ;-) I’ll probably link to him one more time before I’m done here.

Why not just use Oracle VM?

I have far more experience with VMware than I do with Oracle VM, so it shouldn’t be surprising that I present as a VMware fanboy. Oracle has made a clear commitment to the virtualization space, however, and has put together an attractive package to support it. Oracle VM might be a good fit for you, particularly if you:

  • are interested primarily in virtualizing Oracle products (note: Oracle VM can be used for non-Oracle virtualization needs as well)
  • haven’t already made an investment in VMware in other parts of your IT infrastructure.
  • prefer having just one vendor to flog when things go wrong. :)

References/Additional reading

As if I haven’t packed enough links into this post, here are a few more:

Recently relevant (to me) links

I've found a few bits of interesting reading over the past week or two. Most of this content is not brand-new, but since they've added some technical enrichment to my life, I figured that a "shout-out rollup" was appropriate.

  • Jonathan Lewis' review of a Collaborate '09 presentation on tuning by cardinality feedback, and the slides from that presentation, helped me refresh my approach to a report tuning exercise.
  • Kerry Osborne saved my sanity with an 18-month old post, as I tried to figure out why a 'shared server idle wait event' was skewing my 11g Statspack reports.  
  • Ever wondered what the translations are for the command_type column in V$SQL? Martin Widlake offers a few pointers to get that info, in not one, but two posts.
  • Finally, if you're interested in some real-world information about using Oracle E-Business Suite on VMware (and who isn't, really?), J Weinshenker (Twitter: @aus_effendi) has recently spun up a blog about those topics and others of interest to ORACLENERDs of the DBA/Apps DBA variety. Quality content there; go read!

Small change to GL_SECURITY_PKG in EBS 12.1.2

After a recent upgrade to Oracle Applications 12.1.2, a client started experiencing problems with Discoverer reports. These reports were based on views in a custom schema that use APPS.GL_SECURITY_PKG.VALIDATE_ACCESS to restrict the returned data set. Despite no known changes to the reports or associated permissions, reports were terminating with the following error:

ORA-00942: table or view does not exist
ORA-06512: at "APPS.GL_SECURITY_PKG", line 1427

Line 1427 of GL_SECURITY_PKG starts a query against table owned by APPS to which the custom schema did not have access. While this section of the code had not changed between the original system and the upgraded one, one thing that had changed was the package header.

Here's the header from the original package:

SQL> select line
  2       , text
  3    from all_source
  4   where owner = 'APPS'
  5     and name = 'GL_SECURITY_PKG'
  6     and type = 'PACKAGE'
  7     and line <= 2
  8   order by line asc
  9  /

      LINE TEXT
---------- --------------------------------------------------------------------------------
	 1 PACKAGE gl_security_pkg AS
	 2 /* $Header: gluoases.pls 120.10.12000000.1 2007/01/16 22:31:44 appldev ship $ */

And on the upgraded system:

      LINE TEXT
---------- --------------------------------------------------------------------------------
	 1 PACKAGE gl_security_pkg AUTHID CURRENT_USER AS
	 2 /* $Header: gluoases.pls 120.10.12000000.1 2007/01/16 22:31:44 appldev ship $ */

Somehow, the GL_SECURITY_PKG was recompiled with invoker's rights (CURRENT_USER) instead of the default DEFINER rights. This explains why the custom schema was suddenly unable to access the APPS-owned tables without explicit permission to read the tables.

It may be that someone manually compiled this package with AUTHID CURRENT_USER, but it seems more likely that this was done during patching. The short-term "band-aid" fix here is to grant read permission to the custom schema on the tables referenced in APPS.GL_SECURITY_PKG. The longer-term solution, currently underway, is to engage with Oracle Support to determine how or why this package was recompiled with the AUTHID CURRENT_USER property, and learn if there is new guidance regarding the use of this package.