POC: Customized Patch Reports for Grid Control

This is part of the Grid Control POC we recently arranged for a large client.


As per the earlier post on the issue of patch reports, the supplied out-of-box
reports show the patch, bugs fixed, installation time, Host, Home directory
and Platform. The reports do not show the databases, the reason being that
the patch is actually at the Oracle Home level and not at the database level.
If you wish to add in the database name, it is possible to modify the report by
creating a copy and then modifying the Sql statement. For example the
above report is from this statement that can be modified:


SELECT distinct
patch as PATCH,
bugs as BUGS,
installation_time as TIMESTAMP,
host as HOST,
home_location as HOME_DIRECTORY,
platform as PLATFORM
from mgmt$applied_patches patch,
mgmt$em_homes_platform home,
mgmt$target tgt
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)

You can join this to MGMT$target_components which would have the same home as the mgmt$applied_patches home,
and then select the targets that are databases in this home.

SELECT distinct
patch as PATCH,
installation_time as TIMESTAMP,
host as HOST,
tgtcomp.target_name,
tgtcomp.target_type,
patch.home_location as HOME_DIRECTORY,
patch.home_name as HOME_NAME,
platform as PLATFORM
from mgmt$applied_patches patch,
mgmt$em_homes_platform home,
mgmt$target tgt,
mgmt$target_components tgtcomp
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and tgtcomp.home_name = patch.home_name
and tgtcomp.target_type = 'oracle_database'
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)


This is just an example of the customization that is possible.


How do we know what info is in the Grid Control Repository? We recommend using the new Sql Developer
Data Modelling utility from Oracle. This is downloadable from the Oracle Technical Network (OTN) and currently
installs as a stand-alone utility, but will be incorporated into a new release of Sql Developer in the not-too-distant
future. ( Sql Developer keeps getting better and better, just like Grid Control ! )


The Data Modelling utility allows reverese engineering of an existing Oracle Database, you can access
this via the menu. Select File>Import>Data Dictionary. In this way, you can import form Oracle 9,10
and 11, MS SQL Server 2000 and 2005, DB2/390 7 and 8, DB2/UDB 7 and 8.


Use this to reverse engineer the SYSMAN schema in the Grid Control repository, especially the MGMT$ views.
Then you can understand what info is available.


As a test of the customization:

After applying database patch 7210195 via Grid Control to our FINPRD1 and FINDW1 database home,
we created a Grid Control customized report "Applied Interim Patches (Customized - Database Level)".


The Sql used in this report is:


SELECT distinct
patch as "Applied Interim Patch",
installation_time as "Time Applied",
tgtcomp.target_name as "Database Target Name",
patch.home_location as "Oracle Home Directory",
patch.home_name as "Oracle Home Name",
host as Host
from mgmt$applied_patches patch,
mgmt$em_homes_platform home,
mgmt$target tgt,
mgmt$target_components tgtcomp
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and tgtcomp.home_name = patch.home_name
and tgtcomp.target_type = 'oracle_database'
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
order by installation_time desc


As you can see, we have changed the headings of the field columns in the report, and we also changed the styled text in the
Report definition from “INSTR_APPLIED_PATCHES_ALL_HOSTS” to the customized line:


“The report shows the interim patches applied on Oracle Databases across all the hosts
in the last 31 days. Use the time period selector to view the interim patches applied within a time-period. Thanks.”


We also created a Report: "Applied Interim Patches (Customized - Database Level and Select which Database)" that does the above and also allows you to select which database.


The Sql statement for this is:


SELECT distinct
patch as "Applied Interim Patch",
installation_time as "Time Applied",
tgtcomp.target_name as "Database Target Name",
patch.home_location as "Oracle Home Directory",
patch.home_name as "Oracle Home Name",
host as Host
from mgmt$applied_patches patch,
mgmt$em_homes_platform home,
mgmt$target tgt,
mgmt$target_components tgtcomp
where home.HOME_ID = patch.CONTAINER_GUID
and patch.target_guid = tgt.target_guid
and tgtcomp.home_name = patch.home_name
and tgtcomp.target_type = 'oracle_database'
and tgtcomp.target_guid = ??EMIP_BIND_TARGET_GUID??
and patch.installation_time>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
and patch.installation_time<= MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,tgt.TIMEZONE_REGION)
order by installation_time desc


The line which selects the database target selected is “and tgtcomp.target_guid = ??EMIP_BIND_TARGET_GUID??”


Regards,


Porus Homi Havewala
(Oracle ACE Director)
Principal Consultant,
S & I Systems Pte Ltd.