Monday, April 20, 2009

Better way to check Applied Patches

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 !!!

No comments: