Troubleshooting
Problem
API query returns nothing when there are duplicate entries in history table even though the data is available at GST and RST reports.
Symptom
When turn on the trace (Full DSO trace) using WAS console in the portal, we can find the below ORA- error messages in the trace.log
[4/1/14 15:00:18:063 MDT] 00000025 E PVRcDataSource reQuery() [WebContainer : 6] Parent Query = DVUnitRSTAllRows prepareStatment() failed: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row
Cause
Sometimes duplicate entries in the database causes internal database queries to error out. This causes missing data in the RST report. This could happen with the metrics from SAM pack which changes the current subelement and property inventory, and all of those changes get automatically reflected in the appropriate history table. The SAM pack also updates property values, and those get updated in the history table automatically. When inserts and updates to the table happen within the same second, we see duplicates in the database tables for the same resource and metric, one for insert and one for the update. The history table in question is the prop_desc_hist table. These duplicates need to be removed via a script so that SQL statements can run properly to get data.
Resolving The Problem
Run the attached SQL script file as database user pv_admin against the PV database. The script will simply output the rows that will be updated. It will not update them actually. After the output is validated by support team, the script can be invoked to actually update the rows so that timestamps are no longer same. The update can take 10 min or so depending on the size of data. Check the log in /tmp/resolve_updatedupes_PRDH.<tstamp>.log to see the outcome of the script.
Was this topic helpful?
Document Information
Modified date:
21 June 2018
UID
swg21670976