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

1 comment: