Query to check patch applied in R12.2

We often need to find out what all patches are applied to the EBS instance. Now, this varies as per the EBS instance version. We have different queries for R12.1 and R12.2.Let’s find out the Query to check the patch applied in R12/R12.1 and Query to check patch applied in R12.2

Query for R12/R12.1

ad_bugs tables contain the information for all the patches applied to the system.

we can use the 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;

Query to check patch applied in R12.2

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 abort the cycle before it is complete, the records inserted in the above two tables will not be updated or removed.

There are many ways in which we can determine if the patch is applied to R12.2

(a) Oracle Applications Manager’s “Patching and Utilities” feature

We can log in to the Oracle application manager and go to 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 the 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;

Status means

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 the 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 that 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;

The 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 are applied when all application services are down. Only used when instructed by Oracle, such as when applying 12.2.6 RUP.
ONLINE: Patches are applied in a normal online patching cycle. This is the recommended method.

I hope you like this article on a query to check patch applied in r12.2.

Related Articles

Adop(Ad online patching utility) explained R12.2

Oracle patching: Adpatch Complete Overview

Downtime and Hotpatch apply mode in adop R12.2

How to rollback the patch after failed cutover phase in R12.2