Database troubleshooting
This topic provides information on how to analyze and troubleshoot problems that you might experience when using BMC TrueSight Operations Management Report Engine with Oracle or SQL Server database:
Data collection stops
When using the Microsoft SQL Server database, if you give the sysadmin role to a Report Engine database user, you may encounter data collection failures.
To resolve this issue, remove the sysadmin role from the Report Engine database user and restart the Report Engine service. For more information, see the KA423826 Knowledge Article.
Java NullPointer Exception with DB Authentication Failure
When using the Microsoft SQL Server database, if the database login password is not set according to the password complexity policy, Report Engine may throw the Java NullPointer exception with DB authentication failure.
To avoid this issue, perform the following on SQL server:
- Navigate to Start > Control Panel > Administrative Tools > Local Security Policy.
- In the Local Security Policy console, expand Account Policies and select Password Policy.
- Ensure that the Password must meet complexity requirements option is Enabled.
Database troubleshooting queries
- 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:
- If you don't receive the daily_data and hourly_data in the database, check whether SQL Server Agent service or Oracle Scheduled job is running according to the database you are using.
- 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 reuniv.rpt_to_char(TIMERECORDED,'yyyy-mm-dd hh24:mi:ss'),count(*), count(distinct MOTYPEID), count(distinct ATTRIBUTEID) from hourly_data where timerecorded> reuniv.rpt_trunc(reuniv.rpt_sysdate()-1,'DD') 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 reuniv.rpt_to_char(TIMERECORDED,'dd-mm-yy'),count(*), count(distinct MOTYPEID), count(distinct ATTRIBUTEID) from daily_data where timerecorded> reuniv.rpt_trunc(reuniv.rpt_sysdate()-10,'DD') 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 TrueSight Infrastructure Management Server under external report attribute set.
select reuniv.rpt_concat(reuniv.rpt_concat(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’);
- To get the errors related to Oracle jobs, run the get_re_log_table_message.sql script available at %BMC_PROACTIVENET_REPORTENGINE_HOME%\ReportEngine\schema\developer\oracle.
- To get the error details related to the SQL Server job, run the following query and export the output in SQL Server Studio
select err_time ,err_msg from re_log with (nolock) order by id
Database jobs
Following are the various jobs scheduled in the Report Engine Database. You can use following schedules in case of any issues in job scheduling.
Database Edition | Type of job | Job Name (In SQL server, the job names are similar except it has the | Scheduled Time |
|---|---|---|---|
Standard Edition and Enterprise Edition | Every hour | update_dst_offset; | 14:00 |
Standard Edition and Enterprise Edition | Daily once | pr_analyze_config_tables; | 0:00 |
Standard Edition and Enterprise Edition | Daily once | RE_DAILY_JOB; | 21:30 |
Standard Edition and Enterprise Edition | Every hour | RE_HOURLY_JOB; | 14:00 |
Standard Edition and Enterprise Edition | Daily once | pk_purgedata.pr_purge_data; | 00:15 in Oracle. |
Enterprise Edition | Daily once | pk_updateSummaryTables_EE.FIND_NEW_DATA_IN_TABLE | 5:00 |
Standard Edition | Daily once | pk_updateSummaryTables.PR_HOURLYTODAILY; | 06:00 in Oracle. |
Enterprise Edition | Daily once | pr_scheduled_Add_partitions; | 0:15 |
Standard Edition and Enterprise Edition | Every hour | pk_updateSummaryTables.PR_STATSTOHOURLY; | 14:00 |
Standard Edition and Enterprise Edition | Daily once | pk_create_thresholds.pr_call_CREATE_ALL_THRESHOLDS; | 5:00 |
Standard Edition and Enterprise Edition | Every hour | MV_REFRESH_VW_CONFIGDATA; ( Applicable to Only Oracle) | 14:00 |
Standard Edition and Enterprise Edition | Daily once | pk_updateSummaryTables.MV_CREATEANDREFRESH; | 0:00 |
- 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 SQL Server scheduled jobs, perform the following:
- Access the Microsoft SQL Server Management Studio.
- Log in to the report engine database with valid credentials.
- In Object Explorer, expand SQL Server Agent.
- In SQL Server Agent, expand the Jobs folder. All jobs for the report engine database are displayed.
- Right click on the Jobs folder and select View History. The Log file viewer is displayed.
- In Log file viewer, ensure that all report engine jobs are displayed as green and message as The job succeeded.