We often need to find out what all patches are applied to the EBS instance. Now this vary as per the EBS instance version. We have different queries for R12.1 and R12.2.
ad_bugs tables contains the information for all the patches applied to the system.
we can used below query to find out the patch application in the instance
sqlplus apps/<APPS PASSWORD> SELECT DISTINCT RPAD(a.bug_number, 11)|| RPAD(e.patch_name, 11)|| RPAD(TRUNC(c.end_date), 12)|| RPAD(b.applied_flag, 4) BUG_APPLIED FROM ad_bugs a, ad_patch_run_bugs b, ad_patch_runs c, ad_patch_drivers d , ad_applied_patches e WHERE a.bug_id = b.bug_id AND b.patch_run_id = c.patch_run_id AND c.patch_driver_id = d.patch_driver_id AND d.applied_patch_id = e.applied_patch_id AND a.bug_number in ('<bug_no>','<bug_no>') ORDER BY 1 DESC;
The above query 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.
There are many ways which we can determined if the patch is applied to R12.2
a. Oracle Applications Manager’s “Patching and Utilities” feature
We can login to Oracle application manager and go Patching and Utilities and enter the patch. It will show up whether the patch is applied or not
b. AD_PATCH.IS_PATCH_APPLIED pl/sql function.
Using this API is an alternative method for users without access to Oracle Applications Manager’s “Patching and Utilities” feature to determine if a certain patch is applied.
Usage: select AD_PATCH.IS_PATCH_APPLIED(\'$release\',\'$appltop_id\',\'$patch_no\',\'$language\') from dual;
Example sql using the APPL_TOP ID for a specific node in a multinode environment (1045 is the APPL_TOP ID):
SELECT adb.bug_number,ad_patch.is_patch_applied('R12', 1045, adb.bug_number) FROM ad_bugs adb WHERE adb.bug_number in (647647647);
or for single app tier installations:
select ad_patch.is_patch_applied('R12',-1,20034256) from dual; expected results: EXPLICIT = applied NOT APPLIED = not applied / aborted
c. We can use below query to find out the patches applied to the system
set pagesize 200; set linesize 160; column adop_session_id format 999999999999; column bug_number format a15; column status format a15; column applied_file_system_base format a23; column patch_file_system_base format a23; column adpatch_options format a15; column node_name format a15; column end_date format a15; column clone_status format a15; select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS from ad_adop_session_patches order by end_date desc;
N – Not Applied In the current node but applied in other nodes
R – Patch Application is going on.
H – Patch failed in the middle. (Hard Failure)
F – Patch failed in the middle but user tried to skip some failures.
S – Patch Application succeeded after skipping the failed jobs.
Y – Patch Application succeeded.
C – Reserved for clone and config_clone. Indicates clone completed
d. Another query which can be used
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.