TNS error - service not found

I have always beat these problems. But today we took on a new remote client and I could not get in via the tnsnames.ora and listener.ora.

The sitting DBA is a very experienced physical DBA meaning unix and such but not a development DBA.

Neither of us could make it work after 2 hours.

There is nothing left to try and tracing with level 16 was useless.

What else might cause this problem? The above mentioned config files had not been recently modifed - but we also dont know how recently anyone successfully or ever connected to the database.

Crystal Ball Show me these files...

listener.ora tnsnames.ora sqlnet.ora any errors

Sorry cannot see them...

Can we assume that PING\TNSPING are sucessful from the remote client to your db server?

without ping/tnsping results, even an experienced DBA can't tell what's going on.

Does your remote client need to pass thru a firewall before reaching the DB?

Sorry I was there for 13 hours yesterday and forgot to grab the contents of the files. This is local on the server. I can get into the instance with sqlplus "/ a sysdba" but that is not traversing the listener. I will get them today and the tnsping output. Its just a dead simple listener and dead simple tnsnames - seems something else is going on. Found another tnsnames under our monitoring app so that would explain the failure to monitor but it does not explain manual login failures.
Perhaps they have mixed versions of software I dont know why 2000 lines of sqlnet trace shows nothing out of order.

What is the result of TNSping ?

Here are the 3 files. This is basically the virgin configs I found which had not been recently modified. That makes me think about a problem elsewhere but I cant think of any place else to look.

I have since dummed these down to the bare mininum and nothing ever changes, although I had them nearly exact to another client and I got NV - name/value pair errors so perhaps that was due to differing Oracle versions.

Listener:

USE_PLUG_AND_PLAY_LISTENER_dqmdb = OFF
USE_CKPFILE_LISTENER_dqmdb = OFF
LISTENER_dqmdb=
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 123.123.123.123)
(Port = 1530)
)
)
STARTUP_WAIT_TIME_LISTENER_dqmdb = 0
CONNECT_TIMEOUT_LISTENER_dqmdb = 10
TRACE_LEVEL_LISTENER = USER
SID_LIST_LISTENER_dqmdb =
(SID_DESC =
(GLOBAL_DBNAME = dqmdb)
(SID_NAME = dqmdb)
(ORACLE_HOME = /usr/ora92/OraHome)
(PRESPAWN_MAX = 10)
)

TNS:

DQMDB.keystone.kfc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1530))
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = dqmdb)
)
)
dqmdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1530))
(CONNECT_DATA =
(SID = dqmdb)
)
)

SQLNET :

BEQUEATH_DETACH=yes
names.directory_path = (TNSNAMES)
names.default_domain = keystone.kfc
name.default_zone = keystone.kfc
AUTOMATIC_IPC = OFF
DISABLE_OOB = ON
sqlnet.expire_time = 30
TRACE_LEVEL_CLIENT=16
TRACE_FILE_CLIENT = sqlnet.trc
TRACE_DIRECTORY_CLIENT = /usr/ora92/OraHome/network/trace
TRACE_UNIQUE_CLIENT = ON
LOG_FILE_CLIENT = sqlnet.log
LOG_DIRECTORY_CLIENT = /usr/ora92/OraHome/network/log



STATUS of the LISTENER
------------------------
Alias LISTENER_dqmdb
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 09-JUN-2005 21:59:00
Uptime 0 days 15 hr. 4 min. 43 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /usr/ora92/OraHome/network/admin/listener.ora
Listener Log File /usr/ora92/OraHome/network/log/listener_dqmdb.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=123.123.123.123)(PORT=1530)))
Services Summary...
Service "dqmdb" has 1 instance(s).
Instance "dqmdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



IP has been dummied up for the posting.


$ sqlplus [email protected]

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jun 10 12:58:41 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:
ERROR:
ORA-12154: TNS:could not resolve service name

sqlplus DQM@DQMDB

Same error ORA-12154

TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 10-JUN-2005 13:25:26

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
/usr/ora92/OraHome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
)SERVICE_NAME = dqmdb) TCP)(HOST = 123.123.123.123)(PORT = 1530))
OK (0 msec)

change

DQMDB.keystone.kfc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1530))
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = dqmdb)
)
)

to

DQMDB.keystone.kfc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1530))
(CONNECT_DATA =
(SERVER=DEDICATED)
(SID = dqmdb)
)
)

and then try...

by the way the ipaddress in

dqmdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 205.223.10.110)(PORT = 1530))
(CONNECT_DATA =
(SID = dqmdb)
)
)

is diff than the one above for DQMDB.keystone.kfc. is that a typo ?

Typo.


(SID = dqmdb)

Then retry

Enter password:
ERROR:
ORA-12154: TNS:could not resolve service name


I am certain that was one of hundreds of variations we tried yesterday. I am open to any suggestions.

temporarily move the sqlnet.ora and try then

sqlplus xxx@DQMDB

Quote:

---

Originally posted by davey23uk
temporarily move the sqlnet.ora and try then

sqlplus xxx@DQMDB

---

Tried that yesterday too.

Here it is again:

$ rm sqlnet.ora
$ sqlplus sysshep@DQMDB

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Jun 10 16:40:41 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:
ERROR:
ORA-12154: TNS:could not resolve service name

is TNS_ADMIN env variable set and pointing elsewhere?

Quote:

---

Originally posted by davey23uk
is TNS_ADMIN env variable set and pointing elsewhere?

---

Sure feels like it but its not..its how I nav to the dir to work on the files..

echo $TNS_ADMIN
/usr/ora92/OraHome/network/admin


Feels like something is wrong with the binaries or something. Which is why I tried to couch the Thread in, when have you had a 12154 error that was NOT related to the listener.ora/sqlnet.ora/tnsnames.ora.

Its a weird one and they have not given me the clients Metalink ID yet so I cannot throw it over the wall to Oracle.

Why the trace does not show a problem is frustrating, 2000 pages of nothing useful.

last suggestion from me then im out of ideas

try this, you are basically supplying the connect info directo to sqlplus so bypassing the tnsnames file (make sure you have no spaces anywhere, its all on one line and the details are correct)

Code:

---

sqlplus system/manager@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.x)(PORT=1530)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=dqmdb)))

---

If that doesnt work id start questioning the listener

You betcha - that worked ( I had to alter it a bit )

so that means the TNS file is whack. Funny we retyped it in yesterday.

Thanks Davey!

I will use that approach in the future.

This worked and was then ported into the TNS file and its working for everyone now.

sqlplus
'dqm/dqm@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=123.123.123.123)(PORT=1530)))(CONNECT_DA TA=(SERVICE_NAME=dqmdb)))'


:D :D :D :D