This discussion is archived
3 Replies Latest reply: Oct 3, 2013 12:00 PM by Paul M. RSS

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

991464 Newbie
Currently Being Moderated

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. Oracle ACE
    Currently Being Moderated

     

    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 Oracle ACE
    Currently Being Moderated

    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. Oracle ACE
    Currently Being Moderated
    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>

Legend

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