Link to home
Start Free TrialLog in
Avatar of USAlliance
USAlliance

asked on

Oracle error: TNS-03505: Failed to resolve name

Hi,

     I am getting this error when using our amusalliance db. TNS-03505: Failed to resolve name. When I do a tnsping I am able to ping the databse by name but I get the error message when I ping by IP.

I have verified that the database is up:

XXXXXx03:oracle:/home/oracle# ps -ef|grep smon
  oracle  843888       1   0 20:13:00      -  0:02 ora_smon_test
  oracle 1536220       1   0 20:14:37      -  0:02 ora_smon_amusalliance
ryeaix03:oracle:/home/oracle# export ORACLE_SID=amusalliance
ryeaix03:oracle:/home/oracle# echo $ORACLE_SID
amusalliance
ryeaix03:oracle:/home/oracle# sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Mar 22 09:54:24 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> connect appworx
Enter password:
Connected.
SQL>

I would greatly appreciate any help and am not an "Oracle expert". Thanks in advance
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Look in $ORACLE_HOME/network/admin/tnsnames.ora

You need an entry in there pointing to the amusalliance SERVICE_NAME.
Avatar of USAlliance

ASKER

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

#########################################################################

#########################################################################
#######################################
#   Entry for database: amusalliance   #
#######################################

#######################################
#   Entry for database: test  #
#######################################

amusalliance.WORLD =
  (description =
    (address_list =
      (address = (community = tcp.world)(protocol = tcp)(host = xxx.xx.xx.xxx)(port = 1521))
      (address = (community = tcp.world)(protocol = tcp)(host = xxx.xx.xx.xxx)(port = 1526))
    )
    (connect_data =
      (sid_name = amusalliance)
      (service_name = amusalliance)
    )
  )

TEST.WORLD =
  (description =
    (address_list =
      (address = (community = tcp.world)(protocol = tcp)(host = xxx.xx.xx.xxx)(port = 1521))
      (address = (community = tcp.world)(protocol = tcp)(host = xxx.xx.xx.xxx)(port = 1526))
    )
    (connect_data =
      (sid_name = test)
      (service_name = test)
    )
  )

amusalliance_xxx.xx.xx.xxx =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = amusalliance)
      (SERVER = DEDICATED)
    )
  )

what happens when you do: tnsping amusalliance_xxx.xx.xx.xxx

also, since you have .WORLD domains on the other entries and they seem to work, add .WORLD to the entry for amusalliance.

C:\Documents and Settings\appworx>tnsping amusalliance_xxx.xx.xx.xxx

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-MAR-2
010 11:33:38

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = xxx.xx.xx.xxx)(PORT = 1521))) (CONNECT_DATA = (SID = amusalliance) (SERVER = D
EDICATED)))
OK (0 msec)


Same message after I added the .WORLD
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\appworx>tnsping xxx.xx.xx.xxx

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-MAR-2
010 11:36:41

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

C:\Documents and Settings\appworx>


What is your ORACLE_SID set to?

sqlplus /nolog

connect appworx@amusalliance_xxx.xx.xx.xxx

or

export ORACLE_SID=amusalliance_xxx.xx.xx.xxx
sqlplus /nolog
connect appworx
>>tnsping xxx.xx.xx.xxx

With a few exceptions: this will only work if you have an alias called xxx.xx.xx.xxx in your tnsnames.ora file
Some background:

tnsping isn't the same as the OS ping.  again, with a few exceptions in configurations:  tnsping looks for the alias given in the tnsnames.ora file, looks upt the HOST and PORT.  It then makes a call the the listener on that host to see if the service_name has bee registered.

That's it.  The instance can be down or non-existent.  As long as something with that name has been registered with the listener, it will be successful.
C:\Documents and Settings\appworx>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 22 11:46:05 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect appworx@amusalliance_xxx.xx.xx.xxx
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL> connect amtest.world
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect amtest.world
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
C:\Documents and Settings\appworx>set ORACLE_SID=amusalliance

C:\Documents and Settings\appworx>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 22 11:56:45 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect appworx
Enter password:
Connected.
SQL>
>>set ORACLE_SID=amusalliance

Sorry about the 'export' example above.  I was stuck on AIX from your other question.
SQL> connect appworx@AMusalliance_xxx.xx.xx.xx
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

The SID should be
C:\Documents and Settings\appworx>set ORACLE_SID=amusalliance

C:\Documents and Settings\appworx>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 22 12:03:51 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect appworx@AMUSALLIANCE_xxx.xx.xx.xxx
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect appworx@AMUSALLIANCE_xxx.xx.xx.xxx
Enter password:
Connected.
SQL>
Looks like things are working.  Are there any other questions?




Thank you for everything. So, if in the future a tnsping doesn't work but the database connects everything is ok?
I'm confused.

Not really...

tnsping is just a tool to help in checking Client to Listener connectivity.

The tnsping in http:#28242127 worked:  OK (0 msec)

The OK at the end meant everything was OK.

The entry in the tnsnames.ora file is an alias that masks all the HOST/PORT/SERVICE_NAME stuff.

You need to be consistent with that entry with tnsping and the alias for sql*plus.

so if "tnsping MYALIAS" shows OK and the correct server/port/service_name then it's OK to use:  sqlplus user@MYALIAS

forgot to add:

You can make up anything for a tns alias.  You need to make sure it is connecting to the correct server and database instance.

Just because you 'connect' doesn't mean you are connecting to what you think you are.
Sorry for any confusion. Tnsping only works using the name and not the ip.
tnsping should not work with the ip.

http:#28242434

tnsping and ping are two totally different utilities.

Thank you for explaining