Forum Stats

  • 3,852,790 Users
  • 2,264,137 Discussions
  • 7,905,142 Comments

Discussions

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

User_BAKZA
User_BAKZA Member Posts: 67 Blue Ribbon

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

Answers

  • Paul M.
    Paul M. Member Posts: 10,947 Gold Trophy
    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...

  • HDeiby
    HDeiby Member Posts: 193 Bronze Badge

    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/[email protected]_name

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

    wrong: sqlplus user/[email protected]_name;

  • Paul M.
    Paul M. Member Posts: 10,947 Gold Trophy
    edited Oct 3, 2013 3:00PM
    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/[email protected]_name;
    
    

    Why ?

    $ sqlplus test/[email protected]
    
    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/[email protected];
    
    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>
    
    
This discussion has been closed.