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:
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
AD_APPLIED_PATCHES and it will insert thousands of entries in
AD_BUGS (including 3480000).
Caveat: if you use merged patches, always check
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.
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/apps@SID patch_list=ad_applied_patches
The output file
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.