Quirky 11i Apps Patch queires
Method 1
Check Patches applied from Oracle Applications Manager (OAM).
a) Connect to OAM:
http://hostname.domain:port/servlets/weboam/oam/oamLogin
Go to Site Map-->Maintenance-->Applied Patches
Enter Patch ID and press 'Go'
See if Patch is returned.
Method 2
Use 'adphrept' utility documented in Metalink Note:181665.1, section 'Retrieving information on patches or files that have been applied via adpatch'.
$AD_TOP/patch/115/sql/adphrept.sql (available on admin and web nodes in multi tier APPL_TOP)
(1) Patch History report usage:
$AD_TOP/patch/115/sql/adphrept.sql <query_depth> < bug_number or ALL> <bug_product or ALL> \
<end_date_from (mm/dd/rr or ALL)> <end_date_to (mm/dd/rr or ALL)> \
<patchname/ALL> <patchtype/ALL> <level/ALL> <language/ALL> \
<appltop/ALL> <limit to forms server? (Y/N)> \
<limit to web server? (Y/N)> \
<limit to node server? (Y/N)> \
<limit to admin server? (Y/N)> \
<only patches that change DB? (Y/N)>
Specify 1 or 2 or 3 for query_depth
1-> Details of patches only
2-> Details of patches and their Bug Fixes only
3-> Details of patches their Bug Fixes and Bug Actions
The output would be generated in the current directory and would be named adfilerep.xml.
Example1: To get the complete patch details for patches applied in Oct 2009:
$ sqlplus apps @$AD_TOP/patch/115/sql/adphrept.sql 3 ALL ALL 10/01/09 10/31/09 ALL ALL ALL ALL ALL N N N N N
SQL*Plus: Release 8.0.6.0.0 - Production on Thu Nov 26 01:41:59 2009
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Please be patient, this will take a very long time.
If you are prompted for any parameters, please exit and review the parameter list you have submitted.
Gathering data for report. Report output will be written to adfilerep.xml.
Writing data to report file.
Your AD Patch History XML Report is ready at adfilerep.xml.
Note:Please copy the file to OA_HTML top to view it thru browser
Example2: To find whether bug number 6502082 is applied or not.
$ sqlplus apps @$AD_TOP/patch/115/sql/adphrept.sql 1 6502082 all all all all all all all all n n n n n
SQL*Plus: Release 8.0.6.0.0 - Production on Thu Nov 26 01:22:31 2009
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
This report should take less than a minute.
If you are prompted for any parameters, please exit and review the parameter list you have submitted.
Gathering data for report. Report output will be written to adfilerep.xml.
Writing data to report file.
Your AD Patch History XML Report is ready at adfilerep.xml.
Note:Please copy the file to OA_HTML top to view it thru browser
Method 3
You might also use the following query, however methods 1 and 2 are more reliable.
SQL> 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 ('<XXXXXX>','<YYYYYY>')
ORDER BY 1 DESC;
A modified version of the above query is as below:
SELECT DISTINCT a.bug_number, e.patch_name,
TRUNC(c.end_date) end_date, b.applied_flag 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_number1','&bug_number2')
ORDER BY 1 DESC;
Note: Please enter Patch number in place of
Method 4
In multi-node environment you are advised to use Patch Query from section "Checking the Patch Requirements on each Appl_Top" of the Metalink Note:364439.1 - Tips and Queries for Troubleshooting Advanced Topologies.
set serveroutput on
DECLARE
TYPE p_patch_array_type is varray(10) of varchar2(10);
--
p_patchlist p_patch_array_type;
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
CURSOR alist IS
select appl_top_id, name
from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.enable;
dbms_output.put_line(msg);
end;
--
BEGIN
open alist;
--
p_patchlist:= p_patch_array_type('3240000','3460000','4204335','4125550','3942483','4733943');
--
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 ':');
for i in 1..p_patchlist.count
loop
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,p_patchlist(i));
println('..Patch ' p_patchlist(i) ' was ' p_patch_status);
end loop;
END if;
println('.');
END LOOP;
--
close alist;
END;
/
A modification of the above query to find whether a single patch is applied or not:
-- Query to find whether a single patch was applied or not.
set serveroutput on;
DECLARE
p_appltop_name varchar2(50);
p_patch_status varchar2(15);
p_appl_top_id number;
--
cursor atoplist is
select appl_top_id, name from ad_appl_tops;
--
procedure println(msg in varchar2)
is
begin
dbms_output.enable;
dbms_output.put_line(msg);
end;
--
BEGIN
open atoplist;
--
LOOP
FETCH atoplist INTO p_appl_top_id,p_appltop_name;
EXIT WHEN atoplist%NOTFOUND;
--
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name ':');
p_patch_status := ad_patch.is_patch_applied('11i',p_appl_top_id,&bug);
println('..Patch ' &bug ' was ' p_patch_status);
END if;
println('.');
END LOOP;
--
close atoplist;
END;
/
References:
1. Metalink (MOS) Note: 443761.1 - How to check if a certain Patch was applied to Oracle Applications instance?
2. Metalink (MOS) Note: 181665.1 - Release 11i Adpatch Basics
3. Metalink (MOS) Note: 364439.1 - Tips and Queries for Troubleshooting Advanced Topologies
Comments