Sunday, October 30, 2011

Why It Was Not So Terribly Bad to Have Missed OOW This Year...

Sacrilege! Did I just name my article the equivalent of "Join Me in Skipping OOW, You Troublemaking Scamps"?

I am very sorry. This year I was totally prepared to go. I had my plane ticket and a free pass. I was totally beefed up to ride the BART, even from the AIRPORT, a not insignicant willingness on my part because of a long ago acquired fear of public transportation. But my Dad wasn't doing well. So I cancelled and brought him his last McDonalds hot fudge sundae, and then he was gone. As it turns out, my brother and sister-in-law managed to food poison themselves on the Wednesday that I would have been returning from the conference (I was planning to stay with them), so I even managed to avoid that agony. So we here at OnCallDBA are thoroughly convinced that sacrificing the conference was the right way to go (this time, anyway).

With that said, though, I don't want you to think that the conference didn't go on without me. And there were some swell sessions, ones that were so good that they bear remarking upon. In fact, my reading of the presentation slides was so exciting that I did something I swore I would never do - I actually recommended a PANEL for another conference. Oh yes, get to know me and you'll find there are at least 83 things that I have, over the course of my life, concluded I do not like or want to recommend to anyone else. Panels hit the list one ill-fated year when I was asked to be on a panel at Collaborate. This was quite a while back, back when they had those big microphones that needed to be passed from person to person. There was this one fellow, whom I'll call "Mr. Microphone Hogger", and then I was next to him, and then there was this other fellow, whom we might just as well call "Not Close Enough to the Microphone". Mr. Microphone Hogger held onto that microphone and dazzled the audience with responses to their questions for an hour. Our Moderator apparently concluded that introducing us by name was the most important job he could do, and went mute after the introductions. Occasionally, I managed to pry Mr. Microphone Hogger's fingers off of the microphone long enough so that I could toss in a comment or two by bending the microphone toward me. And poor "Mr. Not Close Enough", well, he would have had to lean in front of me to snag that microphone, so he didn't get to say word one. And that was it, that was the year I concluded that panels were not for me.

So now you're wondering, "What panel could this panelphobic non-OOW-attending person possibly recommend?" Well, I've been working on the OAUG Connection Point 12.1 conferences, and I concluded that we needed a panel called: Best of OOW Panel - Four Presentations We Just Can't Stop Talking About. Oh, but it wasn't enough for me to throw a bunch of panelists in front of an audience and get them talking. Nope, the school marm in me came out (although I am not a school marm in real life, I am certainly an overbearing taskmaster), and I picked four people who attended OOW and had good things to say about the presentations, and I asked them to pick their favorite Oracle presentation, and then, just to make sure there was some real skin in the game, I asked them each to write an article about what they learned. Phew! The panelists are exhausted. They struggled to decide which presentation was their absolute favorite. They poured over the presentation slides, which, by the way, are available to EV-ERY-ONE this year, instead of just to people who attended OOW, and then they wrote. And wrote. I was actually a little stunned by how much effort my panelists put into their work. I was thinking maybe a couple of pages, but my treasured panelists got down to work and wrote thoughtfully and thoroughly about their topics.

So, with that said, the next tough job was putting together a newsletter that includes the articles. Here's a link:

In the meantime, the conference in question is OAUG Connection Point - EPM/BI/R12.1 Upgrade. It is coming up quick - November 15-16 in Atlanta, Georgia. And the agenda is AMAZING. If you're thinking about upgrading to Release 12.1, then you need to consider attending this conference. And if you can't make it to this one, the OAUG is working on another one in February in San Diego. Dates haven't been announced yet for that one. I had never attended these OAUG Connection Point conferences until the July 2011 Connection Point Release 12.1 Conference in Chicago, but they offer a terrific way to learn what you need to learn. The crowd is totally focused on the topics at hand, the speakers are some of the best in the business, and the conference is relatively short - 2  days - so you don't have to lose a whole week of work.

I forgot to tell you what the four presentations are, didn't I? Sorry about that. Here's the list:

Kaberi Nayak, Mitre: Prasad Akkiraju and Steven Chan’s Session 17249, Oracle E-Business Suite Technology Certification Primer and Roadmap

Alyssa Johnson, Solution Beacon: Cliff Godwin’s Session 16800, General Session: Oracle E-Business Suite - Vision, Strategy, and Roadmap

Bill Dunham, OATC: Sara Woodhull’s Session 17250, Upgrading Your Customizations to Oracle E-Business Suite 12.1

Mike Swing, TruTek: Nadia Bendjedou’s Session 16541, Coexistence of Oracle E-Business Suite and Fusion Applications: Technical Dive

If you decide to download presentations from OOW, just go to the OpenWorld Content Catalog. Once you find a presentation of interest, click on the pdf icon to its right.

Also, the OAUG plans to have all the Connection Point presentations available by conference time on the OAUG website, so you'll be able to find those as well.

OATM and the Release 12 Upgrade: OATM Caveats - Part III

I mentioned in another post that a client had run into difficulty migrating to OATM. So I figure it behooves me to mention that there are some caveats to bear in mind when you are planning your migration. My colleague Mike Swing from TruTek dug out some excellent points from assorted MOS notes.

1. TIMING: Oracle's Release 12.1.1 Upgrade Guide actually says: "We strongly recommend that you migrate the existing objects after the upgrade is complete. Use the Tablespace Migration Utility (introduced in Release 11i) to perform this task."

What? I thought the leaning was to migrate to OATM before the R12.1 upgrade, not after! My conclusion, after much nattering, is that there are pros and cons to either approach. Oracle was probably trying to make sure you actually do the migration, rather than provide a blanket recommendation that you do so after the upgrade. In fact, Mike Swing from TruTek dug out another note that said "We strongly recommend that you migrate the existing objects after the upgrade is complete. The reason for that is because the new products created with the R12 upgrade are created in the OATM model, but the old objects are still kept in the old tablespace model. This would keep the DB temporarily with a hybrid model, what is not recommended. Probably the best approach to avoid a longer downtime during the R12 upgrade is to migrate to OATM model before that." So I think we can reasonably conclude that no matter when you do the migration, the key is to do it and don't linger with a hybrid tablespace model.

I did ask Oracle, though I haven't gotten around to asking Oracle Support for an opinion, about OATM migration timing. Max Arderius, who is Oracle's Development Manager for the Applications Technology Group, said "There is no exact recommendation for this. It really depends on each environment and the needs for each customer. All I can say is that most customers choose to migrate to OATM before the upgrade so they can minimize the downtime window. Otherwise there is still another task that will need to be completed after the upgrade." Max makes a good point. You've just finished doing a huuuuge complicated upgrade, and before your DBA can run off to celebrate at Disneyland, another project with another downtime window will need to be done if you haven't already knocked the OATM migration off your list in advance.

2. MANDATORY-NESS: Is OATM Mandatory for Release 12?

MOS Doc. ID: 418225.1, "FAQ - OATM - Oracle Applications Tablespace Migration" says: Yes. OATM is mandatory for R12, although it is not mandatory to upgrade to OATM at the same time as the R12 upgrade, as stated at: (page 1-8)

2. TOOL FABULOSITY: OATM is neither psychic (please picture me saying that in my high-pitched voice, with just a bit of added emphasis on the first syllable) nor all encompassing

The following schemas will not be moved by OATM:
  • Custom schemas not registered with the Applications
  • CTXSYS, if not enabled for the migration. If you want to move the CTXSYS schema, please refer to MOS Doc. ID: 417742.1, OATM MIGRATION OF CTXSYS OBJECTS FAIL
  • There may be issues with CTXSYS, JTF and ABM schemas that require special fixes.
I mention this so that you'll be sure to check after you migrate to OATM to make sure that everything you expected to migrate actually has migrated. You may need to do additional research on Oracle Support to figure out how to move the rest.

NOTE: If you've upgraded to OATM after upgrading to Release 12, please satisfy my curiosity and let me know if there were any OATM-enabled patches for your existing modules that had to be changed to point to your existing tablespaces during the upgrade.

3. FASCINATING READING: MOS Notes About OATM For Your Review:
  • Oracle E-Business Suite Upgrade Guide Release 11i to 12.1.3 Part No. E16342-03, August 2010
  • Oracle Applications System Administrator's Guide - Configuration
  • Oracle Applications Concepts
  • Patch 3381489 - the Oracle Applications Tablespace Migration Utility patch
  • Oracle Applications Tablespace Model Release 11i - Tablespace Migration Utility [ID 248857.1]
  • How to run OATM migration utility [ID 404954.1]
  • OATM errors out when moving Intermedia indexes [ID 365466.1]
  • OATM migration leaves WF_CONTROL objects in the old tablespace [ID 418238.1]
  • Unable to drop tablespace APPLSYSD when migrating to OATM [ID 369526.1]
  • How to move objects left in the old tablespace after OATM migration? [ID 463271.1]
  • FAQ - OATM - Oracle Applications Tablespace Migration [ID 418225.1]
  • R12 : After OATM Migration, Some objects left in JTFD TABLESPACE [ID 839099.1]

OATM and the Release 12 Upgrade: When Should You Migrate? - Part II

In my last post, I concluded that Oracle E-Business Suite customers really must, at some point, migrate to OATM. Now the question is, when is the best time to do the migration?

Let's start with Mike Swing from TruTek's diagram of the Release 12 Upgrade:

You can see from the diagram that you can upgrade before or after.

The Case for Migrating BEFORE Upgrading to Release 12:

I'm a big advocate of cleaning up your environment before you upgrade. Purge what can be purged and deal with fragmentation before the upgrade, on the theory that the upgrade downtime window will be less on a consolidated environment.

I also believe that any upgrade steps that can be done before the upgrade should be considered, again, to lesson the downtime window. An additional benefit - the less work you need to do during your critical downtime window, the less likely you are to experience problems. No one wants to get partway through the production upgrade and then have to cancel it because of technical issues.

The potential tradeoff, of course, is whether your data after the upgrade ends up needing another round of cleanup. Another colleague, John Stouffer, an independent consultant that I work with on E-Business Suite Assessments, says that it is worth considering that side of the coin as well.

The Case for Migrating AFTER Upgrading to Release 12:

Your database has just been trounced upon by hundreds of thousands of programs. To what effect? I'll update this post when I've heard back from John Stouffer, as I'm hoping he has some insights. In the meantime, I'll also run queries against clients I've worked with who implemented OATM before the upgrade to R12. Those all included Assessments, so I should have the data on how fragmented they were before they started the upgrade, and whether their data is just as fragmented, more fragmented, or less fragmented afterward.

Practical Experience:
  • Mike Swing pointed out that for his Release 11i to Release 12 training classes, he always uses the Release 11i Vision instance provided by Oracle that is already migrated to OATM. He has never had a customer not migrate ahead of time. The migration is not a small task; it takes time, so if you tried to do it during your upgrade, it could add a significant amount of time to your upgrade process.
  • TruTek has one customer who migrated during their Release 12 upgrade because the original instance was running Release 11.5.7; migrating to OATM would have required upgrading first to Release 11.5.10. Ultimately, this client had to do their entire upgrade in one stretch, so they upgraded the database from RDBMS 9i to 10g, the applications from Release 11.5.7 to Release, migrated to OATM, then upgraded to Release 12.1.3+ with RDBMS 11gR2. The downtime for this upgrade for this customer with a relatively small data footprint, since they only have a few modules fully installed, ended up taking 6 days, with the OATM upgrade taking 6 hours. Lest you panic about the overall length of this upgrade, keep in mind that whether there's a lot of data or not, this client had to upgrade both their database and applications twice. While they might have shaved off some time by throwing hardware at the problem, with this much change, there's only so much you can do to speed up the overall timeframe.
  • Another of TruTek's clients told us that he migrated to OATM way in advance, when his company upgraded their Release 11i instance to RDBMS 10gR2. His migration did not go well - his test upgrades went flawlessly, but when he ran the migration for production, he encountered a number of issues. He commented that for his environment, the physical space saving was "pretty incredible", and offered to take a look after one of his R12 upgrade test runs to see how fragmented the database is after the upgrade.

OATM and the Release 12 Upgrade: Must You Migrate? - Part I

Last night I couldn't fall asleep. So I thumped on my husband's arm and insisted on telling him everything I learned this week about OATM. The man is a saint - he clearly understands the virtue of getting something off your chest, and so patiently listened to what I had to say. Of course, the remarkable thing about my husband is that he does not use Oracle, not in any way, so allowing me to interrupt his sleep with a discourse about OATM was truly above and beyond the call of duty.

The topic is still on my mind, though, so I'll cover it here, which seems like a more appropriate venue.

Let's start with the terminology: OATM is the Oracle Applications Tablespace Model. It's a framework for storing your data for the Oracle E-Business Suite of Applications. Oracle would like you to use their tablespace layout, and they've created a tool to help you migrate your data into those tablespaces. Back in the early days of the E-Business Suite, back before there were more than 200 unique modules, we kept our data in separate tablespaces, two for each module - one for the data and one for the indexes. There was something to be said for this model because back then we had small disks and plenty of them, so we DBAs enjoyed separating our data across as many spindles as possible. As time passed, Oracle introduced more and more modules, and so now managing more than 400 tablespaces isn't practical. Enter OATM.

As you plan your upgrade to Release 12.1... or Fusion... or Release 12.2, you should certainly be thinking about housekeeping. Yep, there's nothing like a full-on upgrade to make housekeeping rise to the top as something that ought to be done. As you prepare to upgrade, or even simply ponder upgrading, it's worthwhile to consider when you should migrate to OATM. I suppose you could also consider whether to migrate to OATM, but the pressure is on and Oracle expects you to get there at some point, at the very latest by the end of your upgrade to Release 12.1.3. If you decided not to do the migration, then I suppose the risk would be that there may be patches out there that assume you've migrated, so they'll fail when you try to apply them, searching for a tablespace that doesn't exist. This isn't the end of the world, but it'll stop you in your work temporarily while you create new tablespaces. Oracle does note that when they implement new modules during the upgrade, they'll place objects in OATM tablespaces. So when all is said and done, if you don't migrate to OATM before you upgrade to Release 12, you'll have a hybrid tablespace configuration with your existing tablespaces broken out by module, and twelve locally managed tablespaces for all products, including a temporary tablespace, a system tablespace, and undo segments until you migrate to OATM.

Now, there are virtues to migrating to OATM. According to Oracle, OATM:
  • Uses fewer and more consolidated tablespaces 
  • Uses Locally Managed Tablespaces
  • Accounts for the I/O characteristics of an object
  • Reclaims space after migration
  • Provides Real Application Cluster (RAC) Support
So I'm convinced. The move needs to be made. But what got me to stewing into the late hours is the question of timing. When is the best time to migrate to OATM - before the upgrade to R12, during the upgrade, or after the upgrade?

Let's look at a diagram from Mike Swing at TruTek:

I work with Mike on upgrades, so I reference this diagram constantly. You'll notice, he gives you the opportunity to convert to OATM twice - either before the upgrade or afterward. And, technically, if you do the conversion before the upgrade, the timing could occur as part of your upgrade. That got me to thinking: when is the best time to migrate to OATM? Is there any advantage to upgrading afterward? How about doing one big migration, complete with OATM, during the Release 12.1.3 upgrade? Are there ever cases when you might choose to do that? And finally, are there advantages to upgrading in advance?

I'll cover this topic in my next blog entry.