Oracle E-Business Suite: Querying Patches, Part 2

In the first part of this blog I tried to shed some light on EBS patch terminology and naming conventions. In this post, I’ll show you how to check your patchset levels and query applied patches.

The very first question is, how do I find out if patch “1234567” for example, was applied?

Of course you can use OAM, as the current release has made a huge step in enhancing OAM to show all necessary information about applied patches. If, however, you don’t like to rely on the GUI, you have a number of other options, which I’ll show you here.

Essentially there are two tables one can use to check for applied patches: AD_BUGS and AD_APPLIED_PATCHES.

It is important to understand the difference between these two. AD_BUGS contains all bug numbers fixed on your system, while AD_APPLIED_PATCHES contains all patch numbers which were applied to your system only.

For example: if you apply 11.5.10 CU2, it will add a row with patch_name=3480000 to AD_APPLIED_PATCHES and it will insert thousands of entries in AD_BUGS (including 3480000).

Caveat: if you use merged patches, always check AD_BUGS.

So how do you query the above two tables?

select bug_number from ad_bugs where bug_number in ('&bug_number');

If this query returns at least one row, that means that the particular bug has been fixed or a patch of the same number was applied.

If you are looking for details of specific patch that was applied, you can join AD_APPLIED_PATCHES with couple other tables like this:

col PATCH_NAME format a10
col PATCH_TYPE format a10
col DRIVER_FILE_NAME format a15
col PLATFORM format a10
select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE,  AD.PLATFORM,AL.LANGUAGE
from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID
and AP.PATCH_NAME = '4502962';

PATCH_NAME PATCH_TYPE DRIVER_FILE_NAM CREATION_DATE   PLATFORM   LANG
---------- ---------- --------------- --------------- ---------- ----
4502962    PATCH-SET  u4502962.drv    30-DEC-06       LINUX      US

If the above query returns at least one row, that means that someone has downloaded a patch from metalink and applied it to the system. If the patch is NLS specific, you should get one row for each language version in which the patch was applied.

Not all patches are required to be translated and not all patches are platform-specific. To find out whether a patch requires the NLS version for your particular language set, you’ll have to query metalink.

Now that we know how to query individual patches, let’s look at querying patchset levels. There are several ways how to do this. Here’s a query for a quick SQL result (for 11i):

col PRD format a40 trunc
col PRDID format a5
col PRDSTATUS format a10
col PATCHSET format a20
SELECT V.APPLICATION_NAME PRD, to_char(V.APPLICATION_ID) PRDID, L.MEANING PRDSTATUS, DECODE(I.PATCH_LEVEL, NULL, '11i.' || v.APPLICATION_SHORT_NAME || '.?', I.PATCH_LEVEL) patchset
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status )
ORDER BY 1;

Another option is to use well known $AD_TOP/sql/adutconf.sql. You can run this script anytime. It will spool adutconf.lst where you can see actual patchlevels of each product, but it will not tell you family pack and pseudo product patch levels.

The Last option is more time-consuming, but it provides extra information, including the current and latest family pack levels. This information is not recorded in 11i, or at least not recorded in an easy-to-read way. In R12 there is a new table, which I will show later.

The patchsets.sh script is not part of the EBS installation; it is available from oracle ftp —
ftp://ftp.oracle.com/support/outgoing/PATCHSET_COMPARE_TOOL/patchsets.sh. If you want to check the latest available patchsets or family packs for your release, I suggest you download a new version every time, because it can be updated very often.

patchsets.sh is really thorough. It queries the AD_BUGS table, generates a csv file, and compares the patch numbers on its list. Unless you have used merged patches, it’s better to run it with the patch_list=AD_APPLIED_PATCHES option, because it will complete much faster. For example: $ ./patchsets.sh connect=apps/[email protected] patch_list=ad_applied_patches

The output file Report_11i.txt or Report_R12.txt will contain all the information about patchsets and family packs you need. On top of that, it will also tell you whether newer patchset exists and if it was released.

Here’s an example showing part of the report output:

              FAMILY PACK PATCHES
Product Baseline Version  Running Version   Latest Available,Status
------- ----------------- ---------------   -----------------------
atg_pf  R12.ATG_PF.A(4461237) R12.ATG_PF.A.4(6272680)  R12.ATG_PF.B(6430106), Rel-Not_Dist
bis_pf  R12.BIS_PF.A(4458359) R12.BIS_PF.A.4(6507067)  R12.BIS_PF.B(5915303), Rel-Not_Dist
cc_pf   R12.CC_PF.A(4464624) R12.CC_PF.A.4(6507355)R12.CC_PF.B(6663753), Rel-Not_Dist
dmf_pf  R12.DMF_PF.A(4510290) R12.DMF_PF.A.4(6506939)  R12.DMF_PF.B(6658955), Rel-Not_Dist
fin_pf  R12.FIN_PF.A(4175000) R12.FIN_PF.A.4(6493602)  R12.FIN_PF.B(4565490), Rel-Not_Dist
hc_pf                                              R12.HC_PF.A.1(6505402), Rel-By_Metal
hr_pf   R12.HR_PF.A(4719824) R12.HR_PF.A.4(6494646)R12.HR_PF.B(6603330), Rel-Not_Dist
mas_pf  R12.MAS_PF.A(4456002) R12.MAS_PF.A.4(6506236)  R12.MAS_PF.B(6654877), Rel-Not_Dist
ok_pf   R12.OK_PF.A(4510215) R12.OK_PF.A.4(6508121)R12.OK_PF.B(6659486), Rel-Not_Dist
om_pf   R12.OM_PF.A(4464619) R12.OM_PF.A.4(6508129)R12.OM_PF.B(6659484), Rel-Not_Dist
opm_pf  R12.OPM_PF.A(4510794) R12.OPM_PF.A.4(6506932)  R12.OPM_PF.B(6659485), Rel-Not_Dist
pj_pf                      R12.PJ_PF.A.4(6512963)R12.PJ_PF.B(6664666), Rel-Not_Dist
plm_pf  R12.PLM_PF.A(4508296) R12.PLM_PF.A.4(6506844)  R12.PLM_PF.B(6658960), Rel-Not_Dist
prc_pf  R12.PRC_PF.A(4172000) R12.PRC_PF.A.4(6497749)  R12.PRC_PF.B(4565514), Rel-Not_Dist
scm_pf  R12.SCM_PF.A(4514188) R12.SCM_PF.A.4(6508212)  R12.SCM_PF.B(6658964), Rel-Not_Dist
scp_pf  R12.SCP_PF.A(4510600) R12.SCP_PF.A.4(6508131)  R12.SCP_PF.B.1(7039001), Rel-Not_Dist
sem_pf  R12.SEM_PF.A(4455884) R12.SEM_PF.A.4(6493617)  R12.SEM_PF.B(4565524), Rel-Not_Dist
srv_pf  R12.SRV_PF.A(4464549) R12.SRV_PF.A.4(6506461)  R12.SRV_PF.B(6657060), Rel-Not_Dist
unv_pf  R12.UNV_PF.A(4956355) R12.UNV_PF.A.4(6512384)  R12.UNV_PF.A.6(7315606), Rel-Not_Dist

In R12 there is a new alternative for the patchsets.sh script. Information about patchset levels for all base product and pseudo-products is also contained in table called AD_TRACKABLE_ENTITIES. This table simplifies the lookup of family pack versions.

If you’d like to know more about patching or related Apps DBA activities, please leave a comment. Thanks for reading.