Using Grid Control Repository for RDBMS Patch Report
By Martin | May 19th, 2011 | Category: Oracle Database, Oracle Enterprise Manager | No Comments »I was looking for a method to utilize the Grid Control Repository, which contains information about installed Oracle Homes, databases and patches, for a patch report.
With a little reverse engineering i came up with these relevant tables:
- Mgmt_Ecm_Snapshot: Every time the inventory is refreshed, a new line is inserted into this table containing the host name and the snapshot_guid. The most current snapshot has flag IS_CURRENT set to ‘Y’
- Mgmt_Inv_Container: Every Oracle Home is a container. This table contains the snapshot_guid and the container_guid along with a container_description which is basically the Oracle Home Path
- Mgmt_Inv_Patch: This table contains container_guid, Patch ID and patch installation timestamp
- Mgmt_Inv_Component: This table lists all the components of the oracle homes along with their version. There is one component per container with the flag Is_Top_Level set to ‘Y’. We use this component for getting the base version of the installed product. (e.g. 11.2.0.2)
- Mgmt_Inv_Versioned_Patch: I am not sure if this table is needed for version information, but one of the mgmt views was using these two tables together, so I used it as a reference.
The complete statement now is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | CREATE OR REPLACE FORCE VIEW "SYSMAN"."RDBMS_PATCH_REPORT" AS SELECT N.Target_Name, S.Start_Timestamp AS Collected_Time, S.Target_Name AS Host_Name, C.Container_Name AS Oracle_Home_Name, Container_Location, P.Id AS Patch_Id, ( CASE Id WHEN '10157506' THEN 'GI Bundle1' WHEN '10185523' THEN 'OWB Bundle' WHEN '10248523' THEN 'PSU Jan 2011' WHEN '11724916' THEN 'PSU Apr 2011' WHEN '12311357' THEN 'GI Psu Apr 2011' ELSE NULL END ) description, P.Timestamp AS Install_Time, CASE WHEN VP.version IS NULL THEN M.version ELSE VP.version END AS Version FROM Mgmt_Ecm_Snapshot S, Mgmt_Inv_Container C, Mgmt_Inv_Patch P, (SELECT T.Target_Guid, T.Host_Name, T.Target_Name, T.Target_Type, Mp.Property_Value AS Oh FROM Mgmt_Targets T, Mgmt_Target_Properties Mp WHERE T.Target_Guid = Mp.Target_Guid AND Mp.Property_Name = 'OracleHome' AND Target_Type = 'oracle_database' ) N, Mgmt_Inv_Component M , MGMT_INV_VERSIONED_PATCH VP WHERE S.Snapshot_Guid = C.Snapshot_Guid AND S.Is_Current = 'Y' AND C.Container_Type = 'O' AND P.Container_Guid = C.Container_Guid AND N.Host_Name = S.Target_Name AND N.Oh = C.Container_Location AND M.Component_Guid = Vp.Component_Guid(+) AND M.Is_Top_Level = 'Y' AND M.Container_Guid = C.Container_Guid ORDER BY 1,2,3; |