Using OEM reports to show PSU levels across the estate
Posted by John Hallas on March 30, 2011
The reporting capabilities of OEM are very good, although sometimes it is hard to find which views the data you want is held in. This post is about sharing how to build a report which details how many databases are at each PSU patch. It will also show how to schedule a repeating report and save the history of each run so that trending can be measured. Most ( if not all ) of the work was performed by Sarabjit Lotay (encouraged by superb leadership !!)
First lets look at the end result. I have had to take out specific detail from our estate but the report looks like this
The red blotches don’t help but you can see the general format and it does prove very useful. The next shot shows how the report is made up
The code for each piece of the report is shown below. SQL to show the number of rdbms homes, the number of ASM homes and how many homes have PSU 4 applied. Note that I am only interested in the 11GR1 databases on this report as we run a seperate one for 10g databases and we only have a few 11GR2 databases at the moment.
select count(*) "Number of Oracle 11g DB Homes" from MGMT$CPF_HOMES_INFO where (home_directory like '%11.1.0%db%' and home_version like '11.1%') or (home_directory like '%db%11.1.0%' and home_version like '11.1%') select count(home_host) "Number of Oracle 11g ASM Homes" from MGMT$CPF_HOMES_INFO where home_directory like '%11%asm%' and home_version like '11%' select count(*) "11g Patched at PSU 4" from mgmt$applied_patches ap, mgmt$target t where ap.target_guid=t.target_guid and patch in ('9654987') and t.target_guid = ap.target_guid
The way that the patches for a PSU on both ASM and rdbms are laid out side by side is from the layout tab of the general page of the report.
I hope that this note has been helpful and I am always happy to see how others are using the OEM reporting capabilities.