Tuesday, November 9, 2010

Gobs of LOBS

All right, I’ll admit it. LOBS have been bothering me since the first time I saw them in the E-Business Suite. They’re big. Really big. And I’ve never been able to find out enough information about them. So every time I look at a client’s data, there they are, lurking, taking up all that space. But this month, I decided to poke around a little on My Oracle Support. Call it my very personal quest for information about LOBS, if you will. And the good news is, sometime within the last year or so, Oracle has provided a lot more information about LOBS. They’ve even provided scripts that provide some perspective on what’s going on with those LOBS. And they’ve even provided some suggestions for how to tame your LOBS.


MOS Doc. ID: 298698.1, "Avoiding abnormal growth of FND_LOBS table in Applications 11i" tells us, "Of all the tables that occupy a very large amount of space within the APPLSYSD and APPLSYSX tablespaces of Oracle Applications instances, FND_LOBS is usually one of the top 10. This is because it stores all the attachments that have been uploaded to Oracle Applications. There is a LOB field within this table called FILE_DATA, and the corresponding LOB segment (e.g., APPLSYS.SYS_IL0000680397C00004$$) is where the actual attachment data is stored, and it is usually very large. It is important that its size be controlled."

MOS Doc. ID: 829235.1, "FAQ - Performance considerations for FND_LOBS" describes LOBS further: "FND_LOBS stores information about all LOBs managed by the Generic File Manager (GFM). Each row includes the file identifier, name, content-type, and actual data. Each row also includes the dates the file was uploaded and when it will expire, the associated program name and tag, and the language and Oracle characterset. The file data, which is a binary LOB, is stored exactly as it is uploaded from a client browser, which means that no translation work is required during a download to make it HTTP compliant. Therefore uploads from non-browser sources will have to prepare the contents appropriately (for instance, separating lines with CRLF). The program_name and program_tag may be used by clients of the GFM for any purpose, such as striping, partitioning, or purging the table if the program is de-installed. They are otherwise strictly informative.

Some of the data that gets into this table belongs to old/expired exports. For every request for an export, an entry in the FND_LOBS table is recorded. This data must be purged regularly. There is a purge program available to purge this data called "Purge Obsolete Generic File Manager Data" (FNDGFMPR). FNDGFMPR is a PL/SQL procedure that deletes old obsolete uploaded files (loaded to the database) for the programs FND_HELP, export and FND_ATTACH, these are programs that are run under the FNDGFU (Generic File Manager Access Utility). MOS Doc. ID: 216541.1 describes how to add The Concurrent Program "Purge Obsolete Generic File Manager Data" To The Sysadmin User."

MOS Doc. ID: 1165208.1, "Questions on Purge Obsolete Generic File Manager Data" says that the tables affected by the Purge Obsolete Generic File Manager Data Concurrent Program are FND_LOBS and FND_LOB_ACCESS. And don't think that I'm the only one fussing about the size of that table: there are at least two enhancements requests asking for ways to more effectively purge the FND_LOBS table. For the PO module, for example, there are two enhancement requests for deleting attachments:
  • Bug 3899857 MASS DELETE PROGRAM FOR DELETING ATTACHMENT - Purchasing does not set an expiration date on the attachments. Also, there is no concurrent request available currently that deletes Purchasing attachments in bulk. Enhancement Request Bug 3899857 is already logged with Oracle Development requesting this functionality.
  • Bug 5676144 NEED A WAY TO CONTROL THE GROWTH OF PO ATTACHMENTS IN THE FND_LOBS TABLE - requests a functionality to allow purging PDF attachments created by the PO Output for Communication for approved purchase orders. Usually the rapid increase in table space is caused by large PDF files, generated because of using large sized files for the company logos (header or footer). As a workaround, one can consider using smaller sized files for the logos.
Progress is being made - the Order Entry module already has Concurrent Programs that purge from the FND_LOBS table:
  • Concurrent Program: OEXPURGS - Order Purge Selection
  • Concurrent Program: OEXPURGE - Order Purge
If you run on the conservative side and don't want to take a chance on inadvertently deleting attachments, the Purge Obsolete Generic File Manager Data program will let you choose what types of files you purge. In this example, we limited the file types to Exports:

We ran the queries included in the assorted MOS Documents and the "Purge Obsolete Generic File Manager Data" Concurrent Program against a client's database to see what we could conclude about their LOBS:


The Concurrent Program "Purge Obsolete Generic File Manager Data" will not purge entries for the Application Help (iHelp) and will only purge attachments or exports if they are expired. Oracle notes that the expiration of attachments should be done via an application, and not manually updating the table, so don't try to clean up the FND_LOBS table by using TOAD.

You can see entries that have an expiration date by program_name with the following query:

select program_name,count(*)
where expiration_date is not NULL
group by program_name;

-------------- ----------
export         7694 - related to Export, these will be purged

Recommendation: Schedule the “Purge Obsolete Generic File Manager Data” to run periodically. Then re-run the query above to make sure it has successfully removed the expired data.

Our client ran the purge and it deleted all of the exports and freed up about 125M of space within the FND_LOBS table.


Entries with no expiration date, which means they will not be purged, can be found by running this query:

select program_name,count(*)
where expiration_date is NULL
group by program_name;

---------------------- ----------
PER_WS1_gb_UK.pdf               1
PER_WS4_gb_UK.pdf               1
PAY_G52003_ar_SA.pdf            1
ES_company_cert.pdf             1
PER_WS5_gb_UK.pdf               1
FNDATTCH                    14948 - related to Attachments
PER_P11D_gb_UK.pdf              1
PER_SUMM_gb_UK.pdf              1
PER_ADDR_gb_UK.pdf              1
PER_WS6_gb_UK.pdf               1
PAY_G42003_ar_SA.pdf            1
PAY_G32003_ar_SA.pdf            1
FND_HELP                    59198 - related to iHelp
Oracle E Records                2
PER_WS3_gb_UK.pdf               1
PER_WS2_gb_UK.pdf               1

17 rows selected.

Oracle says that it is common to see rows with pdf and rtf files.

To find how much space is actually used by the lobsegments:

select sum(dbms_lob.getlength (FILE_DATA)) from FND_LOBS;


To find the total space allocated in the extents:

select sum(bytes), s.segment_name, s.segment_type
from dba_lobs l, dba_segments s
where s.segment_type = 'LOBSEGMENT'
and l.table_name = 'FND_LOBS'
and s.segment_name = l.segment_name
group by s.segment_name,s.segment_type;

----------    ------------------------- ------------------
6,040,576,000 SYS_LOB0000040605C00004$$ LOBSEGMENT

Thus, about a gigabyte of this client’s space is allocated but not used.

To find the space used by each program that will not be purged because the expiration_date field is null:

program_name,round(sum(dbms_lob.getlength (FILE_DATA))/1024/1024,0) "Size(M)"
from APPS.fnd_LOBS
where expiration_date is NULL
group by program_name order by 2 desc;

PROGRAM_NAME                     Size(M)
-------------------------------- ----------
FNDATTCH                               3825
FND_HELP                                279
ES_company_cert.pdf                       0
PER_WS5_gb_UK.pdf                         0
PER_P11D_gb_UK.pdf                        0
PER_SUMM_gb_UK.pdf                        0
PER_ADDR_gb_UK.pdf                        0
PER_WS6_gb_UK.pdf                         0
PAY_G42003_ar_SA.pdf                      0
PAY_G32003_ar_SA.pdf                      0
Oracle E Records                          0
PER_WS3_gb_UK.pdf                         0
PAY_G52003_ar_SA.pdf                      0
PER_WS4_gb_UK.pdf                         0
PER_WS2_gb_UK.pdf                         0
PER_WS1_gb_UK.pdf                         0

17 rows selected.

So about 4650M will not be purged.


MOS Doc. ID: 298698.1, "Avoiding abnormal growth of FND_LOBS table in Applications 11i" explains that "to maintain read consistency, Oracle creates new LOB page versions every time a lob changes. PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data. The PCTVERSION can be set to the percentage of LOB's that are occasionally updated.

The FND_LOBS table's FILE_DATA LOB column usually gets the data uploaded only once, but it is read multiple times. Hence, it is not necessary to keep older versions of LOB data. It is recommended that this value be changed to "0". By default PCTVERSION is set to 10%. It must be set to 0% explicitly. The value can be changed any time in a running system."

You can use following SQL to find the current PCTVERSION value :

from dba_lobs

------------------------- ----------
SYS_LOB0000040605C00004$$ 10

Recommendation: Set PCTVERSION to 0 using the command: alter table applsys.fnd_lobs modify lob (file_data) (pctversion 0);


To help manage the size of the FND_LOBS table, Oracle’s main recommendation is to set PCTVERSION to 0 and use a reasonably sized next_extent. We checked the LOBS tables for our client, and they had very few extents, so the tables are probably sized adequately.

Oracle also notes that "if using locally managed tablespaces, then the number of extents is not as much of a problem as it was in the past with dictionary managed tablespaces. Since the LOB segments are usually very large, they are treated differently from other columns. While other columns can be guaranteed to give consistent reads, these columns are not. This is because it is difficult to manage LOB data rollback segments due to their size, unlike other columns. So they do not use rollback segments. Usually only one copy exists, so the queries reading that column may not get consistent reads while other queries modify them. In these cases, the other queries will get "ORA-22924 snapshot too old" errors."

To see the approximate size of the extent:

min(round(dbms_lob.getlength (FILE_DATA)/1024,0)) "Min Size(K)",
round(avg(round(dbms_lob.getlength (FILE_DATA)/1024,0)),0) "Avg Size(K)",
max(round(dbms_lob.getlength (FILE_DATA)/1024,0)) "Max Size(K)"
from APPS.fnd_LOBS;

Min Size(K) Avg Size(K) Max Size(K)
----------- ----------- -----------
0                    59        1173

Oracle says that there is no "magic" number for the extent size and a compromised setting should be used, observing the following: 
  • Extent should not be too small, to avoid constant extent allocation (which would cause HW enqueues in tables with LOBS)
  • Extents should not be too big, to avoid wasted space
  • Extents size should be bigger than the majority of the size of one lob segments  

The following script from MOS Doc. ID: 963222.1. “Orphaned records in FND_LOBS table when uploading attachments using FNDATTACH form” can be used to check if you have any orphaned records in the FND_LOBS table that have been inserted through the FNDATTACH form.



These 447 orphans are caused by a code bug in file (FNDATTCH.pld) which allows the attachment to be loaded into the FND_LOBS table even if the user does not confirm that the upload completed successfully. To implement the solution, execute the following steps:

1. To prevent creation of orphaned records in FND_LOBS going forward, download and review the readme and pre-requisites for:

Release 11i Customers:


Release 12 Customers:


2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. Retest the issue.
5. Migrate the solution as appropriate to other environments.
6. In order to get a data fix to delete orphaned records in the FND_LOBS table that have been inserted using the FNDATTACH form , log a Service Request with Oracle Support.

Recommendation: Apply Patch 9004099 and then log an SR with Oracle Support to get help with cleaning up the orphaned records.


Once you have addressed the records with expired data by running the “Purge Obsolete Generic File Management Data” Concurrent Program, and changed the PCTVERSION to 0, and adjusted the NEXT_EXTENT value to stop the overly aggressive growth of the allocated space, then it will be worthwhile to see if there is a way to reclaim unused space. Read the documentation very carefully and test very thoroughly, though, as these notes describe making substantial changes to the database. 

If there is a lot of DML activity in the FND_LOB table, some space may be reclaimed by moving the table to another tablespace and then moving back to the original tablespace. Refer to:

MOS Note: 303709.1 “Reclaiming unused space in APPLSYSD tablespace”
MOS Note: 130814.1 "How to move LOB Data to Another Tablespace"

For additional information, refer to:

MOS Note: 118531.1 How to Compute the Size of a Table containing Outline CLOBs and BLOBs
MOS Note: 386341.1 How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM


It is also possible to limit the size of attachments. The profile option 'Upload File Size Limit' can be used to limit the size of uploaded attachments.

Recommendation: Research file size limits by reviewing the following MOS Notes:

MOS Note: 605377.1 How To Set A Maximum File Size For Attachments?
MOS Note: 604458.1 How to Limit The Attachment File Size?

Release 11i Extended Support Patching - You'd Better Watch Out! - by Barbara Matthews

Perhaps you've been seeing the mailnotes, newsletter articles, and even news articles about Oracle's Mandatory Extended Support Patching Requirements. Oracle announced this change in January, 2010, so customers have had quite a while to plan out what they need to do. Perhaps you're thinking "Fah! My company is pretty current, we shouldn't have a problem." Oh, if only that were true. I thought I'd walk you through two case studies of what I've seen, along with a how you can check your status.

To Learn More

If you haven't done so already, please fill out the OAUG's Extended Support questionnaire by November 30th:


They'd like to be your advocate, so understanding what Oracle's E-Business Suite customers' status is would help them in discussing issues with Oracle. Also, I'd love to hear more from customers about what they're doing. If you've applied patches, tell me how long it took, how many patches you had to apply, and if you hit any surprises. And if you're still deciding what to do, tell me about that too!

If you'd like to hear more about the Extended Support patches, John Stouffer, who is on the OAUG Board of Directors, will be giving two webinars titled:

What you need to know about Release 11i Extended Support Patching including Tips and Q & A

You can register at::

November 22nd at 1pm EST
November 23rd at 1pm EST

Don't miss the opportunity to ask questions and relate your own issues and concerns.

To Check Your Company's Extended Support Status

Let's start with the infamous My Oracle Support Doc. ID: 883202.1. In a nutshell, it says:

To be eligible for Extended Support of 11.5.10, which begins on December 1, 2010, your production E-Business Suite environment must be patched to the patch levels indicated in the table under Section 1, requirements 1 through 6. Also, you must have the patches in Section 2 applied. Section 2 includes a large number of patches for a large number of E-Business Suite modules. You have to apply patches to any module that you have installed, shared, or that is a pseudo module. Also, as we get closer to December 1st, My Oracle Support Doc. ID: 883202.1 may be updated with additional patches. So even if you've gone through this drill, you need to keep checking.

Well that's pretty simple. You might wonder what the fuss is all about, and the answer is three-fold. First, these requirements are mandatory. Oracle has traditionally steered clear of making mandatory requirements, so it's a big deal when they introduce one. And second, this mandatory requirement has dozens of patches that might need to be applied. Those patches will require thorough testing before you can move them into production. Even if you've made it past Section 1 of Doc. ID: 883202.1, you'll likely still have a couple of dozen patches to apply. Last of all, the date: December 1, 2010. It's just around the corner. You'll need to figure out what patches need to be applied, find all the pre-requisites, apply them to a test environment, test them, and then apply them to a production environment. Before year end.

Now, how do you figure out what you might have to patch?

The Manual Way

First, run patchsets.sh. At the top, there are a list of modules that you use. Print the list out. Print 883202.1 out. Get out your highlighter. Highlight all the patches in Section 2 that match up to the modules in your patchsets.sh report - whether installed, shared or pseudo.

The Automated Way

You can use the Patch Wizard to tell you what you need to patch, but you might have to patch Patch Wizard to make that work:

Patch 9803629 includes the following pre-requisite patches: 4125550, 3036401, 3264818, 3263588, 3219567, 3264822, 3263645, 3261254, 3262486, 2614213, 3261243, 4038964, , 3262159, 3412795, 3140000

If you're relatively current on the E-Business Suite, you've probably got many of these patches applied already. Go back to patchsets.sh and take a look there. You'll need to get out your highlighter and highlight those cases where the Running Version column is lower than the Latest Available column. Then compare the highlighted patches in patchsets.sh to this list of pre-requisite patches for Patch 9803629 and see how you're doing.

Next Step

By now you've got a list of patches that you need to apply. If you want to use Patch Wizard - and yes, you should want to use Patch Wizard, because once you've gone through MOS Doc. ID: 883202.1 with a highlighter once, you'll never want to do that again - then you'll need to get organized and plan out those patches. One issue with MOS Doc. ID: 883202.1 is that you've got a list of patches, but guess what - you don't really think those patches won't have pre-requisite patches, do you? So getting Patch Wizard working so it will tell you pre-requisite patches is another great reason for applying all those patches to Patch Wizard.

Following are two examples of client's results. You may be surprised to hear that the client who was patched most current - having all the items in Section 1 up to date - had the most patches to apply.

EXAMPLE 1: We’ll start with a client who is way behind on their patching. They applied Release with CU2 in 2006 and haven’t had much time to patch since then. They’re still using JInitiator, and they’re on RDBMS

From the top part of the patchset.sh Report_11i.txt file:

Limited Report to: APPLFULL and APPLSHAR products




Now let’s look at how far behind patchsets.sh shows this customer. The highlighted modules are ones where there are higher versions available:

Patching Patch Wizard

Now let’s start with what it will take to get Patch Wizard patched for this client:

4125550 - 11.5.10 CU2 for ATG Product family - OK
3036401 - Mini-Pack 11i.HZ.L – OK, on N
3264818 - Patch 11i.UMX.H – OK, on H
3263588 - Patch 11i.XDO.H – not seeing XDO, see below Patch 3412795
3219567 - Patch 11i.TXK.B Technology Stack Minipack B (also in 11.5.10)- OK, on B
3264822 - Patch 11i.CAC.B – OK, on C
3263645 - Patch 11i.AK.G – OK, on G
3261254 - Patch 11i.ALR.G – OK, on G
3262486 - 11i.JTA.F – OK, on F
2614213 - AME PATCH :DELIVERY OF GA AND RULE PRIORITY FUNCTIONALITY – will need to check if this is applied
3261243 - Patch 11i.EC.G – OK, on G
4038964 - Minipack 11i.AD.I.1 – OK, on AD.I.4
3262159 - Patch 11i.FND.H – OK, on H
3412795 - ADSPLICE PATCH FOR XDO – not seeing XDO, so need to apply this
3140000 - Oracle Applications Release 11.5.10 – OK!

So the good news is, since this client is on Release CU2, they’ve got almost everything that they need to make Patch Wizard work. They’ll have to check into the AME Patch 2614213 and apply Patch 3412795 and Patch 3263588 to get XDO going.

Reviewing Section 1 in 883202.1

These are the seven requirements in Section 1, and our client has issues with the yellow highlighted ones:

Whew! That’s a lot of patching! I’m not going to list the patches from Section 2, just suffice it to say that there are 37 additional patches, and those patches likely have pre-requisites. So, if you were this customer, what do you think? Could you upgrade to RUP 7, upgrade your database to at least,upgrade to Forms6i Patchset 19, upgrade your users’ from JInitiator to JRE and apply at least 37 additional patches by December 1st? That’s the challenge!

Example 2: A client who is much more current on their patches

For this example, the client is running RDBMS Version 10.2.04, and they’ve applied ATG RUP 7, which brought them up to date on a lot of installed, shared and pseudo modules. They’ll need to check if Patch 2614213 - AME PATCH :DELIVERY OF GA AND RULE PRIORITY FUNCTIONALITY is applied, but otherwise they’re set to apply the Patch Wizard Patch 9803629. Once they apply Patch 9803629, they can double check the list of patches identified, including pre-requisites, and begin planning their test cycle.

Limited Report to: APPLFULL and APPLSHAR products




This client passed the Section 1 requirements in MOS Doc. ID: 883202.1 with flying colors:

Section 2, on the other hand, presented quite a few patches:

There are at least 44 installed, shared, and pseudo modules in this client’s production environment that need patches tested and applied, with at least 67 patches to be applied. The reason this client has more patches to apply from Section 2 is that they have more modules licensed.

This client has decided to apply the extra patches, and is in the middle of a patch current exercise right now. So far, they report that they haven’t hit a lot of problems, but other clients note that applying patches to certain modules has required extensive additional research and time, so your mileage may vary. Also, this client says that there is no way they can get all of these patches tested and installed by December 1st, 2010.

Wednesday, October 13, 2010

OOW Blogging: The Older I Get, The More Behinder I Get

The last time I went to Oracle OpenWorld was, I think, the first year that Oracle called this huge conference Oracle OpenWorld. So, I'm thinking, maybe more than 10 years ago. I hated it. There were a kajillion people thronging around, and every session that I tried to go to was filled past capacity. So, I spent most of my time sitting on the floor outside of rooms, contemplating my database, thankful to at least be away from it, but a little put out.

So I have to say, I never wanted to go back.

But this year, I got a bee in my bonnet and decided that I wanted to see hands on demonstrations of some of Oracle's products. So I cast about for a way to attend, and got permission to attend as an Official Oracle Blogger. So first and foremost, let me just say that I appreciated the opportunity very much. Thank you to Oracle and to Larry Ellison. I wouldn't have been able to come without the free pass.

So now I'm writhing with guilt, because I didn't blog. I didn't blog about what I intended to attend before the conference started. And I didn't blog about what I was seeing while I was there. And I didn't blog about what I saw afterwards. Well, now I'm blogging.

I have many excuses, of course. Before the conference, I was working very hard on preparing for the conference (getting all my work done). And during the conference, I was staying in Oakland, so I had to ride the BART every day, which is a big deal for someone who lives in the countryside and never rides these modern forms of transportation. And then there were the cats. Yes, I stayed with my brother and sister-in-law, and so all my non-blogging during the conference was their fault. They have these three cats, who loved me very much. And I forgot to prepare for the cats, so I arrived without a bag full of antihistimine. So one day, I fell asleep on the couch, when I should have been blogging, and one of the cats decided to hunker down with me, and that was the end of any blogging potential during the conference.

On the way home from the conference, the influence of the cats was so great that the man sitting next to me on the plane offered to pray for me, as I was clearly in distress, with my head in my hands, and my hands full of Kleenex. I took him up on it, and afterward he asked if it had helped. And I had to say, "Err... no, not really, but I thank you very much anyway, because it certainly didn't hurt."

For two + weeks after the conference, I did my level best to hack up an extra cat for my brother and sister-in-law. Finally, I am still coughing, but "not so much". My mother has skipped church for two solid weeks because she doesn't want people to worry that we will give them tuberculosis (she has a wee chronic cough herself). When I cough, I sound like a seal, or maybe a donkey braying. You cannot help but feel sorry for me.

Before I move on to my official recounting of what I learned, I wanted to say that the conference itself, in terms of organization, is sooooo much better than it was the last time I attended 10+ years ago. Attendees are strongly encouraged to sign up for sessions ahead of time. So I carefully went through the list of presentations and signed up for all the hands-on sessions I could find. And, although there were tens of thousands of people at the conference, I never had that standing room only feeling, so somebody must have handled the room selection very carefully. I was not overly fond of all the walking I had to do, but it's hard to complain, since walking from Moscone South to Moscone West to the Marriott meant there was a vast quantity of sessions. There wasn't any time when I couldn't find something interesting to attend. And, as for the walking, I needed it, and once I had proper walking shoes (courtesy of my sister-in-law), I managed just fine.

So, if you're wondering if it is worth it to attend OOW, in my opinion, it definitely is. I still like the smaller Collaborate conference better, but that's primarily because my focus is on the E-Business Suite of Applications, while OOW has a much broader focus. But if Larry invited me back next year, I'd go without hesitation.

So, Larry, thank you again for letting me attend your conference. It was a wonderful experience, I learned lots of things, and now I'm going to blog about what I learned.

Monday, May 10, 2010

Collaborate 10 OAUG Upgrade SIG Meeting by Barbara Matthews

The OAUG Upgrade SIG hosted a meeting at Collaborate 10 to discuss upgrade issues from E-Business Suite Release 11i to Release 12.

The Panel

- John Stouffer, Independent Consultant, Past Chair of the Upgrade SIG, Moderator
- Lester Gutierrez, Oracle EBS Performance Group, works on upgrades and reducing downtime
- Udayn Parvate, EBS Release Management Team, works on building and packaging the E-Business Suite software. Udayn performs installation and upgrade activities, defines and enforces standards for packaging and delivery with development teams. He works to get upgrade issues resolved so E-Business Suite customers' experience is as smooth as possible.
- Sandra Vucinic, Vlad Group, Inc., Chair of the Upgrade SIG
- Floyd Teter, Jet Propulsion Labs, functional guy on the panel
- Steven Chan, Director, Applications Technology Integration, Oracle Corporation
- Michael Rulf, Executive Director - Product Development, AT&T Hosting and Application Services (H&AS)

The Questions and Answers

We are going to upgrade from Release 11.0.3 to Release 12. We are currently running on Sun Solaris and will switch to a Linux Intel environment. Our architecture team is wondering how to do performance load simulation so we know how much hardware we need to buy to run the upgraded E-Business Suite environment. Our database is 700 gigabytes, and we have 2500 users.

- First, you need to get a sense of how many concurrent users you have, and what those users are doing. While you may have a total of 2500 users, those users probably aren't all logged on at the same time, and they likely aren't submitting data and hitting commit at the same time. The limits will not be software, but will be hardware limitations based on the degree of throughput, and read and write operations per second. The architecture team needs to understand the product mix (modules), the read/write workload, and the concurrent manager workload, and then bring in the hardware vendor to help build out a test environment. You can also use load simulation software like Mercury Interactive. Oracle has a Test Starter Kit (see http://blogs.oracle.com/stevenChan/2010/04/ebs_1211_tsk.html) for E-Business Suite Release 12.1.1 that can be helpful, but you need to choose carefully when you put together your test environment.

- As you purchase your hardware, make sure you are positioned to be scalable, so you have room to grow if you need to. You might find, for example, that when you do the first close on the new system, you need to add more hardware to deal with the extra load. If you can move to parallel concurrent processing, you can add another node easily. Given the size of your environment, you should research and understand the options available for using load balancing and shared appl tiers (see Parallel Concurrent Processing Failover and Load Balancing of E-Business Suite Release 11i and Release 12 by Mike Swing at TruTek).

- As far as hardware is concerned, we are starting to see where the Intel world is supporting more processor cores, and the hardware is hot pluggable. Be careful not to skimp on the chassis, but populate it with the number of processors you want. We're about to see a big jump in the industry from 4 core to 8 core. You should try to pick a hardware solution that allows you to make that move if you need to. Note that there's a tradeoff to buying more processing power than you need - if you overload your processor, your software fees will go up, so you have to strike a balance.

We upgraded to release 12.0.4 and saw a 25-30% increase in database size. Is there a similar increase with the 12.0.4 to 12.1.2 upgrade?

TCA, SLA, and E-Business Tax added a lot to the database size, but you've already bit the bullet on those modules, so no, you shouldn't see a significant increase.

We are currently running Release Is there a recommended upgrade path?

You should upgrade to RDBMS Version 11gR2 and then Release 12.1.1, and then apply the 12.1.2 RUP. We've "borrowed" a wonderful diagram from Steven Chan's blog of what your E-Business Suite Upgrade Roadmap is:

Is there a plan to offer a gui screen for the Mobile Supply Chain module? Currently it uses a telnet session.

It is a high priority with the User Experience team, but we aren't sure when it will be released. The Release 11.5.10 gui version, according to someone in the audience, was too slow, so it wasn't adopted by many users.

Do people typically upgrade or reimplement?

Upgrading is less expensive, and much better supported by Oracle. Because of the nature of reimplementations, the path isn't as clear because we all have different reasons for doing it. It is much more difficult to determine what post upgrade patches to apply, and how to do validation testing. Reimplementation customers will have to blaze their own trail.

Is bifurcation still recommended?

For Release 12, the new term is Upgrade by Request. Upgrade by Request allows you to upgrade part of your data during the upgrade, and then upgrade the rest of your data after your upgrade completes. Modules that can use Upgrade by Request include Financials and Procurement, projects, Supply Chain Management, and CRM. You can read more about Upgrade by Request in Appendix G of the Release 12 Upgrade Manual.

Are there any recommendations for preparing for a Release upgrade to Release 12.1.2?

- See Sandra Vucinic's presentation, Get Ready for EBS Release 12.1! Tasks to Complete Now to Ease R12.1 Upgrade Process. Here's a list of tasks from her presentation:

- Upgrade database to 11gR1 or 11gR2
- Convert tablespaces to OATM model
- Evaluate impact of R12.1 on customizations and extensions
- Introduce BI (XML) Publisher and JDeveloper
- Introduce Web ADI and Report Manager
- Archive and purge EBS 11i data
- Convert from JInitiator to Native Sun JRE - _21 is recommended
- Integrate Discoverer Server Release 10g with EBS 11i - use Discoverer 11g if possible as Discoverer 10g is desupported at the end of 2010
- Configure Oracle iAS Release 10g for external apps (SSO, OID, Portal) and integrate with EBS 11i
- Position for high availability and scalability
- Evaluate and complete platform change based on ROI

- RDBMS 11gR2 is a major architectural change in the nature of the cluster software. DBAs need time to understand the fairly steep learning curve. The many new features may help justify doing the database upgrade separately from the E-Business Suite upgrade so DBAs can come up to speed on the technology.

- In terms of testing, have you catalogued all of your customizations? How are you going to train your user community? What tool will you use to develop training guides?

- Read the 11gR2 Upgrade Companion, and be sure to stay current throughout your upgrade on new patches. There's already a RDBMS Version patch which fixes a lot of issues.

A user related his story of upgrading from Release to Release 12.1.2. During the upgrade the patch stuck on a program, they waited a day, and got a pre-upgrade patch that needed to be applied before starting the upgrade. He noted how frustrating this was, and asked if there is any My Oracle Support note that tells all performance and serious functional issues in the upgrade.

The official My Oracle Support documents are the Release 12 Upgrade Guide, the Release Notes, and the NLS Release Notes. When Oracle encounters issues, they update the Release Notes. They may also release additional patches, including Critical Update Patches (CUP), which include a consolidation of fixes. Users must actively monitor My Oracle Support for additional patches and alerts while upgrading. Another option is to look at the Release 12 Forum, a very active, live, real time forum. Often solutions can be found there before logging a problem in My Oracle Support. Check out the forum at: http://forums.oracle.com/forums/forum.jspa?forumID=395&start=0

How close are we to desupport on Release

Premier Support ends at the end of November, 2010. The extra cost for Extended Support is waived until November, 2011. Extended Support ends in November 2013. Premier support is what you have today, and includes certifications with Oracle products and other products. If a new MS Windows client is released, with Premium Support, Oracle will certify to that. Extended Support, however, will not include third party certifications, so if a new service pack for Windows 7 is released after November, 2010, Oracle is not bound to certify to that service pack. Odds are, they'll give it a try, but they are not likely to produce new patches. The emphasis at Oracle once Extended Support kicks in for Release will be on Release 12.

This point is very important - E-Business Suite customers use a variety of other products besides the E-Business Suite on their computers. Once Extended Support starts, customers may find themselves needing to upgrade because of some other product that they use, but unable to do so because of certification issues with Windows or JRE or other products.

When you move to R12, JInitiator is not certified and won't work on Windows 7 or Vista. You will need to migrate to the native SUN JRE client. Don't have to worry about java conflicts between applications.

I am new to an organization that uses grants, contracts, and projects. In the past, these were considered outliers that kept us from going to the latest release. Will these modules impact our ability to go to Release 12?

Panel members pointed to several companies using those modules who are upgrading, and said they have not hit any big issues.

An audience member said that she had put together a plan for her company based on what she has heard at the conference and wanted to make sure they were heading in the right direction. They are running RDBMS Version with Release, they are already on JRE, they have Discoverer and ADI/GL already implemented with the web, and are moving to RDBMS 11g using OATM with a 2 node shared $APPL_TOP and the latest Release 12 (currently 12.1.2). Is that where her company should be heading?

The panel agreed, her approach is correct.

What features of RDBMS Version 11gR2 would be useful to the E-Business Suite Applications?

You can save a baseline of your production RDBMS 10g performance, and 11gR2 will tell you that your current execution plan is fine, or that you need to run a different plan. You won't lose any performance by going to 11g. This feature is built into 11g. If you run into a performance problem, you can fall back to the 10g plan to get immediate relief while working with Oracle Support to get a more permanent fix.


All in all, it was a fun meeting with lots of questions and interesting answers. As one might expect, the OAUG Upgrade SIG strongly urges you, if you haven't done so already, to get ready to upgrade to Release 12. There are exciting features in RDBMS 11gR2 that will benefit the E-Business Suite Applications even if you don't upgrade to Release 12. The path to upgrading is well-laid out and tested, and the software is now very stable. If you have questions about the upgrade, feel free to contact the OAUG Upgrade SIG. Just drop a note to barb at oncalldba dot com

Collaborate 10 OAUG Database SIG Meeting by Barbara Matthews

The OAUG Database SIG held a lively meeting at Collaborate 10 in Las Vegas. Steven Chan, Senior Director in Oracle's Applications Technology Group, spoke at length about the nuances of Oracle's Lifetime Support policy. You can (and should!) read the details about Lifetime Support at http://www.oracle.com/support/lifetime-support-policy.html.

Understanding just what is included with Extended Support and Sustaining Support, and at what price, is important for customers to understand. You can also sign up for My Oracle Support notifications to receive automatic emails when the LSDs (Lifetime Support Documents) change.

Steven explained that E-Business Suite Release 11.5.10 Premier Support lasts six years from November 2004. That means that at the end of November, 2010 - that's right, this year - Premium Support ends. To stay supported on Release 11.5.10 after that, you'll have to pay a premium on support costs to run in "Extended Support". Steven strongly recommended not running in production in Extended Support unless you really need to. He described Extended Support as a dangerous place to be.

And here's where the nuances come in. With Extended Support, you can still log a P1 problem - but there's no guarantee that the resolution will come quickly - it might take months. Even within Premium Support, Oracle supports only the current and previous database releases for 12 months after the current database has been released. That's a subtle point that could cause big issues for customers - if you are running RDBMS, you're supported for only 1 year now that 11gR2 is available. If you are running RDBMS, thinking you are supported based on what you read on the support page, you aren't - that support stopped in February, 2009. These policies override the E-Business Suite support agreements.

One technical issue brought up at the meeting concerned Sun JRE. Sun JRE is required on Windows desktops for users to access the Applications. Up until JRE 1.6.0_17, the software worked fine. However, Sun introduced changes that broke session management and ordering with JRE 1.6.0_18. The recommendation at the meeting was that if you were still on JRE 1.6.0_17, you should not apply versions 18, 19 or 20, because they all have significant issues for E-Business Suite users. With automatic updates on PCs, you can imagine the issues that this might cause. Also, certain customers, particularly government customers, are required to stay current, so those customers had no choice but to upgrade to the latest version of JRE, even though it had these two major bugs.

Now add one more point - JRE 1.6.0_20 was released to correct a significant security issue that occurred between JRE 1.6.0_17 and JRE 1.6.0_20, which means that if you're on JRE 1.6.0_17, you're ok, but if you are on JRE 1.6.0_18 or JRE 1.6.0_19, you need JRE 1.6.0_21. For military organizations that are required to stay within a certain number of releases of the most current, they may have no choice but to upgrade. Steven commented that while this issue was going on, there really was no reassuring place for an E-Business Suite administrator to be right now.

Mark Farnham suggested that if you're a non-DOD compliant organization with pretty good campus-wide security, and you're a low enough target value, then you can wait for JRE 1.6.0_21, which is expected shortly. The PCI (credit card) industry has a similar issue to military organizations. They have to apply all vendor security patches within 30 days of their release. The good news, according to Mark, is that the really mercenary hackers aren't interested in most organizations.

The recommendation, therefore, is that as soon as a release becomes available that doesn't have the functionality issues, all E-Business Suite users should seriously consider moving to that release. You can read more about this issue on Steven Chan's blog at:


Another issue with versions comes with third party vendors. If third party vendors don't stay current on JRE versions, then users may find themselves working with releases that have bugs or security issues. Agile and Siebel were two examples of vendors with this issue. Steven said that the only thing customers can do in this case is apply pressure on the third party vendors. The advantage of staying current with JRE, for example, is that each new release brings new memory management capabilities, better security, and bug fixes, so, with the exception of the temporary issues with JRE 1.6.0_18 - 1.6.0_20, staying current is the way to go.

Yet another example of the need to understand the nuances of Oracle support agreements lies with the 9i Application Server used with Release 11i and the 10g Application Server used with Release 12 applications. The double edge to that sword is that Application Server 10g has its own application lifecycle, and the Application Server 10g obsolescence life cycle overrides the E-Business Suite life cycle.

One of the questions from the group was what Oracle is doing to make this situation better. Steven said that Oracle is testing better and more, taking more time to test and running more tests. Now that Sun is integrated with Oracle, there's a better chance of working better together. The Oracle team now gets early shipments of Application 10g Patchset 3, and similarly will get early builds of JRE releases at some point.

One very strong point that Steven highlighted is that Oracle can only go so far in their testing. He strongly recommended that if you are testing a combination of software that isn't documented by Oracle, then it is your job to test heavily, as there are too many possible combinations for Oracle to test.

Randy Giefer, from Solution Beacon, asked if Sun Java might merge into Oracle's CPU cycle. Steven said that while this was a nice idea, he wasn't sure it would ever happen, as we can't really afford to wait for up to 3 months for new changes. He also said that the JRE crowd marches to a different beat because of military requirements.

Also covered in this meeting was the need to stay current with Oracle's quarterly CPUs (Critical Patch Updates). Oracle has improved their CPU process by making the CPUs cumulative as of January 2010 for the Applications users. Where in the past, if you wanted to patch current you would have had to apply each CPU, now when you apply the latest CPU - currently the April 2010 CPU - you're set and don't need to apply any others until the next cumulative quarterly CPU comes out in July 2010.

One additional recommendation discussed was the need to periodically review your company's Master License Agreement. Customers need to be aware that the Oracle Account Manager can't authorize anything that flies contrary to the Master License Agreement. An example - implementing custom objects can eliminate a customer's ability to run using a runtime license. One subtlety, however, is that if Oracle directions say that you have to create an object (there are instructions, for example, that tell you to create custom indices for some of your GL objects), then you are ok and are not violating your E-Business Suite license.

Customers have run into similar issues with partitioning. If you are a licensed EBS customer and Oracle introduces a new product requirement, they can't force you to buy a new license. This caveat is driven by mandatory upgrades of the E-Business Suite that would otherwise have forced you to upgrade the technology. On a new license, for example, customers have to buy the partitioning option when licensing the database, because the E-Business Suite uses partitioning. Customers that upgrade, however, don't have to buy the partitioning software as long as they don't partition any data themselves.

Steven mentioned one area with exciting upcoming changes - for those customers who have considered Active Data Guard to create a mirror of an E-Business Suite database for hardware or software failure, it would be really valuable to be able to use that database for reporting. Currently, the database simply sits, unused, unless a failure occurs.

For Active Data Guard to work, you would have to break the mirror to query against the database, but Steven said that Oracle is working on a series of patches that would allow you to run reads against the real-time copy of the database. This functionality will be included with Version 11gR2, and there may be a port for 11gR1. The one catch is that not all reports will work in this environment. Steven asked that if anyone had E-Business Suite reports (provided by Oracle, not custom reports written by customers!) that they would especially like to be able to run on Active Data Guard, that they should send Steven a note with a list of those reports so that he could ensure that they were tested.

That said, we leave you with two pieces of valuable information - first, Steven's blog, http://blogs.oracle.com/stevenChan/about.html, and second, his email, steven dot chan at oracle dot com.

An Inquiring Mind Wants to Know (More About Reimplementing vs Upgrading) by Barbara Matthews

At the end of the Reimplement vs. Upgrade Panel at Collaborate 10, someone asked the panel ”Do third party products that claim to fix the issues that might make you decide to reimplement actually work?” The issues include changing the chart of accounts, calendar, and organization structures, or consolidating multiple instances to a single global instance with a shared service center.

The question went unanswered, because no one on the panel had firsthand experience. Since Floyd Teter said “Reimplementing is like getting a root canal without anesthetic in the lobby of the IRS while you're waiting for the audit,” and others on the panel agreed, I decided to do a little digging around. If you can avoid reimplementing the full E-Business Suite and migrating your 11i data, based on everything that was said by the panel, then it would be worthwhile to do so.

So I searched for a software vendor and came across eprentise. I started with Skip Straus , an ex-Oracle Consulting Practice Manager and now an eprentise salesman, and asked him lots of questions. Then I read a paper that he wrote called Why Reimplement? I really wanted to attend the eprentise presentations at Collaborate on global instance consolidation and upgrade vs. reimplement, but that didn't work out. I spoke to Skip again over the phone, asked more questions, and finished up by looking at the eprentise website.

I also asked around to see if my consulting colleagues had any opinions.

The biggest concern raised by consultants was whether Oracle would support a customer that used eprentise transformation software to change Oracle EBS’s "implementation-time configurations." According to Skip, Oracle does not support third party tools, nor does it support the conversion scripts that you would write yourself to extract and transform the data. The assorted eprentise solutions change the data content or format (such as numeric to alpha), but not the database structure. The result is consistent and correct. He said that eprentise has lots of customers who have made the move, and they haven't had any customer report that Oracle wouldn't support them. The eprentise software changes and converts the data; the company supports the conversion and will address any issues. That does not violate any Oracle support agreement.

Also, if you're going to reimplement because your data is bad, your choice is to clean up your data anyway, or abandon it since it’s untrustworthy. If you are reimplementing because you want to change your chart of accounts, well, you'll still be changing your chart of accounts as part of the reimplementation. So it would seem like fixing the data over on your Release 11i side, making the changes to base setups in the E-Business Suite that are not easily changed, would be a good way to go. I also like the idea of changing your data first, and upgrading second, rather than trying to do both at the same time as part of a reimplementation.

Migrating all your data manually seems like a painful way to go rather than using Oracle's well-tested upgrade path. I understand the panelists who said it would take a lot more time and money to reimplement and migrate your data.

I'll leave you with these questions:

1) If you've used a third party vendor's software to avoid reimplementing, whose product did you use?
2) Did it work?
3) Were the tools easy to use?
4) Would you recommend this path to others?
5) Do you have any caveats?
6) Did you run into any issues with Oracle Support?

Drop me a line at editor@trutek.com, as I'd love to hear more about this.

Collaborate 10 Release 12 Reimplement vs. Upgrade Panel by Barbara Matthews

The Panel

Panel members included Sandra Vucinic, Vlad Group, Inc., John Stouffer, Independent Consultant, Floyd Teter, JPL, Stephen Horgan, Oracle Corporation, Kyle Harris, Oracle Corporation, Mike Swing, TruTek, and Alyssa Johnson, Solution Beacon.

The Questions and Answers

What are the main decision points for upgrading versus reimplementing?

- If you are thinking of changing your accounting structure, you may have to reimplement. Reimplementing can offer a good opportunity to make changes to the accounting setup manager, subledger accounting, and the chart of accounts, and implement additional ledgers. Other reasons that might drive a reimplementation include if there is significant bad data in the existing environment, if the customer has lost their customization history, or company wide consolidations, mergers and acquisitions.

- The biggest issue with reimplementing is money. When you upgrade, you can expect to spend 20-25% of your original implementation costs. Reimplementation costs go way up; close to the original implementation costs. Floyd Teter suggested that if you can squeeze by with an upgrade, do the upgrade. If something dramatic has changed with the way you model your business, then reimplement. Floyd said that reimplementing versus upgrading was strictly a cost/benefit tradeoff.

Do reimplementations take longer than upgrades?

- Floyd said that reimplementation is always going to take longer. He suggested tacking on 40% to the schedule in terms of effort, but it could be even more, depending on your implementation.

- If you haven't archived or purged your data in a very long time, and have a lot of historical data, how does that drive the implement versus upgrade decision? Can you update selected data?

- You should archive as much data as you can before your upgrade process starts.

- You can also bring over only the data you want, and use your Release 11i instance to reference historical data.

- You can use Oracle's Upgrade by Request process to pick and choose how much data to bring over. Oracle provides a lot of flexibility, but you have to time it and make sure it makes sense. With SLA there is a pre-upgrade patch that lets you control how much of the data you upgrade. If you have 10 million debits and credits, they will all get updated, but you can use Upgrade by Request to update some now and the rest later. You can upgrade the last fiscal year of your data, and it will ensure you have at least 6 months worth of data when you go live.

Are there any lessons learned from upgrading?

- The technological architecture changes, along with E-Business Tax, SLA and TCA caused a big increase in size with Release 12. TCA alone went from 30 tables to 300. You should expect to need an additional 20-25% of disk space for Release 12.

- With the original upgrade to Release 12, AP changed considerably and there were a number of functionality issues that came up. AP is in much better shape since Release 12.0.4, although 12.0.6, the most current release, is also the terminal release for 12.0.x. Performance has improved and there are less errors. Oracle has a proactive information center on My Oracle Support that has information about AP. In many cases, you can find solutions without even logging an SR. AP in Release 12.1.2 is as stable as the other subledger modules.

How does customization impact the decision to reimplement or upgrade?

The consensus was that customization impacts need to be dealt with for both reimplementing and upgrading, and that customizations shouldn't have more impact on one method over another. For both reimplementing and upgrading, customers should consider the following:

- No matter what you decide, those customizations are in your business now because they serve a purpose and are addressing business needs. From that point of view, you need to decide whether the business process gap is still there or not. You need to figure this out whether you plan to upgrade or reimplement. The good news is that sometimes customers have been able to eliminate customizations thanks to new features in Release 12.

- You should catalog your customizations. You should know why you put those customizations in and map it to Release 12 functionality to see which customizations you can retire, and then retire what you can. By doing this groundwork, your upgrade or reimplementation will be that much easier.

- Oracle Consulting offers in its Advanced Customer Services a CEMLI Catalog Service that provides an inventory of your CEMLIs (Configuration/Customization, Extension, Modification, Localization, and Integration). These are scripts, and don't appear to be generally available except through Oracle Consulting. But they sounded like they would be quite handy. You can read more about them at http://www.oracle.com/support/advanced-customer-services/cemli-services.html - They are available as part of a "free" Oracle Insight engagement.

The Gorilla in the Room: We're thinking about reimplementing next year. Should we do that or wait for the Fusion Applications?

These were the pearls of wisdom from our experts:

Sandra - Consider Release 12 as a milestone in your journey. It is stable. There are live customers on it. It is a known animal. Doing nothing leaves you with less options. R12 is your milestone to get to Fusion. A lot of great business functionality is available now. With desupport for Release 11i coming in December, 2010, you need to get to Release 12, rather than wait for Fusion.

Mike Swing - If you wait to upgrade to R12, you are putting your production system at risk of being forced to upgrade in a hurry to resolve an issue that can't be resolved in R11i.

Alyssa - Don't wait, you won't regret it.

Floyd - Reimplementing is like getting a root canal without anesthetic in the lobby of the IRS while you're waiting for the audit. Upgrading is always the better choice if you can.

Kyle Harris, from Oracle, pointed out that you may not have a path from Release 11i directly to Fusion. Floyd explained that there's no guarantee that Oracle will offer a direct path from Release 11.5.10 to Fusion 2 or 3. The current direct path offering is for Release to Fusion 1. So if you think you're going to sit on Release 11.5.10 and wait for a fully functional Fusion Application, don't. 80% of E-Business Suite Release 11.5.10 customers are probably not good candidates for Fusion Release 1. Fusion Release 1 will not include manufacturing, for example.

Stephen - Oracle Support will support the upgrade and not the reimplementation. Customers need to really make sure they can't upgrade before considering reimplementation. This point was made at other conference presentations as well - Oracle has limited resources for testing and must focus on what the vast majority of customers will be doing, which is upgrading. Reimplementation issues will vary by customer, depending on their situation, so Oracle Support will certainly try to help with issues, but the more thoroughly tested path will always be the upgrade path.

In the past, when we went from Release 10.7 to Release 11i, Oracle had good numbers on the expected downtime window given certain factors. What is a typical idea of what you can expect for the downtime window?

Mike Swing suggested that the upgrade from RDBMS Version to could take 8-12 hours, with the applications upgrade taking 12-15 hours, plus 1 day of testing; so about 3 days. To get that 3 day downtime window, the other panelists suggested you would need to consider the following:

- Test your hardware environment. In most cases you will bring in new servers. Do as much as you can ahead of time. Consider adding CPUs that you might not use moving forward. Can you lease additional CPUs for the upgrade period? Can you borrow CPUs from other hardware?

- Brainstorm ways to improve performance with your third party vendors and consultants.
Use Upgrade by Request to process the most necessary data during the upgrade, and then complete processing the rest of the data after go-live.

- Consider taking the database upgrade off the table. All new releases of the database have been certified. Mitigate the risk. While you do have to test twice if you do the database upgrade separately from the applications upgrade, the testing is different for the database upgrade, where performance considerations are the main issue, and the Applications upgrade testing for R12 is largely functionality testing.

- You won't know until you test it, then, after you test it, try to figure out how much time it will take and what you can do to reduce the time. There are lots of documents about how to reduce the downtime on My Oracle Support.


All in all, the consensus from the panel was that if you can avoid doing a reimplementation, you should, due to issues with cost, support, and time. For those customers who are waiting for Fusion, the panel concluded that if the functionality provided in Fusion Release 1 doesn't match your business needs, then you should move to Release 12 first, and wait for Fusion to catch up with your requirements in a future release.