Hi Victor,
Since upgrading NetXMS server on Solaris from RC version to 2.0.2 I observe following frequently problem:
[24-Feb-2016 10:33:53.520] [ERROR] SQL query failed (Query = "UPDATE raw_dci_values SET raw_value=?,transformed_value=?,last_poll_time=? WHERE item_id=?"): ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
[24-Feb-2016 10:33:53.520] [ERROR] SQL query failed (Query = "INSERT INTO idata_1899 (item_id,idata_timestamp,idata_value) VALUES (?,?,?)"): ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
On the Oracle Db side my observation is following:
SID SQL_TEXT OPEN_CURSORS USER_NAME
---------- ------------------------------------------------------------ ------------ ------------------------------
213 INSERT INTO dct_threshold_conditions (threshold_id,group_id, 299 NETXMSOWNER
202 INSERT INTO dct_threshold_conditions (threshold_id,group_id, 299 NETXMSOWNER
203 INSERT INTO dct_threshold_conditions (threshold_id,group_id, 299 NETXMSOWNER
211 INSERT INTO dct_threshold_conditions (threshold_id,group_id, 299 NETXMSOWNER
201 INSERT INTO dct_threshold_conditions (threshold_id,group_id, 299 NETXMSOWNER
203 insert into sys.aud$( sessionid,entryid,statement,ntimestamp 1 NETXMSOWNER
203 SELECT item_id FROM raw_dci_values WHERE item_id=223 1 NETXMSOWNER
203 SELECT threshold_id FROM thresholds WHERE item_id=126 1 NETXMSOWNER
202 SELECT threshold_id FROM thresholds WHERE item_id=21 1 NETXMSOWNER
202 INSERT INTO dct_thresholds (id,table_id,sequence_number,acti 1 NETXMSOWNER
203 SELECT threshold_id FROM thresholds WHERE item_id=123 1 NETXMSOWNER
203 DELETE FROM dc_table_columns WHERE table_id=:1 1 NETXMSOWNER
203 SELECT item_id FROM raw_dci_values WHERE item_id=222 1 NETXMSOWNER
202 DELETE FROM dci_schedules WHERE item_id=9 1 NETXMSOWNER
202 DELETE FROM dci_schedules WHERE item_id=48 1 NETXMSOWNER
203 UPDATE dc_tables SET node_id=:1,template_id=:2,template_item 1 NETXMSOWNER
203 DELETE FROM dci_schedules WHERE item_id=121 1 NETXMSOWNER
203 DELETE FROM dci_schedules WHERE item_id=218 1 NETXMSOWNER
203 UPDATE items SET node_id=:1,template_id=:2,name=:3,source=:4 1 NETXMSOWNER
203 INSERT INTO dc_table_columns (table_id,sequence_number,colum 1 NETXMSOWNER
203 SELECT threshold_id FROM thresholds WHERE item_id=219 1 NETXMSOWNER
202 SELECT item_id FROM raw_dci_values WHERE item_id=21 1 NETXMSOWNER
203 SELECT item_id FROM raw_dci_values WHERE item_id=117 1 NETXMSOWNER
202 DELETE FROM dci_schedules WHERE item_id=73 1 NETXMSOWNER
SQL_FULLTEXT
--------------------------------------------------------------------------------
INSERT INTO dct_threshold_conditions (threshold_id,group_id,sequence_number,column_name,check_operation,check_value) VALUES (:1,:2,:3,:4,:5,:6)
"INSERT INTO dct_threshold_conditions" is opening maximum allowed number of cursors and blocking further operations.
This seems to be cursor memory leak. Application is opening a cursor and not closing it when it's done.
How to solve this issue? This problem is blocking connections (login-in) from Management Console few hours after restart of NetXMS server.
Regards,
Marcin
Hello,
NetXMS server 2.0.2 is not usable. It's blocked few hours after every restart:
[04-Mar-2016 08:19:47.926] [ERROR] SQL query failed (Query = "INSERT INTO idata_280 (item_id,idata_timestamp,idata_value) VALUES (?,?,?)"): ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
As a result of ORA-01000 it's not possible to login using NetXMS console or if this is possible, other actions are blocked, for example checking details of Alarm (see attached screenshot).
Increasing number open cursors from 300 to 400 does not help.
Best regards,
Marcin
Hi,
I've fixed the issue. Fix will be included into 2.0.3 release, or I can build patched 2.0.2 for you.
Best regards,
Victor
Hi Victor,
Thank you very much for fixing the issue.
When release 2.0.3 will be available?
It would be nice to have a patched build for Solaris. We will replace binary file immediately.
As a workaround we disabled most of the DCI where thresholds were used and this slowed down the process of using cursors on dct_threshold_conditions table.
Now NetXMS server is usable for 6-8 hours after restart.
Best regards,
Marcin
Hi,
I plan to make 2.0.3 release this Friday. I'll try to build patched binary later today.
Best regards,
Victor
Hi,
please try to replace existing libnxcore.so.2.0.0 with this one: https://cloud.netxms.org/index.php/s/XzmBjTnJ0E0zlDV
Best regards,
Victor
Thank you Victor.
Library was replaced and this solved the problem.
Best regards,
Marcin