How to query OEM Sysman Repository to find some valuable information




Querying Oracle Management Repository (OEM repository) to dig out for a valuable informations 

How to find Availiable Target Types in OEM




SQL> elect distinct target_type,type_display_name from mgmt_targets order by 1;

Target Type	Display Name
cluster	Cluster
composite	Group
has	Oracle High Availability Service
host	Host
j2ee_application	Application Deployment
j2ee_application_domain	Domain Application Deployment
metadata_repository	Metadata Repository
oracle_apache	Oracle HTTP Server
oracle_beacon	Beacon
oracle_cloud	Cloud
oracle_database	Database Instance
oracle_dbmachine	Oracle Database Machine
oracle_dbsys	Database System
oracle_em_service	EM Service
oracle_emd	Agent
oracle_emrep	OMS and Repository
oracle_emsvrs_sys	EM Servers System
oracle_exadata	Oracle Exadata Storage Server
oracle_exadata_dbsys	Oracle Database Exadata Storage Server System
oracle_exadata_grid	Oracle Exadata Storage Server Grid
oracle_home	Oracle Home
oracle_listener	Listener
oracle_oms	Oracle Management Service
oracle_oms_console	OMS Console
oracle_oms_pbs	OMS Platform
oracle_pdb	Pluggable Database
oracle_si_fabric	Ethernet/InfiniBand Fabric
oracle_si_host_remote	Systems Infrastructure Remote Host
oracle_si_netswitch	Systems Infrastructure Switch
oracle_si_network	Systems Infrastructure Network
oracle_si_pdu	Systems Infrastructure PDU
oracle_si_rack	Systems Infrastructure Rack
oracle_si_server	Oracle Server
oracle_si_server_map	Systems Infrastructure Server
oracle_si_virtual_platform	Oracle Virtual Platform
oracle_si_virtual_server	Oracle VM Instance
oracle_vm_cloud	Oracle VM Cloud
osm_cluster	Cluster ASM
osm_instance	Automatic Storage Management
rac_database	Cluster Database
weblogic_domain	Oracle WebLogic Domain
weblogic_j2eeserver	Oracle WebLogic Server
weblogic_nodemanager	Oracle WebLogic Node Manager

How to find all registered targets in OEM for a particular target type

So I want to find all the targets registered in OEM repository whose target type is "rac_database"

in other words, basically I want to find all the rac databases registered in my OEM.

SQL> select target_name,target_type,target_guid from mgmt_targets where target_type='Cluster Database';

TargetName	TargetType	TargetGUID
MTP1P	rac_database	237E5C0EB79B5453C77AC7DF1814F9C7
MTP2P	rac_database	455C25441A577694E0537C01840A1B7A
MT1D	rac_database	45CE40C9DA0E2497E0537C01840A7DC0
AND1D	rac_database	46A5236E79CAE7DC821F3BD08D4B4DBC
AND2D	rac_database	49BA387D72AB7BED6C800968C83B1334
ORCL1P	rac_database	503190DF8F9B6E05E0537C01840A255C

How to find a particular target information registered in OEM

SQL> select * from mgmt_targets where target_name='MT1D';

How to find Importent Matrics related to a particular Target

SQL> select * from mgmt$metric_daily where target_name = 'MT1D' and trunc(rollup_timestamp) = trunc(sysdate)-1;

How to find the daily growth of a database from OEM repository

SQL> select rollup_timestamp, average
from mgmt$metric_daily
where target_name = 'MT1D'
and column_label = 'Used Space(GB)'
order by rollup_timestamp;

Rollup_Timestamp              Average DB Size in GB
20-DEC-16                          18317.2
21-DEC-16                          18343.82
22-DEC-16                          18366.56
23-DEC-16                          18488.34
24-DEC-16                          18419.59