[collectd] oracle plugin can't find tnsnames.ora

[Giovanni Torres]

I successfully compiled the oracle plugin for collectd but I can't get it to work.

I installed oracle-instantclient12.1-devel-12.1.0.1.0-1.x86_64 and oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64.  I set $ORACLE_HOME in /etc/profile.d and I set the LD_LIBRARY_PATH in /etc/ld.so.conf.d.

Here is my oracle config in /etc/collectd.d/

LoadPlugin oracle

<Plugin oracle> 

  <Query "io_per_tablespace">
    Statement "SELECT sum(vf.PHYBLKRD)*8192 AS PHY_BLK_R, \
                      sum(vf.PHYBLKWRT)*8192 AS PHY_BLK_W, \
                      'tablespace' AS i_prefix, \
                      dt.tablespace_name \
                   FROM ((dba_data_files dd JOIN v$filestat vf ON dd.file_id = vf.file# ) \
                         JOIN dba_tablespaces dt ON dd.tablespace_name = dt.tablespace_name) \
                   GROUP BY dt.tablespace_name"
    <Result>
      Type "io_octets"
      InstancesFrom "i_prefix" "TABLESPACE_NAME"
      ValuesFrom "PHY_BLK_R" "PHY_BLK_W"
    </Result>
  </Query>

  <Database "DB1">
    ConnectID "DEV"
    Username "collectd"
    Password "secret"
    Query "io_per_tablespace"
  </Database>

</Plugin>


This is the log entry from the oracle plugin:

[2014-07-02 15:12:24] oracle plugin: o_read_database (db = DB1, query = (none)): OCILogon("DEV") failed: ORA-12154: TNS:could not resolve the connect identifier specified


I have a tnsnames.ora in $ORACLE_HOME/network/admin but the daemon doesn't seem to be looking for it:

strace -e access,open /usr/sbin/collectd -C /etc/collectd.conf -f

open("/etc/collectd.d/oracle.conf", O_RDONLY) = 3
open("/usr/lib64/collectd/oracle.so", O_RDONLY) = 6
open("/usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1", O_RDONLY) = 6
open("/usr/lib/oracle/12.1/client64/lib/libnnz12.so", O_RDONLY) = 6
open("/usr/lib/oracle/12.1/client64/lib/libons.so", O_RDONLY) = 6
open("/usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1", O_RDONLY) = 6
open("/usr/lib/oracle/12.1/client64/lib/libociei.so", O_RDONLY) = 5
open("/usr/lib/oracle/12.1/client64/network/admin/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/12.1/client64/network/admin/oraaccess.xml", F_OK) = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/12.1/client64/network/admin/oraaccess.xml", F_OK) = -1 ENOENT (No such file or directory)
open("/usr/lib/oracle/12.1/client64/network/admin/oraaccess.xml", O_RDONLY) = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/12.1/client64/network/admin/sqlnet.ora", F_OK) = 0
open("/usr/lib/oracle/12.1/client64/network/admin/sqlnet.ora", O_RDONLY) = 5
access("/root/.sqlnet.ora", F_OK)       = -1 ENOENT (No such file or directory)
open("/etc/orabasetab", O_RDONLY)       = -1 ENOENT (No such file or directory)
open("/usr/lib/oracle/12.1/client64/install/orabasetab", O_RDONLY) = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/12.1/client64/network/admin/sqlnet.ora", F_OK) = 0
open("/usr/lib/oracle/12.1/client64/network/admin/sqlnet.ora", O_RDONLY) = 6
access("/usr/lib/oracle/12.1/client64/network/admin/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/intchg.ora", F_OK)         = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/12.1/client64/network/admin/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/12.1/client64/network/admin/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
access("/etc/tnsnav.ora", F_OK)         = -1 ENOENT (No such file or directory)
access("/usr/lib/oracle/12.1/client64/network/admin/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)


It doesn't appear to be looking for tnsnames.ora.  My $ORACLE_HOME is /usr/lib/oracle/12.1/client64.  This is the same ORACLE_HOME that was used when compiling the plugin, if that matters.


Can someone point out what I may be doing wrong or what I have missed to get this to work?


Thanks,
Giovanni