This discussion is archived
9 Replies Latest reply: Jul 8, 2012 3:03 AM by ajith RSS

ORA-12154: TNS:could not resolve service name

879447 Newbie
Currently Being Moderated
Hi,

I am experiencing problems connecting to my local oracle db using the sqlplus command tool.
I have created the necessary settings as described into the documentation and I am able to connect to the db with external tools (sqldeveloper, net manager, net configuration assistant - test) but when I try to do the following:

sqlplus system/password@prem

I receive the following error message:

ORA-12154: TNS:could not resolve the connect identifier specified

My service name is prem.ucb.lan, my connect identifier is prem, I use the TNSNAMES for local naming. I've been struggling for days around this problem now and it seems that there isn't much information on the net regarding how to debug this issue. Everything points to "possible incorrect tnsnames.ora" but I am pretty sure that mine is OK.

Any ideas?
  • 1. Re: ORA-12154: TNS:could not resolve service name
    Paul M. Oracle ACE
    Currently Being Moderated
    ORA-12154: TNS:could not resolve the connect identifier specified

    My service name is prem.ucb.lan, my connect identifier is prem, I use the TNSNAMES for local naming.
    Please post your TNSNAMES.ORA and SQLNET.ORA.
  • 2. Re: ORA-12154: TNS:could not resolve service name
    879447 Newbie
    Currently Being Moderated
    Here is my listener.ora:

    # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = PREM.UCB.LAN)
    (SID_NAME = PREM.UCB.LAN)
    (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
    )
    )

    LOGGING_LISTENER = OFF

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = PREM))
    )
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HSTDBPRDSE)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER = C:\app\Administrator\product\11.2.0\dbhome_1\log

    TRACE_LEVEL_LISTENER = OFF
  • 3. Re: ORA-12154: TNS:could not resolve service name
    879447 Newbie
    Currently Being Moderated
    Here is my sqlnet.ora:

    # sqlnet.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora
    # Generated by Oracle configuration tools.

    # This file is actually generated by netca. But if customers choose to
    # install "Software Only", this file wont exist and without the native
    # authentication, they will not be able to connect to the database on NT.

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DEFAULT_DOMAIN = UCB.LAN

    ADR_BASE = C:\app\Administrator\product\11.2.0\dbhome_1\log
  • 4. Re: ORA-12154: TNS:could not resolve service name
    879447 Newbie
    Currently Being Moderated
    And finally, here is my tnsnames.ora:

    # tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
    # Generated by Oracle configuration tools.

    PREM.UCB.LAN =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(Key = PREM))
    (ADDRESS = (PROTOCOL = TCP)(HOST = HSTDBPRDSE)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PREM.UCB.LAN)
    )
    )
  • 5. Re: ORA-12154: TNS:could not resolve service name
    Paul M. Oracle ACE
    Currently Being Moderated
    listener.ora is not relevant here. The error you get is a client one, and the following is what you can find within the documentation about using tnsnames.ora :


    ORA-12154: TNS:could not resolve the connect identifier specified

    Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
    Action:
    - If you are using local naming (TNSNAMES.ORA file):

    - Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

    - Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

    - Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

    - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
  • 6. Re: ORA-12154: TNS:could not resolve service name
    879447 Newbie
    Currently Being Moderated
    Ok, I've added a TNSNAMES to the sqlnet.ora file definition. Still no positive answer from sqlplus...

    Any other ideas?
  • 7. Re: ORA-12154: TNS:could not resolve service name
    879447 Newbie
    Currently Being Moderated
    Ok, PROBLEM SOLVED.

    The issue was created by my password which was P@ssw0rd. It seems that the symbol @ messes things up which is utterly stupid, especially when we are talking about well known issue and the maturity level of the Oracle range of DB products...

    :)
  • 8. Re: ORA-12154: TNS:could not resolve service name
    937633 Newbie
    Currently Being Moderated
    Hi,


    I am using linux machine as my DB host and linux as my DB client. I have done all the setups manually and have also modified the tnsnames.ora and listener.ora files on both host and client.

    While running the workload I am getting the error as
    ORA-12154: TNS:could not resolve the connect identifier specified

    Please suggest
  • 9. Re: ORA-12154: TNS:could not resolve service name
    ajith Newbie
    Currently Being Moderated
    Awesome same issue..

    Thankyou verymuch..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points