Netxms OracleDB errors
-
Hello,
After upgrading NetXMS from 1.2.16 to 2.0-M2 I see following errors in the netxms log file:
[12-Feb-2015 14:09:43.618] [ERROR] SQL query failed (Query = "INSERT INTO tdata_records_822 (record_id,row_id,instance) VALUES (?,?,?)"): ORA-00001: unique constraint (NETXMSOWNER.SYS_C0040248) violated
[12-Feb-2015 14:09:43.618] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:10:43.645] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:10:48.485] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:14:17.166] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:14:18.103] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:15:43.556] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:15:48.396] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:19:18.031] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:19:19.029] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:20:43.484] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:20:48.308] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:24:18.945] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:24:19.955] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:25:43.396] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:25:48.219] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:29:19.867] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:29:20.867] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:30:43.325] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:30:48.128] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:34:20.858] [ERROR] Invalid object identifier 116 in event processing policy
[12-Feb-2015 14:34:21.791] [ERROR] Invalid object identifier 116 in event processing policyand following errors when running check on the database:
[root] $ /opt/netxms/bin/nxdbmgr check
NetXMS Database Manager Version 2.0-M2 Build 8007Checking database (excluding collected data):
* Checking zone objects... [PASSED]
* Checking node objects... [PASSED]
* Checking interface objects... [PASSED]
* Checking network service objects... [PASSED]
* Checking cluster objects... [PASSED]
* Checking template to node mapping... [PASSED]
* Checking object properties... [PASSED]
Invalid object ID 116 used in policy. Delete it from policy? (Y/N) y
* Checking event processing policy... [FIXED ]
* Checking network map links... [PASSED]
Data collection table (TDATA) for node [110] not found. Create? (Y/N) (Y/N) y
SQL query failed (ORA-02270: no matching unique or primary key for this column-list):
CREATE TABLE tdata_rows_110 (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_110(row_id) ON DELETE CASCADE)
Data collection table (TDATA) for node [192] not found. Create? (Y/N) (Y/N) y
SQL query failed (ORA-02270: no matching unique or primary key for this column-list):
CREATE TABLE tdata_rows_192 (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_192(row_id) ON DELETE CASCADE)
Data collection table (TDATA) for node [194] not found. Create? (Y/N) (Y/N) y
SQL query failed (ORA-02270: no matching unique or primary key for this column-list):
CREATE TABLE tdata_rows_194 (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_194(row_id) ON DELETE CASCADE)During every execution of nxdbmgr the same errors occurs (also for Invalid object ID 116).
Could help in resolving this issue?
Best regards,
Marcin -
Hi,
please send me output of query
SELECT * FROM metadata
from your database. For now, try to answer y only to invalid object fix - that way it will fix at least this problem.
Best regards,
Victor -
Below is the output:
VAR_NAME VAR_VALUE
---------------------------------------------------------------
LocationHistory
CREATE TABLE gps_history_%d (latitude varchar(20), longitude varchar(20), accuracy integer not null, start_timestamp integer not null, end_timestamp integer not null, PRIMARY KEY(start_timestamp))SchemaVersion
344Syntax
ORACLEIDataTableCreationCommand
CREATE TABLE idata_%d (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) null)IDataIndexCreationCommand_0
CREATE INDEX idx_idata_%d_id_timestamp ON idata_%d(item_id,idata_timestamp DESC)TDataTableCreationCommand_0
CREATE TABLE tdata_%d (item_id integer not null,tdata_timestamp integer not null,record_id number(20) not null,UNIQUE(record_id))TDataTableCreationCommand_1
CREATE TABLE tdata_records_%d (record_id number(20) not null,row_id number(20) not null,instance varchar(255) null,PRIMARY KEY(record_id),FOREIGN KEY(record_id) REFERENCES tdata_%d(record_id) ON DELETE CASCADE)TDataTableCreationCommand_2
CREATE TABLE tdata_rows_%d (row_id number(20) not null,column_id integer not null,value varchar(255) null,PRIMARY KEY(row_id,column_id),FOREIGN KEY (row_id) REFERENCES tdata_records_%d(row_id) ON DELETE CASCADE)TDataIndexCreationCommand_0
CREATE INDEX idx_tdata_%d_id_timestamp ON tdata_%d(item_id,tdata_timestamp)TDataIndexCreationCommand_1
CREATE INDEX idx_tdata_rec_%d_instance ON tdata_records_%d(instance)10 rows selected.
-
-
Hi,
seems that metadata is incorrect for some reason. Try to stop server and execute the following query:
UPDATE metadata SET var_value='CREATE TABLE tdata_records_%d (record_id number(20) not null,row_id number(20) not null,instance varchar(255) null,PRIMARY KEY(row_id),FOREIGN KEY (record_id) REFERENCES tdata_%d(record_id) ON DELETE CASCADE)' WHERE var_name='TDataTableCreationCommand_1';then drop table(s) having ORA-00001 errors and run nxdbmgr check again (it should re-create all missing tables correctly).
Best regards,
Victor -
After UPDATE in DB I run nxdbmgr . It worked:
Data collection table (TDATA) for node [816] not found. Create? (Y/N) (Y/N) y
Data collection table (TDATA) for node [819] not found. Create? (Y/N) (Y/N) y
Data collection table (TDATA) for node [822] not found. Create? (Y/N) (Y/N) y
* Checking data tables... [FIXED ]
33 errors was found, 33 errors was corrected
All errors in database was fixed
Commit changes? (Y/N) ynxdbmgr check caused some changes in NetXMS configuration. I see alarms that were not present before.
For example there was an event processing policy without defined event.
It was not working, now after stasrting NetMXS server it started generating alarms for every event.Thank you,
Marcin -
Yes, that could happen if there was incorrect data in event processing policy. You have to manually fix or delete those rules.
Best regards,
Victor
Hello! It looks like you're interested in this conversation, but you don't have an account yet.
Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.
With your input, this post could be even better 💗
Register Login