Being an Apps DBA patching is our bread and butter. And they way we check it make the difference. We had one issue and I was going through metalink note : 364439.1, which had this query. So thought of sharing with you all. Here you go:
===================================================
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;
/
===========================================================
Sample Output
---------------------------
Server1:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was EXPLICIT
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was NOT_APPLIED
..Patch 4733943 was EXPLICIT
.
.
Server2:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was NOT_APPLIED
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was EXPLICIT
..Patch 3942483 was EXPLICIT
..Patch 4733943 was NOT_APPLIED
.
.
Server3:
..Patch 3240000 was NOT_APPLIED
..Patch 3460000 was NOT_APPLIED
..Patch 4204335 was NOT_APPLIED
..Patch 4125550 was NOT_APPLIED.
.Patch 3942483 was NOT_APPLIED
..Patch 4733943 was NOT_APPLIED
---------------
Happy Troubleshooting !!!
Monday, April 20, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment