Sunday, February 13, 2011

Querying EM Grid Control data

EM Grid Control provides lots of benefits in  the enterprise. Most people discuss Grid Control in the context if Diagnostic Packs and Tuning Packs which Grid Control has a nice interface to, but Grid Control  has  a wealth of other information  built into it.
I was recently asked by a colleague to help him find all the Listener ports that were being used in the Enterprise .
While Grid Control Doesn't provide a default  report for this . All the data is available in the Grid Control repository. A Simple Table Based report based on the below query can be used to get that data

SELECT mgmt$target.host_name,mgmt$target.target_name,mgmt$target.target_type,mgmt$target_properties.property_name,mgmt$target_properties.property_value
FROM mgmt$target, mgmt$target_properties
WHERE (mgmt$target.target_name = mgmt$target_properties.target_name)
AND (mgmt$target.target_type = mgmt$target_properties.target_type)
and (mgmt$target.target_type='oracle_listener')
and (mgmt$target_properties.property_name='Port');

The Above SQL Will Display all Listener's and their ports.
the MGMT$target and mgmt$target_properties can provide and great deal of other options and reports based on Block Sizes and other parameters set can be used to quickly find issues in your enterprise.
P.S The above query is based on the Grid Control 10.2.0.5 Repository

Google Search