Home » RDBMS Server » Networking and Gateways » ORA-12154: TNS:could not resolve service name (oracle 9.2.0.4.0, Sunsolaris)
ORA-12154: TNS:could not resolve service name [message #456748] Wed, 19 May 2010 07:27 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

I can able to login to the database through an user A. But when I try login as B,I am getting the follwing error.

ORA-12154: TNS:could not resolve service name

I am using the following method.

1. Login to the database server via putty.
2. sqlplus A/****

for the user A,the step2 is working fine.
But the same step is failing for the user B.And the user B is trying to connect to the database from the same server as the user A logged in to the database successfully.

db01: sqlplus B/*****

SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 19 13:12:52 2010

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

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


Enter user-name:


I dont know why this error is coming for a particular user only. Can you please help me to clarify this?

[Updated on: Wed, 19 May 2010 07:28]

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #456758 is a reply to message #456748] Wed, 19 May 2010 07:50 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are "users" you are talking about different people? Do they use different computers? Is there TNSNAMES.ORA file on user A's computer (which is different from TNSNAMES.ORA that uses user B)? If so, make those files equal and try again.
Re: ORA-12154: TNS:could not resolve service name [message #456764 is a reply to message #456758] Wed, 19 May 2010 07:53 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
I meant to say user A and B are the user accounts created in the database.

Both are trying to login to the database from the same computer or server.
Re: ORA-12154: TNS:could not resolve service name [message #456771 is a reply to message #456764] Wed, 19 May 2010 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But obviously not from the same environment.
As Littlefoot said, check what is different in tnsnames.ora or TNS_ADMIN variable they use.

Regards
Michel
Re: ORA-12154: TNS:could not resolve service name [message #456798 is a reply to message #456771] Wed, 19 May 2010 09:14 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.cryer.co.uk/brian/oracle/ORA12154.htm

sriram Smile
Re: ORA-12154: TNS:could not resolve service name [message #456819 is a reply to message #456771] Wed, 19 May 2010 10:15 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

Hi Littlefoot/Michel,

I am very sure both users are trying to connect to the database from the same server only. Since I am only checking this.

As per my understanding, TNSnames is not required to connect to the database from the database server(It holds the database). So I didn't use tns entries when I connect to the database for both users.

Please correct me, if I am wrong.Since I am new to the oracle.



Re: ORA-12154: TNS:could not resolve service name [message #456825 is a reply to message #456819] Wed, 19 May 2010 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't have to use tnsnames.ora to connect a local database.
But if your parameter LOCAL is set it is taken as the default service name and then you use the tnsnames.ora and a remote connection.

Regards
Michel
Re: ORA-12154: TNS:could not resolve service name [message #456826 is a reply to message #456819] Wed, 19 May 2010 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> So I didn't use tns entries when I connect to the database for both users.

please COPY & PASTE for whole session doing the following:

env | sort
sqlplus A/
exit
sqlplus B/
exit

Re: ORA-12154: TNS:could not resolve service name [message #456829 is a reply to message #456826] Wed, 19 May 2010 10:52 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
As you suggested, I pasted my session details below.

db01: env | sort
A__z="*TMOUT
ERASE=^H
HOME=/export/home/shahulj
LOGNAME=shahulj
MAIL=/usr/mail/shahulj
MANPATH=/usr/share/man:/usr/local/man:/opt/SUNWexplo/man:/opt/SUNWvts/man:/opt/CTEact/man:/opt/emc/WideSky/V5.2.0/man
ORACLE_HOME=/eai/u01/app/oracle/product/9.2
ORACLE_SID=eai2
PATH=/usr/bin:/usr/local/bin:/usr/sbin:/eai/u01/app/oracle/product/9.2/bin
PS1=rioosp01-ukbr:
PWD=/export/home/shahulj
SHELL=/bin/ksh
SSH_CLIENT=171.192.121.91 54642 22
SSH_CONNECTION=171.192.121.91 54642 101.126.231.82 22
SSH_TTY=/dev/pts/1
TERM=vt100
TMOUT=1800
TZ=GB
USER=shahulj
_=/usr/bin/env
db01: sqlplus shahulj

SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 19 16:40:28 2010

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

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> sho user
USER is "SHAHULJ"
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
db01: env | sort
A__z="*TMOUT
ERASE=^H
HOME=/export/home/shahulj
LOGNAME=shahulj
MAIL=/usr/mail/shahulj
MANPATH=/usr/share/man:/usr/local/man:/opt/SUNWexplo/man:/opt/SUNWvts/man:/opt/CTEact/man:/opt/emc/WideSky/V5.2.0/man
ORACLE_HOME=/eai/u01/app/oracle/product/9.2
ORACLE_SID=eai2
PATH=/usr/bin:/usr/local/bin:/usr/sbin:/eai/u01/app/oracle/product/9.2/bin
PS1=rioosp01-ukbr:
PWD=/export/home/shahulj
SHELL=/bin/ksh
SSH_CLIENT=171.192.121.91 54642 22
SSH_CONNECTION=171.192.121.91 54642 101.126.231.82 22
SSH_TTY=/dev/pts/1
TERM=vt100
TMOUT=1800
TZ=GB
USER=shahulj
_=/usr/bin/env
db01: sqlplus otruserisp1

SQL*Plus: Release 9.2.0.4.0 - Production on Wed May 19 16:41:07 2010

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

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


Enter user-name:
Re: ORA-12154: TNS:could not resolve service name [message #456834 is a reply to message #456829] Wed, 19 May 2010 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post content of sqlnet.ora
Re: ORA-12154: TNS:could not resolve service name [message #456836 is a reply to message #456834] Wed, 19 May 2010 11:21 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

NAMES.DIRECTORY_PATH= (TNSNAMES)

SQLNET.EXPIRE_TIME=5
Re: ORA-12154: TNS:could not resolve service name [message #456841 is a reply to message #456836] Wed, 19 May 2010 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
some heavy duty debugging is now required by doing the following commands

script /tmp/capture.log
truss sqlplus otruserisp1

This will spew many, many lines. To terminate the "script" command issue a CTRL-D
View/review content of /tmp/capture.log which will show every file that sqlplus tries to open.
Compare & contrast when DB login succeeds to determine what is different & why 1 user fails
Re: ORA-12154: TNS:could not resolve service name [message #456843 is a reply to message #456829] Wed, 19 May 2010 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post result of "which sqlplus", "id", "groups" in both case.

Regards
Michel
Re: ORA-12154: TNS:could not resolve service name [message #456844 is a reply to message #456748] Wed, 19 May 2010 11:45 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
I am getting the following error. What to do now?

db01: script /tmp/capture.log
Script started, file is /tmp/capture.log
db01: truss sqlplus otruserisp1
truss: cannot trace set-id or unreadable object file: /eai/u01/app/oracle/product/9.2/bin/sqlplus
Re: ORA-12154: TNS:could not resolve service name [message #456845 is a reply to message #456844] Wed, 19 May 2010 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>db01: truss sqlplus otruserisp1
>truss: cannot trace set-id or unreadable object file: /eai/u01/app/oracle/product/9.2/bin/sqlplus

I'm not surprised.
Try doing "sqlplus otruserisp1" as OS user "oracle" or who ever owns Oracle software tree.
Try doing the same as OS user "oracle" or who ever owns Oracle software tree.
Re: ORA-12154: TNS:could not resolve service name [message #456846 is a reply to message #456845] Wed, 19 May 2010 11:55 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

Sorry to say. I don't have the credential for oracle user. is there anyother way?
Re: ORA-12154: TNS:could not resolve service name [message #456847 is a reply to message #456843] Wed, 19 May 2010 11:57 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi Michel,

Please find the details below.

db01: which sqlplus
/eai/u01/app/oracle/product/9.2/bin/sqlplus
db01: id
uid=803250120(shahulj) gid=1(other)
db01: groups
other
db01:

I am logging to the database with 2 different user accounts not from the 2 different database server.
Re: ORA-12154: TNS:could not resolve service name [message #456848 is a reply to message #456846] Wed, 19 May 2010 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Sorry to say. I don't have the credential for oracle user. is there anyother way?
Part of me says that this error should not be happening because the error should NOT be oracle user specific.
However I realize that Oracle is too dumb to lie & it is unhappy about something.
I am kind of out ideas of where to look.
When as last time "sqlplus otruserisp1" worked OK? What changed?
Is there any LOGON trigger that might be throwing this error?
Re: ORA-12154: TNS:could not resolve service name [message #456853 is a reply to message #456847] Wed, 19 May 2010 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate sqlnet trace, in sqlnet.ora:
TRACE_DIRECTORY_CLIENT=<your directory for instance /tmp>
TRACE_FILE_CLIENT=<a file name for instance sqlnet.trc>
TRACE_LEVEL_CLIENT=admin

Then try the failed connection and post the trace file.

Regards
Michel
Re: ORA-12154: TNS:could not resolve service name [message #456857 is a reply to message #456846] Wed, 19 May 2010 14:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Sorry to say. I don't have the credential for oracle user. is there anyother way?

OK we have seen 1 user that works & 1 user that fails.
What happens with other DB schemas/users?

post last 40 - 60 lines of listener.log file

[Updated on: Wed, 19 May 2010 14:15]

Report message to a moderator

Re: ORA-12154: TNS:could not resolve service name [message #471822 is a reply to message #456857] Tue, 17 August 2010 09:20 Go to previous messageGo to next message
dadeline
Messages: 1
Registered: August 2010
Location: Brussels
Junior Member
Just stumbled on the very same kind of issue.
Problem was tnsnames.ora was not readable by the user not able to connect.
So, I got:
TNS-03505: Failed to resolve name
untill I made tnsnames.ora readable for others.
Hope this helps
Re: ORA-12154: TNS:could not resolve service name [message #471831 is a reply to message #471822] Tue, 17 August 2010 09:46 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It may, thanks.

Regards
Michel
Previous Topic: Connecting a client to Oracle
Next Topic: Problems starting Listener and Oracle Services
Goto Forum:
  


Current Time: Fri Mar 29 02:28:38 CDT 2024