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