There are some tables in oracle apps (AD tables especially) involved when applying patches.
Some of them are very useful when we need specific information about patch already applied.
I will show the main tables and afterwards some handy related SQL’s to retrieve patch applied details and how we can also get all this information via OAM.
AD_APPLIED_PATCHES – The main table when we are talking about patches that applied in Oracle Apps.
This table holds information about the "distinct" Oracle Applications patches that have been applied.
If 2 patches happen to have the same name but are different in content (e.g. "merged" patches), then they are considered distinct and this table will therefore hold 2 records (eTRM).
I also found that if the applications tier node is separate from the concurrent manager node, and the patch applied on both nodes, this table will hold 2 records, one for each node.
AD_PATCH_DRIVERS – This table holds information about all patch drivers included in specific patch.
For example if patch contain only one unified driver like u[patch_name].drv then ad_patch_drivers will hold 1 record.
On the other hand, if patch contain more than 1 driver, for example d[patch_name].drv and c[patch_name].drv, this table will hold 2 records.
AD_PATCH_RUNS – holds information about each execution of adpatch for a specific patch driver.
In case a patch contains more than one driver, this table will hold a record for each driver.
This table also holds one record for each node the patch driver has been applied on (column APPL_TOP_ID).
AD_PATCH_RUN_BUGS – holds information about all the bugs fixed as a part of specific run of adpatch.
AD_BUGS – this table holds information about all bug fixes that have been applied.
We have 2 options to view applied patch information:
1) via OAM – Oracle Applications Manager
2) Via SQL queries
With OAM it’s easy and very intuitive, from OAM site map -> “Maintenance” tab -> “Applied Patches” under Patching and Utilities.
Search by Patch ID will get all information about this patch; In addition, drill down by clicking on details will show the driver details.
For each driver we can use the buttons (Timing Details, Files Copied, etc.) to get more detailed information.
With SQL we can retrieve all the above information, sometimes more easily.
For example: How to know which modules affected by specific patch?
With OAM:
1) search patch by Patch ID
2) click on Details
3) For each driver click on “Bug Fixes” and look on product column.
With SQL:
Run the following query, it will show you all modules affected by specific patch in one click…
select distinct aprb.application_short_name as "Affected Modules"
from ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_patch_run_bugs aprb
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and apr.patch_run_id = aprb.patch_run_id
and aprb.applied_flag = 'Y'
and aap.patch_name = '&PatchName';
Another SQL will retrieve basic information regarding patch applied, useful when you need to know when and where (node) you applied specific patch:
select aap.patch_name, aat.name, apr.end_date
from ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_appl_tops aat
where aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and aat.appl_top_id = apr.appl_top_id
and aap.patch_name = '&PatchName';
To check if specific bug fix is applied, you need to query the AD_BUGS table only.
This table contains all patches and all superseded patches ever applied:
select ab.bug_number, ab.creation_date
from ad_bugs ab
where ab.bug_number = '&BugNumber';
For any question or additional information you are welcome to leave a comment...
Aviad |
7 comments:
Good Information, it will be good if you can add adpatch options=prereq, that will be easy for anyone to get list of patches to apply.
I got this from Oracle Support and it shows patches applied to multiple application tiers - this sql shows if the 11.5.10.2 maintenance pack has been applied (patch number 3480000)
===================================
DECLARE
TYPE p_patch_array_type is varray(30) of varchar2(10);
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
p_result varchar2(15);
p_instance varchar2(15);
gvAbstract varchar2(240) := NULL;
CURSOR alist IS
select appl_top_id, name from ad_appl_tops;
procedure println(msg in varchar2)
is
begin
dbms_output.enable(1000000);
dbms_output.put_line(msg);
end;
BEGIN
select instance_name into p_instance from v$instance;
open alist;
p_patchlist:= p_patch_array_type('3480000');
LOOP
FETCH alist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN alist%NOTFOUND;
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ' - - - ' || p_instance );
println('=============================');
for i in 1..p_patchlist.count
loop
begin
select ABSTRACT into gvAbstract
from FND_UMS_BUGFIXES
where BUG_NUMBER = p_patchlist(i);
exception
when NO_DATA_FOUND then
gvAbstract := NULL;
end;
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,p_patchlist(i));
case p_patch_status
when 'EXPLICIT' then
p_result := 'APPLIED';
else
p_result := p_patch_status;
end case;
println('Patch ' || p_patchlist(i)|| ' - ' || substr(gvAbstract,1,25) || ' - was ' || p_result);
end loop;
END if;
println('.');
END LOOP;
close alist;
END;
/
Good information and explanation.
Keep up the good work.
Regards,
Sabdar Syed.
http://sabdarsyed.blogspot.com
Thanks Sabdar
Aviad
Hi,
Is there any table which holds information regarding which new files have been copied, which existing files have been modified etc during the application of a patch ? I would really like to know
Hi Asif,
You can run adpatch in TEST mode.
It generates a log file with all files that would have involved.
Try:
>$ adpatch apply=n
Hi,
please provide solution for below request to my mail id:
we have around 90 databases of 3 different applications running on 50 linux servers.
if any CPU/one-off patch applied on oracle home existing on above servers. want to check that CPU/one-off patch applied or not from one single point i.e., from one master server.
Thank you so much....
Naresh
nkumarbio@gmail.com
Post a Comment