In EBS R12.2, ad_bugs does not tell if an EBS patch is applied or not due to the implications of online patching and its "abort" feature.
1. The simple way to check if a parch (33207251, for example) was applied or not in R12.2 (if node info is not important in a multi-node instance):
SQL> SELECT ad_patch.is_patch_applied('R12',-1,33207251) FROM dual;
AD_PATCH.IS_PATCH_APPLIED('R12',-1,33207251)
----------------
EXPLICIT
2. More accurate way to check patching status:
Query (modified from a query in Doc ID 443761.1) for finding out more details on patching (if it was applied by ADOP). In a multi-nodes environment, it does not tell which node, but the timestamp gives you some clue.
SQL> SELECT DISTINCT a.bug_number, e.patch_name, to_char(c.end_date, 'DD-MON-RRRR HH24:MI:SS') End_date, e.patch_type, d.patch_abstract
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 = '33207251'
order by 1,3 ASC;
BUG_NUMBER PATCH_NAME END_DATE PATCH_TYPE PATCH_ABSTRACT
--------------- ------------ ---------------------------- ------------------------------------------------------
33207251 33487428 28-JAN-2022 09:14:10 ONE-OFF ORACLE APPLICATIONS RELEASE
12.2: CPU PATCH FOR JAN 2022
33207251 33487428 29-JAN-2022 08:20:21 ONE-OFF ORACLE APPLICATIONS RELEASE
12.2: CPU PATCH FOR JAN 2022
NOTES: If a patch is part of another big patch, such a CPU patch, it might not show up in table ad_applied_patches or ad_adop_session_patches. For instance, patch 33207251 was applied successfully as part of Jan 2022 CPU patch 33487428, but it does not show up in queries:
SQL> select * from ad_applied_patches where patch_name in ('33207251', '33487428');
SQL> select * from ad_adop_session_patches where bug_number in ('33207251', '33487428');
3. Below query could be a good one to tell if a patch was applied successfully or not on node level. But, for a cloned instance, it lists only patches that were applied after clone.
SQL> SELECT adb.bug_number, aas.name appl_top_name, adb.creation_date, adb.language,
decode(ad_patch.is_patch_applied('R12',aas.appl_top_id,adb.bug_number,adb.language),'EXPLICIT','APPLIED','NOT_APPLIED','NOT APPLIED') status
FROM ad_bugs adb,
(select distinct appltop_id appl_top_id, node_name name from ad_adop_sessions
where node_name in (select node_name from ADOP_VALID_NODES) ) aas
where adb.bug_number in (
'33168664',
'33207251',
'33487428',
'26521736'
) order by creation_date desc, adb.bug_number,aas.name,adb.language;
EXPLANATION:
Oracle Doc ID 1963046.1 give a query to find if a patch was applied or not:
SQL> SELECT adb.bug_number,ad_patch.is_patch_applied('R12', \'$appl_top_id\', adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in ('\$patch_no\');
expected results:
EXPLICIT = applied
NOT APPLIED = not applied / aborted
The problem is table APPLSYS.AD_APPL_TOPS has too many obsolete data (Doc ID 2075234.1). Especially for cloned instance, it may be useless in finding APPL_TOP_ID. I find table AD_ADOP_SESSIONS has accurate APPL_TOP_ID info. Below is an example to confirm patching 33168664 failed on 2nd node.
SQL> select distinct appltop_id, node_name, node_type from ad_adop_sessions
where node_name in (select node_name from ADOP_VALID_NODES);
APPLTOP_ID NODE_NAME NODE_TYPE
----------------- -------------------- ----------
2088 appNode1d slave
1088 appNode2d master
SQL> SELECT adb.bug_number,ad_patch.is_patch_applied('R12', 1088, adb.bug_number) status
FROM ad_bugs adb WHERE adb.bug_number in ('33168664');
BUG_NUMBER STATUS
--------------------- -----------
33168664 EXPLICIT
SQL> SELECT adb.bug_number,ad_patch.is_patch_applied('R12', 2088, adb.bug_number) status
FROM ad_bugs adb WHERE adb.bug_number in ('33168664');
BUG_NUMBER STATUS
--------------------- -----------
33168664 NOT_APPLIED
Additional NOTES:
A). Doc ID 443761.1 (How To Check If a Certain Patch Was Applied to Oracle E-Business Suite Instance) also gives a way to check which patches were applied in each ADOP_SESSION_ID (patching cycle). But, I found ad_adop_session_patches is NOT very reliable.
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;
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 exec_time format a15;
column clone_status format a15;
column clone_status format a15;
SQL> select ADOP_SESSION_ID, BUG_NUMBER, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYSTEM_BASE, ADPATCH_OPTIONS, NODE_NAME,
END_DATE, ROUND((end_date - start_date) * 24*60,2) exec_time, CLONE_STATUS,
DECODE(status,'N','Applied on other nodes',
'R','Running',
'H','Failed (Hard)',
'F','Failed (Jobs Skipped)',
'S','Success (worked after skipping the failed)',
'Y','Success',
'C','Clone Complete') status
from ad_adop_session_patches
order by end_date desc;
'R','Running',
'H','Failed (Hard)',
'F','Failed (Jobs Skipped)',
'S','Success (worked after skipping the failed)',
'Y','Success',
'C','Clone Complete') status
from ad_adop_session_patches
order by end_date desc;
B). Snapshot update: If you are sure a patch is applied but it is not showing as applied via the above steps, then update the snapshot manually with the steps below:
1) Start adadmin after source the RUN FS env.
2) Select "2. Maintain Applications Files menu" in "AD Administration Main Menu".
3) In "Maintain Applications Files", select "4. Maintain snapshot information".
4) Select "2. Update current view snapshot" in the "Maintain Snapshot Information".
5) Select "1. Update Complete APPL_TOP" in the "Maintain Current View Snapshot Information".
C). To determine the RUN files system:
SQL> SELECT EXTRACTVALUE(XMLType(text),'//oa_context_file_loc')
appl_top, status
FROM fnd_oam_context_files
WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')
AND CTX_TYPE='A'
AND (status IS NULL OR UPPER(status) IN ('S','F'))
AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'run';
FROM fnd_oam_context_files
WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')
AND CTX_TYPE='A'
AND (status IS NULL OR UPPER(status) IN ('S','F'))
AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'run';
To determine the PATCH files system:
SQL> SELECT EXTRACTVALUE(XMLType(text),'//oa_context_file_loc')
appl_top, status
FROM fnd_oam_context_files
WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')
AND CTX_TYPE='A'
AND (status IS NULL OR UPPER(status) IN ('S','F'))
AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'patch';
appl_top, status
FROM fnd_oam_context_files
WHERE name NOT IN ('TEMPLATE','METADATA','config.txt')
AND CTX_TYPE='A'
AND (status IS NULL OR UPPER(status) IN ('S','F'))
AND EXTRACTVALUE(XMLType(text),'//file_edition_type') = 'patch';
