Zabbix can not resolve TNS identifier

[ITguru]

Hi guys

I need your help. I am trying to monitor an Oracle database.
My Zabbix (3.0.7) is running on Ubuntu 14.04.

Thats what I already did:
- I installed "unixODBC" and the "Oracle Instant Client" on my server.
- I configured /etc/odbc.ini (Driver is defined in this file therefore I have odbcinst.ini)
- I created tnsnames.ora and sqlnet.ora

With this configuration I can succesfully isql to my database.

But when I create a trigger in zabbix the following error message appears:
Cannot connect to ODBC DSN:[SQL_ERROR]:[08004][12154][[unixODBC][Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified
]|

I did a lot of research and tried every suggestion I found on zabbix forum or elsewhere. I read that in most cases SELinux causes this behaviour, but on my Ubuntu there ist no SELinux.

Can anyone help me out? Any suggestion is welcome.

Thanks alot

[ITguru]

no hints on that topic?
I hope you can help me.

[GPegel]

I have a working config in 3 different environments. What is important is that you use the same name in your item key as you use in your odbc.ini file.

For example, a part of my odbc.ini looks like this:

[TEST_DATABASE]
Driver=OracleODBC-12.1
ServerName=//servername:port/instance_name
UserID=zabbix
Password=zabbix

Now my item key in Zabbix looks like this:

db.odbc.select[whatever_name,TEST_DATABASE]

[ITguru]

Hi

thanks for your reply.
I use the same name my item key as defined in my odbc.ini

This is my odbc.ini:

[DB_Prod]
Driver= /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
ServerName= test.server.com
Port= 1521
UserID= user
Password= pass

And this is my item:

db.odbc.select[gms-app-alarm,DB_Prod]

Did you set any additional environment variables for zabbix?
I only set the system env vars (ORACLE_HOME, LD_LIBRARY_PATH..)

[GPegel]

I have set these 3 environment variables:

LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:/usr/lib64:
TNS_ADMIN=/usr/lib/oracle/12.1/client64/network/admin
ORACLE_HOME=/usr/lib/oracle/12.1/client64

I've also create a file in /etc/sysconfig/zabbix-server with these lines:

ORACLE_HOME=/usr/lib/oracle/12.1/client64
LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:/usr/lib64
TNS_ADMIN=/usr/lib/oracle/12.1/client64/network/admin
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/lib/oracle/12.1/client64/lib
 
export ORACLE_HOME
export LD_LIBRARY_PATH
export TNS_ADMIN
export PATH

After creating the file, restart Zabbix Server.

ps. I'm running my Zabbix Server on CentOS 7, not on Ubuntu.

[ITguru]

I think this might be the problem
I added the variables in my

/etc/default/zabbix-server

file, too. But after restart of zabbix the variables are still not set.

anf@SBSRVZABBIX01:/$ sudo strings /proc/28496/environ
PATH=/usr/local/sbin:/usr/local/bin:/usr/bin:/usr/sbin:/sbin:/bin
TERM=linux
UPSTART_JOB=zabbix-server
UPSTART_INSTANCE=

I have no idea, why zabbix does not set the vars

[GPegel]

Okay, weird.

By the way, I'm looking at your odbc.ini file and I'm wondering where your instance name is.

I'm not an Oracle DBA expert but I guess you have to add an instance name to connect to the right database. Correct me if I'm wrong.

[ITguru]

I set the instance in my tnsnames.ora:

test.server.com =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=db.server.lan)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=proddb.server.com))
    (ENABLE=BROKEN)
  )

[GPegel]

Yes me too but I also set it in odbc.ini

[ITguru]

thats strange. When I add it at odbci ini i can not connect with isql anymore

[GPegel]

Are you really 100 percent sure your instance name is correctly spelled? Maybe a typo?

Or is your DNS on your server correct? Maybe you could try to change the servername to IP address in your odbc.ini file.

Maybe an idea to write your odbc.ini exactly the same as I did and particular the "ServerName=//..."

[ITguru]

Can you post your tnsnames.ora for me to verify?

Thanks a lot

[SirMax]

My /etc/default/zabbix stopped working.
you can see errors in journalctl

best way on new systems is to use systemd units
change file
/lib/systemd/system/zabbix-[proxy/server].service

add in [service] lines
Environment="ORACLE_HOME=/usr/lib/oracle/12.1/client64"
Environment="LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64"
Environment="TNS_ADMIN=$ORACLE_HOME/network/admin"
Environment="TNS_ADMIN=/usr/lib/oracle/12.1/client64/network/admin"

[soumyacharya]

I am afacing the same issue. i am able to connect to oracle database using isql but in zabbix front end it is replying tns: could not receive the correct identifier provided. I have disabled selinux policy and also updated the env variables in /etc/sysconfig/zabbix-server