So cloud control - what's my database PSU?

Keeping track in an automated way of all the database PSU versions applied across the entire database estate can be a little tricky and time consuming (or both). Cloud control has the facility to be able to give us all this information - however what is available by default is not perfect for a few of reasons:

1) Anything 'inventory' related seems to be part of extra cost option packs  - we don't have these packs and i guess most shops don't - this is pretty cost prohibitive to try and use
2) Querying the underlying tables/views that contain the data used in the cloud control screen is possible - but again this may require a licence - I'm not sure of the exact details of what does and doesn't need a licence. Maybe even looking at the data requires a licence (e.g. AWR/ASH inside the database for example)
3) Even if you have the licences the screen may not do exactly what you want and repository tables/views are not the easiest to work with. I couldn't also find a simple list of the PSU's to be able to use that for reporting purposes (that's not to say it isn't there - i just couldn't find it)

Anyway to collect this information bypassing all of the above issues - i decided to collect the information by myself using metric extensions and then build a report based of that collected information.

I've shown before how to make use of metric extensions but I'll document it again here as I did this one with plsql rather than straight SQL and it was a little tricky to get correct - so i think it may be useful for others.

1) First up we go to Enterprise->Monitoring->metric extensions and fill in some basic details (this is a view after i created it rather than while i was doing it - but you can see the relevant details)

2) Now we get onto the second screen (which is where i had all the trouble). Here i had to define some plsql that would return the current PSU in the database - the approach i came up with seems to work in every case i tried it on - but I can;t guarantee it's 100% correct.......

 To help anyone who wants to copy this method here is the plsql in text format

declare
  v_version  varchar2(80);
  v_comp     varchar2(80);
  complete_v varchar2(80);
  l_output1  number;
  l_output2  varchar2(80);
begin
  dbms_utility.db_version(v_version, v_comp);
  OPEN :1 for
    select 1, maxer
      from (select max(psus) as maxer
              from (select replace(comments, 'PSU ', '') as psus
                      from sys.registry$history
                     where comments like 'PSU%'
                       and replace(comments, 'PSU ', '') > v_version
                    union
                    select v_version as psus from dual));
  --dbms_output.put_line(complete_v);
end;

Make sure to set up the bind variables exactly as i have them to get it to work.

3) now we map the return columns

4) Now we use default credentials (dbsnmp unless you changed it), test if necessary and then finish up and create deployable draft.

Once it's deployable , we then need to publish it and assign it to as many hosts as we see fit - this can be manually selecting all hosts or assigning it to a monitoring template which is auto deployed.

So now we have the PSU version being collected and uploaded by the agent every day - we now want to be able to join to that data to create some reports.

You can see the real time value straight away by browsing to the all metrics section of cloud for a particular database - the metric extensions stand out as they have a little logo next to them

So we can see above the database in question is version 11.2.0.2.11.

Right now lets find where these values are stored in the OMR. Now this took me a while to track down (and i know the tables reasonably well) - but that's a one off activity.

But I've done the hard bit for you - the metric values (for string metrics at least) can be found in this view (and I'm using the _LATEST version of it) and i'm joining this to the main targets table

SELECT target_name, value
    FROM sysman.GC_METRIC_STR_VALUES_latest mv, MGMT_TARGETS MT
   WHERE ENTITY_GUID = MT.Target_Guid
     AND METRIC_COLUMN_NAME = 'PSU_VERSION'

If we then do another couple of joins we can link it to get an overall summary of some of the basics

with psus as
 (SELECT target_name, value
    FROM sysman.GC_METRIC_STR_VALUES_latest mv, MGMT_TARGETS MT
   WHERE ENTITY_GUID = MT.Target_Guid
     AND METRIC_COLUMN_NAME = 'PSU_VERSION')
select d.target_name,
       d.host_name,
       --d.characterset,
       psus.value,
       --d.startup_time,d.release,
       d.dbversion
--d.is_64bit,os.os_summary,os.freq,os.mem,os.disk,os.cpu_count
  from MGMT$DB_DBNINSTANCEINFO d, MGMT$OS_HW_SUMMARY os, psus
 where os.host_name = d.host_name
   and psus.target_name = d.target_name

I've commented out a few lines here as i just wanted to compare information from the version column against the target with that returned from my metric - but you can add in whatever is useful.

So running the query above returns this data

So there we go - a relatively easy way to get a list of the PSU versions in every database in your estate - and it's automatically updated - and it even keeps history.......

Footnote - the data may not fill in immediately (I'm not sure when the rollup of the data into the tables/views is done to make them queryable) - but if you waut 24 hours everything will definitely be there.