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
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):
3. Find all the BUGS
Then, use the corresponding PATCH_DRIVER_ID 279993 in the following query:
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)
This returns the original bugs/patches that were merged together:
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,
FROM applsys.ad_comprising_patches acp,
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