Query Applied Patches in Oracle EBS 12.2

 

Query Applied Patches in Oracle EBS 12.2

In releases prior to 12.2, we used to query ad_bugs or ad_applied_patches to find out if a patch is applied or not.
In 12.2, this is no longer the right way to do it due to the implications of online patching.
For example, if you apply a patch in an online patching cycle and aborted the cycle before it is complete, the records inserted in the above two tables will not be updated or removed.

The following query helps in identifying the patches applied:

SELECT adop_session_id, bug_number, session_type, 
       DECODE(status,'N','Applied on other nodes',
                     'R','Running',
                     'H','Failed (Hard)',
                     'F','Failed (Jobs Skipped)',
                     'S','Success (Jobs Skipped)',
                     'Y','Success',
                     'C','Clone Complete') status,
       applied_file_system_base, patch_file_system_base,
       node_name, start_date, end_date, 
       ROUND((end_date - start_date) * 24*60,2) exec_time,
       adpatch_options, autoconfig_status, driver_file_name
FROM ad_adop_session_patches
WHERE session_type IN ('ADPATCH','HOTPATCH','DOWNTIME','ONLINE')
ORDER BY adop_session_id, start_date, end_date;

Session type column was restricted to the following values:

• ADPATCH: Patches applied using the traditional adpatch tool. It is used right after installing 12.2 and before online patching is enabled by applying patch 13543062.
• HOTPATCH: Patches applied in hotpatch mode (aka. no online patching cycle is involved). It should be used only when instructed by Oracle.
• DOWNTIME: Patches applied when all application services are down. Only used when instructed by Oracle, such as when applying 12.2.6 RUP.
• ONLINE: Patches applied in a normal online patching cycle. This is the recommended method.

If you want to find out which patch fixed a specific bug, you can use the following query:

SELECT b.bug_number, asp.adop_session_id, asp.bug_number patch#,
       asp.session_type, asp.applied_file_system_base,
       asp.node_name, asp.start_date, asp.end_date
FROM ad_bugs b, ad_patch_run_bugs prb, ad_patch_runs pr,
     ad_patch_drivers pd, ad_adop_session_patches asp
WHERE b.bug_id = prb.bug_id
  AND prb.patch_run_id = pr.patch_run_id
  AND pr.patch_driver_id = pd.patch_driver_id
  AND pr.patch_run_id = asp.patchrun_id
  AND prb.applied_flag = 'Y'
  AND b.bug_number = '&bug_num';