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/[email protected] 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.
You forgot to mention that you need to connect to the apps schema in order to execute the ad_bugs table.
I have 2 ERP environments, one being a clone of other. For a particular patch XXX , on the 1st env I see an entry in both ad_bugs as well as ad_applied_patches whereas on the second env, I see an entry only in ad_bugs. I am not sure why this is the case.
What I am trying to do is run a query against each database and do a diff on the outputs to find the patch level difference between the 2 environments.
I would very much like to know when applying a patch exactly which files were in fact updated. Sometimes there can be a patch that will contain thousands of files, but in fact only one or two were updated. The log files don’t seem to be a concrete source of information in this respect.
pls provide me a sql by which I will be able to get the total patching time (end time- start time) for an adpatch
This is an older thread, but I am hoping you can shed some light on something. I have two apps tiers for an EBS instance. The APPL_TOP is not shared. The primary tier is patched normally, the second tier is patched using the nodatabaseportion option. How can I tell if a patch has been applied to the second tier? Querying ad_bugs doesn’t tell me which tiers have the patch.
I’m not the original author, but I think I can help. :) The table you’re looking for is ad_patch_runs. Join with ad_appl_tops to translate the appl_top_id field. To join ad_patch_runs to ad_bugs, you’ll need to use ad_comprising_patches (which has bug_id and patch_driver_id). I might be missing a piece, but that should get you started.