Maintaining and patching the E-Business Suite is faster when using the AD Merge Patch utility or admrgpch as the applmgr user, to merge patches. AD Merge Patch merges separate patches together, and the Patch Name stored in the database is “merged” and the patch driver is u_merged.drv. Merging patches avoids the duplication of effort, during the application of the merged patch, if multiple patches are trying to copy and generate the same file. The biggest time savings is the ability to recompile invalid objects only once, instead of once for each patch.

The database stores information about the patch when each patch is applied. The patch name is usually the same as the patch number. However, for a merged patch, the patch name is “merged”. This makes it difficult to report the patches that have been applied to your instance. One approach is to keep the patches on the filesystem in a directory named after the patch session.

Another approach to find all patches within a merged patch is to understand the tables that contain this information and the relationships between these tables.

By joining  the following APPLSYS tables, we can relate Patches to Bugs and know when the patches were applied:

ad_applied_patches   ->  ad_patch_drivers   ->   ad_comprising_patches   ->  ad_bugs

We can find all the bugs associated with a patch, even a merged patch.

1.      Find the APPLIED_PATCH_ID for a merged patch

Query the AD_APPLIED_PATCHES table:

SELECT applied_patch_id, rapid_installed_flag, patch_name, patch_type, maint_pack_level

FROM   applsys.ad_applied_patches

 

  Figure 1

Problem: We need to find all the patches that were part of the merged patch.

2.      Find the PATCH_DRIVER_ID

295501 is the applied_patch_id for the merged patch.  In order to find the detail of the merged patch, we need to look in the AD_PATCH_DRIVERS table, using the APPLIED_PATCH_ID from the AD_APPLIED_PATCHES table.

From the AD_PATCH_DRIVERS table we find the APPLIED_PATCH_ID 295501 and the corresponding PATCH_DRIVER_ID (279993):

AD_PATCH_DRIVERS Table:

 

Figure 2

3.      Find all the BUGS

Then, use the corresponding PATCH_DRIVER_ID 279993 in the following query:

SELECT *

FROM (SELECT ab.bug_number ||DECODE(ab.baseline_name,NULL,”,’.’)

|| ab.baseline_name,  acp.patch_abstract

FROM  ad_comprising_patches acp, ad_bugs ab

WHERE acp.bug_id = ab.bug_id AND acp.patch_driver_id =279993)

WHERE ROWNUM<201

This returns the original bugs/patches that were merged together:

 

Figure 3

I use bugs/patches because sometimes a bug number will also be the patch name/number. This happens when the bug is not merged with other bugs into a bigger patch and assigned a surrogate number.  Patches with surrogate numbers are related to multiple bugs.

4.      Join the queries together

By joining the queries together, we get a list of all patches and the patch names, even patch names that were assigned by Oracle. Oracle also uses AD Merge Patch to merge patches.

SELECT ab.bug_number, ap.patch_name, ap.patch_type, ap.maint_pack_level,

ap.last_update_date

FROM     applsys.ad_comprising_patches acp,

applsys.ad_bugs ab,

applsys.ad_patch_drivers pd,

applsys.ad_applied_patches ap

WHERE    acp.bug_id = ab.bug_id

AND acp.patch_driver_id = pd.patch_driver_id

AND pd.applied_patch_id = ap.applied_patch_id

ORDER BY ap.last_update_date desc

 

Figure 4