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,
12)|| RPAD(b.applied_flag, 4) BUG_APPLIED
ad_patch_drivers d ,
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 (‘<XXXXXX>’,'<YYYYYY>’)
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.
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
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
Another query which can be used
SELECT adop_session_id, bug_number, session_type,
DECODE(status,’N’,’Applied on other nodes’,
‘F’,’Failed (Jobs Skipped)’,
‘S’,’Success (Jobs Skipped)’,
‘C’,’Clone Complete’) status,
node_name, start_date, end_date,
ROUND((end_date – start_date) * 24*60,2) exec_time,
adpatch_options, autoconfig_status, driver_file_name
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.