-
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...
Amar
"There is a difference between knowing the path and walking the path."
-
Can we assume that PING\TNSPING are sucessful from the remote client to your db server?
Assistance is Futile...
-
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?
What's next after 10g?
-
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 ?
Last edited by BJE_DBA; 06-10-2005 at 01:28 PM.
-
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 DQM@DQMDB.keystone.kfc
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
Last edited by roadwarriorDBA; 06-10-2005 at 02:34 PM.
-
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 ?
Amar
"There is a difference between knowing the path and walking the path."
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|