Query to check CPU patch level

Query to check CPU patch level

After moving from CPU patches to PSU patches for Database, we no longer get the CPU information from the query:

SQL> select comments from registry$history;

SQL> select comments from registry$history;

COMMENTS
--------------------------------------------------------------------------------
CPUOct2008
CPUApr2008
view recompilation
Upgraded from 10.2.0.3.0
CPUJan2009
CPUJul2009
PSU 10.2.0.4.1
CPUOct2009
PSU 10.2.0.4.2
CPUJul2010
CPUOct2010
view invalidation
Upgraded from 10.2.0.4.0
Patchset 11.2.0.2.0
CPUApr2011
CPUJul2011
CPUOct2011
PSU 11.2.0.2.5

18 rows selected.

If you notice it only shows Patchset 11.2.0.2.5 instead of having two rows one for PSU and one for Jan 2012 CPU which is included in the PSU.

We always apply all CPU patches relevant to our techstack, which usually means the database patches applied with opatch and EBS patches applied with adpatch.  So I created a query based on the EBS patch numbers for CPUs from April 2011 - July 2012 CPU:

column BUG format a8;
column PATCH format a60;
set linesize 100;
set pagesize 200;
select b.bug_number BUG, b.LAST_UPDATE_DATE LDATE, decode(bug_number,
13979374, 'July 2012 CPU patch for 11i+RUP7',
13979377, 'July 2012 CPU patch for 11i+RUP6',
13979372, 'July 2012 CPU patch for R12.1+ATG_PF.B.Delta3',
13979375, 'July 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621942, 'April 2012 CPU for R12.1+ATG_PF.B.Delta2',
13621941, 'April 2012 CPU for R12.0+ATG_PF.A.Delta6',
13621940, 'April 2012 CPU for 11i+RUP7',
13621939, 'April 2012 CPU for 11i+RUP6',
13322561, 'Jan 2012 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Jan 2012 CPU for R12.0+ATG_PF.A.Delta6',
13322559, 'Jan 2012 CPU for 11i+RUP7',
13322557, 'Jan 2012 CPU for 11i+RUP6',
12794417, 'Oct 2011 CPU for R12.1+ATG_PF.B.Delta2',
12794416, 'Oct 2011 CPU for R12.0+ATG_PF.B.Delta6',
12794415, 'Oct 2011 CPU for 11i+RUP7',
12794414, 'Oct 2011 CPU for 11i+RUP6',
12406916, 'Jul 2011 CPU for R12.1',
12406915, 'Jul 2011 CPU for R12.0',
12406914, 'Jul 2011 CPU for 11i+RUP7',
12406913, 'Jul 2011 CPU for 11i+RUP6',
11660357, 'Apr 2011 CPU for R12.1',
11660356, 'Apr 2011 CPU for R12.0',
11660355, 'Apr 2011 CPU for 11i+RUP7',
11660354, 'Apr 2011 CPU for 11i+RUP6'
) PATCH
from APPS.AD_BUGS b
where b.BUG_NUMBER in ('13979374','13979377','13979372','13979375','13621942','13621941','13621940',
'13621939','13322561','12794416','13322559','13322557','12794417','12794416','12794415','12794414',
'12406916','12406915','12406914','12406913','11660357''11660356','11660355','11660354')
order by patch;

Result on the same instance:
BUG      LDATE           PATCH
-------- --------------- ------------------------------------------------------------
11660355 20-AUG-11       Apr 2011 CPU for 11i+RUP7
13322559 12-MAY-12       Jan 2012 CPU for 11i+RUP7
12406914 19-NOV-11       Jul 2011 CPU for 11i+RUP7
12794415 11-FEB-12       Oct 2011 CPU for 11i+RUP7