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;
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
