Oracle DBA – A lifelong learning experience

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"
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"
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.

Finally the report is scheduled to run on a fortnightly basis and save copies. This is from the schedule tab and the key thing is to tick the ‘Save copies of report’  box.

I hope that this note has been helpful and I am always happy to see how others are using the  OEM reporting capabilities.

3 Responses to “Using OEM reports to show PSU levels across the estate”

  1. Dan said

    Very Nice – thanks for sharing !

  2. CT said

    Nice Report. However, this would require updating each quarter when the PSUs are released. I am having a running battle in an Oracel SR now, trying to convince them that it is bad practice to have the database version ( select version from v$instance) showing as x.x.x.0 when Psu patches have been applied.

  3. […] […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: