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.

FND_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:

PURGE-ABLE DATA

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(*)
from FND_LOBS
where expiration_date is not NULL
group by program_name;

PROGRAM_NAME   COUNT(*)
-------------- ----------
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.

DATA THAT WILL NOT BE PURGED

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

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

PROGRAM_NAME           COUNT(*)
---------------------- ----------
                             2062
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;

SUM(DBMS_LOB.GETLENGTH(FILE_DATA))
----------------------------------
5,047,907,542

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;

SUM(BYTES)    SEGMENT_NAME              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:

select
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
                                        546
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.

PCTVERSION

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 :

select SEGMENT_NAME, PCTVERSION
from dba_lobs
where TABLE_NAME ='FND_LOBS';

SEGMENT_NAME              PCTVERSION
------------------------- ----------
SYS_LOB0000040605C00004$$ 10

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

NEXT_EXTENT

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:

select
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  
ORPHANED RECORDS

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.

SELECT COUNT(*)
FROM FND_LOBS FL
WHERE NOT EXISTS
(SELECT '1'
FROM FND_DOCUMENTS_TL FDT, FND_DOCUMENTS FD, FND_ATTACHED_DOCUMENTS FAD
WHERE FD.DOCUMENT_ID = FDT.DOCUMENT_ID
AND FAD.DOCUMENT_ID = FD.DOCUMENT_ID
AND FDT.MEDIA_ID = FL.FILE_ID
AND FD.DATATYPE_ID = 6)
AND PROGRAM_NAME = 'FNDATTCH'
AND EXPIRATION_DATE IS NULL;

COUNT(*)
----------
447

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:

Patch 9004099 - ORPHANED RECORDS IN FND_LOBS TABLE CONSUME DATABASE DISK SPACE

Release 12 Customers:

Patch 9454616 - PROGRAM_TAG,UPLOAD_DATE,EXPIRATION_DATE,PROGRAM_NAME ARE NULL

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.

RECLAIMING SPACE

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

LIMITING ATTACHMENT SIZE

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:

http://www.zoomerang.com/Survey/WEB22B7BG2TA85

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
or
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 11.5.10.2 with CU2 in 2006 and haven’t had much time to patch since then. They’re still using JInitiator, and they’re on RDBMS 10.2.0.3.

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

Limited Report to: APPLFULL and APPLSHAR products

APPLFULL: AK ALR AP AR AX AZ BIS CE CHV EDR FA FII FND FRM FTP GL HZ ICX INV IZU PA PN POA PO POS RCM RG ZPB

APPLSHAR: AD AS AU BIC BIL BIM BIX BOM CRP CUA DT EC ENG FF FLM HRI ISC JTF MFG MRP MSC ONT OPI PAY PER PJM PMI QA SHT WIP XLA

Pseudo Products: ADX AME AML BLC BPA CAC CDR CLE CSK CSZ CTB EDW EWS FTP FWK HCP HCT IGP IGR IPATCH IRC ISX ITA ITM JTA JTH JTO JTP JTT JTU JTY MSX OAM OCM OIE OIR OIT OWF PFT PJR POV RCM TXK UMX

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 11.5.10.2 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 10.2.0.4,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

APPLFULL: ABM AK ALR AMF AMS AP AR ASF AS ASL ASO ASP AST AX AZ BEN BIC BIL BIM BIS BIV BNE BOM CCT CE CHV CRP CSC CS CSI CSS CUG CZ EAA EC ECX EDR ENG FA FEM FII FND FRM FV GHR GL GMA GMD GME GMF GMI GML GMP GR HRI HXC HXT HZ IBA IBC IBY ICX IEO IES IEU IEX IGI INV ISC IZU JTF JTM ME MRP OKC OKE OKI OKL OKS OKX ONT OPI OTA PA PAY PER PJM PMI PN POA PO PQH PQP PSA PSB PV QA QOT QP QRM RG SSP WIP WSH XDO XNI XTR

APPLSHAR: AD AMV ASG AU BIX CN CSD CSF CUA DT FF FLM IBE IEM MFG MSC PSP SHT XLA

Pseudo Products: ADX AME AML BLC BPA CAC CDR CLE CSK CSZ CTB EDW EWS FTP FWK HCP HCT IGP IGR IPATCH IRC ISX ITA ITM JTA







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.