Oracle 11 connection problems. Please help!

[untergeek]

Up until now we've been using Oracle 10.2.0.1 as our OCI backend for Zabbix which was connecting to an 11i database. We're starting to have problems where Zabbix won't write to the database quickly enough and we suspect Oracle 10 libs are not interacting with Zabbix 1.8 very well.

The reason we had this configuration is that Zabbix did not appear to want to run with the 11i libraries. Despite tnsping and sqlplus all working properly from the same host as the zabbix server, when we use a binary compiled against the Oracle 11 libraries we see this in our log file:

460:20100406:143424.378 [Z3001] Connection to database '//czab11db.incert.REDACTED.com:1560/CZAB11DB' failed: [-1] ORA-12154: TNS:could not resolve the connect identifier specified

On the database side, we see this in the listener log file:
TNS-12502: TNS:listener received no CONNECT_DATA from client

We don't know what's going on. Please help!

[untergeek]

Guys, this is a bug. This should not be happening from what my db people are telling me.

The error means that the connection string being created is for the OCI or the lightweight direct connection (like what Java uses) but that the library is expecting something in the format for TNS listener.

Acting on that hunch, we created a new entry in our tnsnames.ora to test the theory:

//pzabbix2db.prod.REDACTED.com:1560/PZABBIX2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pzabbix2db.prod.REDACTED.com )(PORT = 1560))
    )
    (CONNECT_DATA =
      (SID = PZABBIX2)
    )
  )

Guess what. This worked flawlessly. This should be addressed somewhere! We actually had to name the TNS instance after the connection string that Zabbix was creating.

[ZhidkovAN]

oracle

Hi!

On client side in sqlnet.ora (in $TNS_ADMIN dir) check parameter
NAMES.DIRECTORY_PATH
It must include ONAMES
example:
NAMES.DIRECTORY_PATH=(TNSNAMES,ONAMES)
And this working fine

[ZhidkovAN]

Guys, this is a bug. This should not be happening from what my db people are telling me.

Not a bug, it's connection method

[untergeek]

What I'm being told is that if that is the connection method it should not be even looking at the tnsnames.ora (which it is). Here's our sqlnet.ora:

# SQLNET.ORA Network Configuration File
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES)
TRACE_LEVEL_CLIENT = OFF 
TRACE_DIRECTORY_CLIENT = /opt/oracle/product/11.1.x/network/admin 
TRACE_FILE_CLIENT = cli.trc 
TRACE_LEVEL_SERVER = off 
TRACE_DIRECTORY_SERVER = /opt/oracle/product/11.1.x/network/trace
TRACE_FILE_SERVER = server.trc 
SQLNET.INBOUND_CONNECT_TIMEOUT=120

Would it make a difference if we put ONAMES before TNSNAMES? I wouldn't know, but is there an order of operations and it isn't merely failing TNSNAMES and checking for ONAMES after, but failing at TNSNAMES?

[ZhidkovAN]

What I'm being told is that if that is the connection method it should not be even looking at the tnsnames.ora (which it is). Here's our sqlnet.ora:

# SQLNET.ORA Network Configuration File
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES)
TRACE_LEVEL_CLIENT = OFF 
TRACE_DIRECTORY_CLIENT = /opt/oracle/product/11.1.x/network/admin 
TRACE_FILE_CLIENT = cli.trc 
TRACE_LEVEL_SERVER = off 
TRACE_DIRECTORY_SERVER = /opt/oracle/product/11.1.x/network/trace
TRACE_FILE_SERVER = server.trc 
SQLNET.INBOUND_CONNECT_TIMEOUT=120

Would it make a difference if we put ONAMES before TNSNAMES? I wouldn't know, but is there an order of operations and it isn't merely failing TNSNAMES and checking for ONAMES after, but failing at TNSNAMES?

sqlnet.ora on database side? Be sure that ONAME exist in sqlnet.ora on CLIENT size (where zabbix server running).
You wrote that client version i s10g, but in sqlnet.ora is 11g.

[untergeek]

This is what I mentioned in my initial post:

Up until now we've been using Oracle 10.2.0.1...

when we use a binary compiled against the Oracle 11 libraries we see this in our log file...

Yes, this is all on the client side now. The sqlnet.ora as published is on the client side, not the server side.

[gmmercer]

Same issue - Zaabix using //serverort/user with tnsnames

I have the same issue. Looking in my sqlnet trace file, it is searching tnsnames.ora for an entry with the form //serverort/user instead of either teh sid/service name or onames.