Tuesday, December 6, 2011

Did You Hear That?: ATG 12.1.3 Release Update Pack Required by Feb 2012 for Premier Support

I'm quoting my friend Floyd Teter, who is quoting Steven Chan from Oracle.

"ATG 12.1.3 Release Update Pack Required by Feb 2012 for Premier Support"

umm... It's December 6th. You're all getting ready for the holidays. Or your plant shutdown. Or your year end code freeze. And I just wanted to say, if you're on Release 12 and you're not on Release 12.1.3, then you need to get there. By February. And when I say February, I think that means January 31st. Not February 29th. You've probably been thinking you have until February 28th, but this year is a Leap Year (I am an expert on years in which the Leap occurs, because I only get anniversary gifts every four years). In spite of the extra day provided by Leap Year, the deadline for getting the ATG 12.1.3 Release Update Pack in is February 1, 2012. Which is, like, really soon.

To get you started down the path to R12.1.3, here are some tips:
  • First, be sure to read MOS Doc. ID: 1066312.1, Oracle E-Business Suite Applications Technology Readme for Release 12.1.3 (R12.ATG_PF.B.delta.3, Patch 8919491).
  • Make sure you're current on your RDBMS Interoperability Patches - the appropriate MOS Note will depend on which version of the RDBMS you are currently running. I see new patches for various RDBMS versions periodically, so don't skip this step. Here's the one if you're running RDBMS 11gR2: MOS Doc. ID: 1058763.1, Interoperability Notes Oracle E-Business Suite Release 12 with Oracle Database 11g Release 2 ( Of course, that said, I want you to upgrade to, but we'll talk about that later.
  • There's been a change to the steps for applying R12.1.3, Patch 8919491 - there's a new patch, 12964564, that Oracle says you should merge with Patch 8919491. If you're in the middle of testing and didn't notice this change, MOS Doc. ID: 1066312.1 says you can tack it on afterward as a post-upgrade step if you need to.
  • Don't forget the Post 12.1.3 patches from MOS Doc. ID: 1066312.1.
And then, just when you think you've got all the patches you could possibly apply, you need to apply more!
  • If you haven't done so already, apply a patch to Patch Wizard, a free tool provided by Oracle as part of Oracle Application Manager (OAM), which is also free. But Patch Wizard needs to be patched or you'll rip your hair out trying to figure out why it isn't working. See Patch Wizard Utility [ID 976188.1], Patch Wizard FAQ [ID 976688.1], Patch:10629916 for the latest version of Patch Wizard in Oracle E-Business Suite (Release 12.0), and Patch:10629956 for the latest version of Patch Wizard in Oracle E-Business Suite (Release 12.1).
  • Run a query to see what modules are either shared or installed in your environment.
  • Configure Patch Wizard (I swear, it's really easy; you just need to check boxes for the modules you have either shared or installed in your environment). If you configure Patch Wizard, then it will tell you just the patches you need for your modules. If you don't, then it will tell you every patch for every module that Oracle offers.
  • Run Patch Wizard. Sort the results to make "Unapplied" patches pop to the top, Copy the unapplied patches that are listed into a spreadsheet or Word document, read all the readmes looking for additional pre-requisite patches, post-patches, or extra steps. Update your spreadsheet. Apply the patches.
  • Consider patching your applications server while you're at it - there's an Application Server Release 3 (10.1.3) Patchset 5 ( Release 12.1.1 implemented There are several Forms fixes consolidated in bundle Patch 9593176.
  • Oh, go on, consider patching your RDBMS Version as well - Version is now certified. And if you do that, then make sure you check for additional Interoperability patches for Version with Release 12.1.3. And if you're going to do that, you might as well look for a higher PSU for Version 
  • We'll leave testing and applying the January 2012 CPU, due out in mid-January, for another day.
And BOOM, you're there. I've done this several times for an assortment of upgrades from 12.1.1 to 12.1.3, and I'm going to go out on a limb here and say that you'll probably have a lot of patches to apply (I'll say less than 200, but Release 12.1.3 counts as a pretty darned big patch), but they probably won't cause you issues in applying them. So, applying the patches will probably not be a bad experience, so be kind to your test team and get that part out of the way ASAP so they can have as much time as possible to test what you've given them.

You can download a free copy of Red River's Oracle E-Business Suite Release 11i & Release 12 Patching 101, which steps you through how to set up and use Patch Wizard. I have a vested interest in this book, as I wrote it with John Stouffer, and Mike Swing from TruTek provided the test environment for pounding through Patch Wizard issues. We'll release a new issue right before Collaborate 2012 to update the patch list and other modifications that Oracle has made since last year.

Shared HR Customers: Now That You've Made it to R12.1.3, Should You Upgrade to HR RUP4?

I am working right now with an E-Business Suite client who is a shared HR/Payroll user. My client is testing the upgrade from Release to Release 12.1.3+. If you've gone through the upgrade, you may sympathize when I say that every time I tell the client that there's one more patch to apply, I can actually hear his eyes rolling around in his head.

So, when you upgrade to Release 12.1.1, you upgrade to HR RUP3. And if you run patchsets.sh afterward, you'll notice that HR RUP4 is now available. And if you patch Patch Wizard and run Patch Wizard, it will tell you about some one-off patches for HR, as well as recommend that you apply HR RUP4. So here's the question: must you?

I did a little research. First, I have to tell you that I've become a My Oracle Support Known Issues Note Junkie. Oracle creates Known Issues documents to correspond to big patches that they've released. The Known Issues notes track outstanding issues and patches for those issues if they are available. And yes, there's a Known Issues Note for HR RUP3. So if you've upgraded to Release 12.1.3, you're already behind and need to take a look at the Known Issues Note. There's also a Known Issues Note for HR RUP4.

I trudged through those two Known Issues notes, and found that for HR RUP3, there are 55 additional recommended patches. That's as of 11/24/2011, the last time I looked. There may be more by now. And for HR RUP4, there were 24 additional recommended patches. Hmmm... 55 patches... 24 patches. I weeded through the Readmes for both MOS notes, tracked any additional pre-requisites, built myself a list for HR RUP3 and HR RUP4, waffled several times over what to do, and finally made this recommendation to my client:

Upgrade to HR RUP4, and apply the Known Issues patches for HR RUP4 that apply to your environment.

We weeded out, for example, patches that were legislative related. Shared users don't apply legislative patches. We weeded out patches for modules that weren't installed. By the time we were done, for this client, we were able to cut down to the HR RUP4 patch plus six additional patches from the HR RUP4 Known Issues document.

My client applied the patches and they all went in smoothly. We haven't tested yet, as he is sorting through the Patch Wizard-recommended patches today, but still, what we did apply applied smoothly. I can't guarantee that you'll be so lucky, since your data is different and you may have different shared and fully installed modules, but I did find it comforting to be able to say that for this client, applying HR RUP4 did not cause any issues during the application process.

Back to the original question: Must you upgrade to HR RUP4? Could you apply the HR RUP3 patches and let it go at that? Could you skip the extra HR RUP3 patches?

I think the answer to this kind of question always falls back to how risk averse you are. And maybe how solid your test team is. Oh, and it really matters if you're a fully installed HR/Payroll user or a shared installed HR/Payroll user.

If you have a super solid test team and they've thoroughly tested your R12.1.3 instance and they aren't seeing any issues, and if you've passed the HR RUP3 Known Issues patch list by them, and they don't believe that any of those patches are necessary, and if you're an HR/Payroll shared mode user, then you could probably stop where you are. Probably. Me, I'm so freaking risk averse that I would feel compelled to apply more patches. My concern is always that a change in a patch that is described in a readme will affect something that isn't described in the software that I care about.

On the other hand, my understanding is that if you are a fully installed HR/Payroll user, then you're supposed to stay current on patches, so you need to apply HR RUP4, and you need to follow the instructions that describe running hrglobal.drv for legislations, and you need to find the HR RUP4 Known Issues document and apply those patches, and you need to continue to monitor the HR RUP4 Known Issues document for more patches.

That said, I'm very interested in HR right now, so if you have any opinions, I'd love to hear from you.

psst... I found a MOS Note that describes how to switch from Fully Installed to Shared for HR

A couple of weeks ago I was trolling around on My Oracle Support, something I do quite frequently to stay current on the latest patches for Release 12.1.3, and I found a super-interesting MOS Note:

Changing Oracle Human Resources Installation from FULL HR/FULL Payroll Install to SHARED HR/SHARED Payroll Install [ID 461063.1]

Now that's exciting. Aren't you excited? If you're not familiar with HR, the issue here is that traditionally, Oracle has said that they wouldn't support changing the status of your HR module from fully installed to shared install. So seeing instructions on how to do so, and the caveats to consider, is just plain interesting.

The note describes two supported ways of making the change: 

1.  If you've never run the hrglobal.drv program against your instance, then it is very easy to switch from fully installed to shared installed. Customers in this situation likely inadvertently chose to fully install HR/PAYROLL when installing the E-Business Suite in the first place, but really had no intention of using the additional functionality or licensing those modules. Those customers correctly ignored instructions in various patches to run hrglobal.drv over the years, because they didn't need to run it. So, if you really intended to be a shared user, and you never ran hrglobal.drv, then MOS Note 461063.1 includes a script that will flip the switch on the STATUS column in the FND_PRODUCT_INSTALLATIONS table. MOS Note 461063.1 also includes a script you can run to see if you've run hrglobal.drv, so it's very easy to figure out if you're in this situation. The MOS note also includes a script to change the status of some other HR/Payroll-related modules.

Now, I couldn't resist checking at one of my clients who is already in shared mode, so they ran the query for me, and it turns out that they've run hrglobal.drv twice, once in 2006 and again in 2011. I mention this because to me this suggests that folks are confused about what they're supposed to be doing when it comes to HR. I looked over various MOS notes, and have to admit that I was confused. You have to understand what Oracle is talking about when they discuss legislations, and if you don't, then it would be easy to conclude that applying something you might not need is easier than not applying something you might need. Personally, I think it would be nice if any patch that includes hrglobal.drv also checks to see if HR/Payroll is fully installed, but I am not an Oracle programmer so the solution may be more involved than my simple perspective surmises. 

Anyway, the MOS document includes some more queries and changes that you need to make to fully apply these changes, so check it out if you're interested.

2. The more difficult scenario occurs if you have HR fully installed, you wish you hadn't, and you have run hrglobal.drv. I'm copying straight from the MOS note to avoid any misinterpretation here:

To be SUPPORTED as a SHARED HR Installed instance you will have to do the following:

3a. Create a 'brand new instance' installed as SHARED HR Install (also you will have to verify the STATUS for the other HR related products and change their STATUS appropriately (Oracle provides a list in the MOS Note))

3b. You will then need to export your table data from your FULL HR Install instance to your newly created SHARED HR Install instance where the hrglobal.drv script has 'never been run'. This means 'all of your table data for all Oracle applications'.

3c. This is the process for changing Oracle Human Resources Installation from FULL HR Install to SHARED HR Install where the hrglobal.drv script has been run (legislations ARE installed on your instance).

I have to admit, I'm utterly intrigued by this option, but concede I would be worried that I might accidentally include table data related to being fully installed in the import from the original Full HR instance described in Step 3b. But I'm betting that logging an SR with Oracle could result in enough information to do the deed. You can see that making this change is a significant undertaking, and your test team would definitely have to put your instance through a thorough test process. But it's nice to see that there is a way to get there if you want to.

With all that said, if you've followed this process, or come up with your own process for making this change, I would love to hear more details about it and whether you ran into any additional issues. Also, how did your test team test that the conversion worked? Do tell me. I'm all ears.

The Subtleties of CPU Application Between Releases

I've been plugging away on an R11.5.10 to R12.1.3 upgrade over the last couple of weeks, and ran into an issue. My client made it all the way in a test upgrade to Release 12.1.3 and then created a merged patch of some additional recommended patches, including the October 2011 CPU. And, well, it failed. Here's the message he saw:


sql /yyy/a/oraapp/xxx/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/afmimefilext.sql &un_fnd &pw_fnd

Start time for statement below is: Thu Dec 01 2011 15:36:49

INSERT INTO FND_MIME_TYPES ( mime_type_id, mime_type, ctx_format_code,
last_update_date, last_updated_by,creation_date, created_by,
last_update_login,file_ext, allow_file_upload ) VALUES (
fnd_mime_types_s.NEXTVAL, 'application/zip', 'IGNORE', sysdate, -1,
sysdate,-1, -1, 'zip', 'N')

AD Worker error:

The following ORACLE error:

ORA-00001: unique constraint (APPLSYS.FND_MIME_TYPES_U2) violated

With research, we concluded that the problem was occuring with the Oct 2011 CPU for Release 12, which includes two patches, 12794417 and 12921332. With more research, we realized that as part of the RUP7 "Known Issues" document, we had already applied the Oct 2011 CPU, but on Release 11i, rather than on Release 12. And with more research, we realized that Oracle gives the Release 11i CPU patch a different patch number than the Release 12 CPU.

Hmmph. I did not know that. I had been counting on adpatch to screen out duplicate patches between Release 11i and Release 12, so it hadn't occured to me to worry about whether the latest CPU had already been applied.

Our solution? For this pass of the upgrade (a test pass), we rolled back to before the post-Release 12.1.3 merged patch was applied, but after the Release 11i Oct 2011 CPU had been applied (because that occurred at the very beginning of the upgrade), took the Release 12 Oct 2011 CPU out of our merged patch, and then applied the merge again, with no problems.

Considerations? Well, for the next pass, we'll remove the Release 11i Oct 2011 CPU from our collection of patches that we apply to Release 11i before upgrading. We'll add the Release 12 Oct 2011 CPU back in to the merge that we ran after upgrading to Release 12.1.3. And for future upgrades, we'll make sure we're tracking which CPU has been applied on which E-Business Suite release more carefully, because it sure doesn't work to apply the CPU twice.

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: http://campaign.r20.constantcontact.com/render?llr=574eipcab&v=001bNaaDsMZRRdPhsclrMifR34aOZbF1jRdkqf7FF0c94_PLv8bau-OCevkzCCNaT-6kSoa6IFwWhTnuIFz6pLcdC3mhBECLXwTXzSpeW_2lm7DrUWLaDiI4MqXI8Plnj0veJ_MMHGJ7dtkjTga49iJJVVciqjr_uE4B-TK8vD3aU-Ad1JoQ5A0EMsiQ-0qMRgEn3odD5ua453P4z2S3Xb9kMA453iFo1--39BU5UGO2Z4i9uINe52R5H06lyA1ehflQNCJ7la_J6pKvsI8aJY2JryU6FircKnHlU0oDc_MaqAzTDjE2VI4Kxu1G_qrK9Hm_1b7TCpnYm6s3zvK5zhDaGvAkVJps301iPZa0lcE6ochIlQlO7eC2aj636aG4Qa0

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:

http://download.oracle.com/docs/cd/B40089_08/current/acrobat/r12upg11i.pdf (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.