NetXMS Support Forum

I have .tnsnames.ora file in my local home directory

[vlltcmpsswtdap01.atldev.com]/home/swdev/istnetxms/agent205/netxms/log> cd
[vlltcmpsswtdap01.atldev.com]/home/swdev>

[vlltcmpsswtdap01.atldev.com]/home/swdev> ls -ltra | grep tns
-rw-rw-r--   1 swdev swdev    375 Nov 30 15:47 .tnsnames.ora

I have following entry in .tnsnames.ora

MPSDEV1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lltcdevoda1-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mpsdev1)
    )
  )

Using DBQuery I am tring to connect to above database

I gave following entry in agent configuration file

*DBQUERY
Database = id=MPSDEV1;driver=oracle.ddr;server=10.7.68.65;login=Netxms;Password=mpsdev
Query = query1:MPSDEV1:5:SELECT count(*) FROM containers


when I started agent I got following error

[15-Dec-2015 00:27:51.709] [WARN ] DBQUERY: cannot connect to database MPSDEV1 (ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA)
[15-Dec-2015 00:27:51.709] [DEBUG] DBQUERY: Query::poll(query1): no connection handle for database MPSDEV1


I tried giving id as SID also


*DBQUERY
Database = id=mpsdev1;driver=oracle.ddr;server=10.7.68.65;login=Netxms;Password=mpsdev
Query = query1:mpsdev1:5:SELECT count(*) FROM containers

But still I got same error in debug file


[WARN ] DBQUERY: cannot connect to database mpsdev1 (ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA)
[DEBUG] DBQUERY: Query::poll(query1): no connection handle for database mpsdev1


For setting up subagent I refered to example given in admin guide, is the info given in admin guide is correct ? what I am missing in subagent configuration
why DBQUERY subagent could not identify the TNS entry ?

---------------------------------------
Configuration Example
# This sample nxagentd.conf instructs agent to:
# 1. load DBQuery subagent
# 2. Define two databases - db1 (Oracle) and db2 (MySQL).
# 3. Execute query "SELECT f1 FROM table1" in database db1 every 60 seconds
# 4. Execute query "SELECT f1 FROM table2 WHERE f2 LIKE ':%'" on DSN2 every 15 seconds
MasterServers = netxms.demo
SubAgent = dbquery.nsm
*DBQUERY
Database = id=db1;driver=oracle.ddr;server=10.0.0.2;login=netxms;encryptedPassword=H02kxYckADXCpgp+
Database = id=db2;driver=mysql.ddr;server=10.0.0.4;dbname=test_db;login=netxms;password=netxms1
Query = query1:db1:60:SELECT f1 FROM table1
Query = query2:db2:15:SELECT f1 FROM table2 WHERE f2 LIKE ':%'
ConfigurableQuery = query3:db2:Comment in param:SELECT name FROM images WHERE name like ?
-------------------------------------------

I think guide is wrong. Don't have access to oracle right now so can't check, but it should be like this (if environment is set correctly and "sqlplus Netxms/mpsdev@MPSDEV1" connects successfully):

Database = id=mpsdev1;driver=oracle.ddr;server=MPSDEV1;login=Netxms;Password=mpsdev

or you can use instant client connection string without tnsnames:

Database = id=mpsdev1;driver=oracle.ddr;server=//lltcdevoda1-scan:1521/mpsdev1;login=Netxms;Password=mpsdev

or you can even use full connection string there (haven't tried that, but should work as well):
Database = id=mpsdev1;driver=oracle.ddr;server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lltcdevoda1-scan)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mpsdev1)));login=Netxms;Password=mpsdev