I am running Oracle 11g on Windows Server 2008R2. I have a client application(Zabbix) running elsewhere that is trying to connect to it.
The client machine has:
And as such, it does not have sqlnet.ora or tnsnames.ora files. It is attempting to establish a connection with direct addressing. With a print statement in the php, I see that it is calling
ociplogon(user,password,$connect);
with connect being set to //dbhostmachine:1521/zabbix. So everything looks good there.
C:\Users\mdobrini> lsnrctl services
LSNRCTL for 64-bit Windows: Version 11.1.0.7.0 - Production on 02-NOV-2012 13:00:55
Copyright (c) 1991, 2008, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhostmachine.dddd.cccc.com)(POR
Services Summary...
Service "zabbix" has 1 instance(s).
Instance "zabbix", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:578 refused:0 state:ready
LOCAL SERVER
Service "zabbixXDB" has 1 instance(s).
Instance "zabbix", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: dbhostmachine, pid: 6384>
(ADDRESS=(PROTOCOL=tcp)(HOST=dbhostmachine.dddd.cccc.com)(PORT=61396))
Service "zabbix_XPT" has 1 instance(s).
Instance "zabbix", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:578 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Attempting to connect locally works fine:
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Nov 2 13:06:31 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter user-name: SYSTEM
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SQL> connect uuu/ppp@localhost/zabbix
Connected.
SQL>
However, remotely I get the popular error:
ociplogon(): ORA-12154: TNS:could not resolve the connect identifier specified
The strange thing is that it was working intermittently before. I would get an error about every 10 seconds in my application:
30923:20121101:142237.595 [Z3001] connection to database '//dbhostmachine:1521/zabbix' failed: [-1] ORA-12516: TNS:listener could not find available handler with matching protocol stack
30923:20121101:142237.596 watchdog: database is down
but there was definitely data being sent across the network, and persisted in the db. After restarting application and server I am no longer able to get a connection at all and am stuch on ORA-12154
ORA-12154 was being caused by a DNS issue. After switching to IP addressing that went away.
ORA-12516 was being caused by maximum sessions/processes being reached on the DB. After adjusting that as well as some PHP OCI8 parameter this went away as well.