9 Replies Latest reply: Jul 8, 2012 5:03 AM by ajith RSS

    ORA-12154: TNS:could not resolve service name

    879447
      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.
          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
            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
              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
                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.
                  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
                    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
                      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
                        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
                          Awesome same issue..

                          Thankyou verymuch..