3 Replies Latest reply: Oct 3, 2013 2:00 PM by Paul M. RSS

    We are unable to identified the root cause of ORA-12154.

    991464

      Hi,

       

      We are using Oracle 10g forms and reports server on linux machine.

      Yesterday we had faced  ORA-12154 error, and our all end users were facing this issue on application access.


      I have checked the TNS entry located at /d01/oracle/OraHome_1/network/admin, inside the tnsnames.ora

      our entry was absolutly correct as before like;


      SALES =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver.abc.com)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = SALES.abc.com)

          )

        )


      But, all users were facing ORA-12154.


      I just change the entry manually as below, but not fruitful.


      SALES.abc.com =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver.abc.com)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = SALES.abc.com)

          )

        )

       

      But when I created the below entry using NETCA, all users were able to connect with application.

      And problem was solved.

       

      SALES.abc.com =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver.abc.com)(PORT = 1521))

          )

          (CONNECT_DATA =

            (SERVICE_NAME = SALES.abc.com)

          )

        )

       

      Please assist us what was the root cause, and what is the logic behind this?

       



      Regards

        • 1. Re: We are unable to identified the root cause of ORA-12154.
          Paul M.

           

          I just change the entry manually as below, but not fruitful.


          But when I created the below entry using NETCA, all users were able to connect with application.

           

          ORA-12154 is often caused by syntax errors, or for tnsnames.ora not being found where it should be.

           

          $ oerr ora 12154

          12154, 00000, "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 aconnect 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.

           

           

          The files look the same, but NETCA's one is surely correct, and in the correct place, yours maybe not...

          • 2. Re: We are unable to identified the root cause of ORA-12154.
            HDeiby

            When you connect to a Database Instance you need specify the "Service Name on Tnsnames.ora".

             

            Please, check if "tnsnames.ora" exists on $ORACLE_HOME/network/admin

             

            The correct way to connect is:

             

            sqlplus user/password@service_name

             

            if you type with ";" you will get that error, example:

             

            wrong: sqlplus user/password@service_name;

            • 3. Re: We are unable to identified the root cause of ORA-12154.
              Paul M.
              check if "tnsnames.ora" exists on $ORACLE_HOME/network/admin

              Yes, unless you have some TNS_ADMIN set...

              if you type with ";" you will get that error, example:

               

              wrong: sqlplus user/password@service_name;

              Why ?

              $ sqlplus test/test@test11

               

              SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 20:54:10 2013

               

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

               

               

              Connected to:

              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

              With the Partitioning, OLAP, Data Mining and Real Application Testing options

               

              SQL> exit

              Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

              With the Partitioning, OLAP, Data Mining and Real Application Testing options

              $ sqlplus test/test@test11;

               

              SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 20:54:18 2013

               

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

               

               

              Connected to:

              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

              With the Partitioning, OLAP, Data Mining and Real Application Testing options

               

              SQL>