Skip to Content

Database troubleshooting

  • To check Oracle scheduled jobs, ensure that no job is broken, and the next scheduled run is showing correct value where some jobs are scheduled for hourly run and some jobs are for once-a-day run by running the following query:

    select to_char(next_date,'dd-mm-yyyy hh24:mi:ss')||', '||broken||', '|| what from user_jobs order by next_Date;
  • To check data collection, run the following query which will display date, time, number of data points collected for that hour, number of distinct devices, and number of distinct attributes from the previous day:

    select to_char(trunc(TIMERECORDED,'HH24'),'dd-Mon-yyyy hh24:mi:ss'),count(*), count(distinct HOSTID), count(distinct ATTRIBUTEID) from stats_data where timerecorded> trunc(sysdate-1) group by trunc(TIMERECORDED,'HH24') order by trunc(TIMERECORDED,'HH24');
  • The following query displays data from Hourly data table. If the number of devices and attributes are less than the above query, then it shows that the hourly summarization job is not working.

    select to_char(TIMERECORDED,'dd-mm-yyyy hh24:mi:ss'),count(*), count(distinct HOSTID), count(distinct ATTRIBUTEID) from hourly_data where timerecorded> trunc(sysdate-1) group by TIMERECORDED order by TIMERECORDED;
  • The following query displays data from Daily data table. If the number of devices and attributes are less than the above query, then it shows that the daily summarization job is not working.

    select to_char(TIMERECORDED,'dd-mm-yyyy'),count(*), count(distinct HOSTID), count(distinct ATTRIBUTEID) from daily_data where timerecorded> trunc(sysdate-10) group by TIMERECORDED order by TIMERECORDED;
  • The following query lists all the attributes from which the report engine is collecting data. If you do not see attributes for which you want to collect data, then add them to BMC ProactiveNet Server under external report attribute set. 

    select ATTRIBUTESETNAME||', '|| ATTRIBUTENAME from externalreportattributeset order by ATTRIBUTESETNAME, ATTRIBUTENAME; 

  • To be able to get data in the reports in Oracle connection, perform the following steps:

    • Login to the RE Database using the reuniv DB user credentials.

    • Execute following command where redbuser is the RE Database user

      exec create_synonym_reuniv(‘redbuser’);



 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC ProactiveNet Performance Management Reporting 9.5