Oracle OEM ETL throubleshooting

Version 7
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    TrueSight Capacity Optimization


    Capacity Optimization



    Added OEM ETL, tested connection, everything appears to be working but no data is loading. What am I missing?





    Legacy ID:KA420107


    After checking the ETL logs it looks like the ETL is not getting any information from the OEM database.

    1. Was the user used by the ETL created using the OEM interface and has the VIEW ALL profile, or at least has access to the views SYSMAN.MGMT$METRIC*?
    3. If the user was created using the OEM interface and has the correct profile/access, is the OEM interface showing data?
    5. If the OEM interface is showing data, please connect to the OEM database with the user used in the ETL and run the following queries and check if they return any result? If no, probably the issue is OEM side     
      • SELECT target_name AS target_name, target_type AS target_type, target_guid AS guid, timezone_region AS tz, display_name AS display_name, last_metric_load_time AS last_metric_load_time FROM SYSMAN.MGMT$TARGET WHERE target_type IN ('oracle_database','rac_database','host','cluster');
      • select * from SYSMAN.MGMT$TEMPLATE_METRICCOLLECTION where rownum<1;
      • select target_type,metric_name,schedule_ex from SYSMAN.MGMT$TEMPLATE_METRICCOLLECTION where target_type IN ('oracle_database','rac_database','host','cluster');
    7. If a whitelist is implemented, make sure the regex pattern used can pick the systems needed to. This is key to import data, if this is not well implemented the ETL may result in empty Datasets thus no data will be loaded.

    Note: in the first query, you can optionally add and target_guid=??: if you geht the GUID from the lookup of one system, you can understand if it has data in the source: it happens sometimes that the OEM agent running on a server stop to collect data and in this way, you can check if this is the problem and so need to be investigate OEM side and not CO side.

    If the problem is that one system or a subset of systems are not more reporting data, you can use this query

    SELECT target_guid,   TO_CHAR( mmh.rollup_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS ts,   metric_name,   metric_column,   average,   3600 AS duration,   key_value,   key_value2,   key_value3,   key_value4,   key_value5 FROM SYSMAN.mgmt$metric_hourly mmh WHERE mmh.rollup_timestamp >= to_date('2016-12-10 00:00:00','YYYY-MM-DD HH:MI:SS') AND mmh.rollup_timestamp    < SYSDATE AND mmh.rollup_timestamp    > TO_DATE('201611','YYYYMM') AND mmh.target_guid         = 'xxx' AND mmh.metric_column      IN ('total_free','total_unallocated','shared_free_pct','diskActivWritesBlks','pgScanRate','disks_unallocated','DiskActivitybusy','db_used','DiskActivityavrun','commits_ps','large_free_pct','cpuusage_ps','ALLOCATED_GB','consistentreadchanges_ps','transactions_ps','totalErrorRate','dumpTotal','spaceAllocated','bufcachehit_pct','usercalls_ps','totalThroughput','dbblkgets_ps','summarized_dbs','size','cpu_time_pct','db_free','summarized_hosts','cpuLoad','usedLogicalMemoryPct','diskActivWritesPerSec','physwrites_ps','archAvail','logons_ps','physreads_ps','longestServ','cpuload','consistentreadgets_ps','archUsed','DiskActivityavserv','dumpUsedPercent','archTotal','writeRate','archUsedPercent','cpuKernel','cpuIOWait','pctAvailable','memUsedPct','logons','diskActivReadsBlks','softparse_pct','libcache_hit_pct','DiskActivityreadwrite','dumpAvail','total_used','cpuUtil','spaceUsed','noOfProcs','dumpUsed','DiskActivityavwait','readRate','PagingActivityppgout','java_free_pct','dbblkchanges_ps','parses_ps','dictionaryhit_pct','pctUsed','activeMem','diskActivReadsPerSec','totIO','redowrites_ps','summarized_asm','totalRate','sortsdisk_ps','indxscansfull_ps','executions_ps','totiosmade','tabscanslong_ps','USED_GB','swapUtil','cpuUser','disks_allocated') ORDER BY ts,   metric_name,   metric_column,   key_value,   key_value2,   key_value3,   key_value4,   key_value5;

      Note: replace xxx for target_guid with the OGUID of one of the problematic system (it is the value in the lookup table).
    Normally, when a system or a subset is no more reporting data, the cause is the OEM agent that stop to collect data.


    Article Number:


    Article Type:

    Solutions to a Product Problem

      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles