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

ORA-12154: TNS:could not resolve the connect identifier specified
Cause: A connection to 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 type of connect  identifier  used was a net service name then 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 proper directory 
   and is accessible.
  -Check that net service name used as the connect identifier exists 
   in tnsnames.ORA file.
  -Make sure there are no syntax errors anywhere in tnsnames.ORA file. 
   Look for unmatched parentheses or stray characters. Errors in a tnsnames.ORA 
   file may make it unusable.
- If you are using directory naming:
  -Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH 
   parameter in the Oracle Net profile (SQLNET.ORA).
  -Verify that the LDAP directory server is up and that it is accessible.
  -Verify that the net service name or database name used as the connect 
   identifier is configured in the directory.
  -Verify that the default context being used is correct by specifying a fully 
   qualified net service name or a full LDAP DN as the connect identifier
- If you are using easy connect naming:
  -Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH 
   parameter in the Oracle Net profile (SQLNET.ORA).
  -Make sure the host, port and service name specified are correct.
  -Try enclosing connect identifier in quote marks. See Oracle Net Services Admin
   Guide or Oracle operating system specific guide for more information on naming.

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/net12150.htm

Problem Description:
Oracle client uses either a "tnsnames.ora", "A Directory Server" or 
"Oracle Names Server" to resolve Database Service. This error indicates that 
resolution is not working properly. 

The ORA-12154 is most likely related to your local copy of the tnsnames.ora file, 
your listener configuration and your database name aliases.

Make sure that your listener is listening for same service name that you are using. 
- Check: $ORACLE_HOME/network/admin/tnsnames.ora 
Your service name might have an alias, so check for global entries and local entries.
- Check your global_name setting with this SQL:
  select * from global_name;
  The query value should match your init.ora parms for db_name and db_domain.  

ORA-12154 occurs when a user attempts to logon to an Oracle database. 
The tnsnames.ora file is not found or has a syntax error. 
- Incorrect Syntax
- Missing or wrong location
- The database name isn't in the tnsnames.ora file

If you are using a directory or names server, you should check your sqlnet.ora file.

Steps to fix ORA-12154:
- Check instance name has been entered correctly in tnsnames.ora. 
- There should be no control characters at the end of the instance or database name.
- All paranthesis around the TNS entry should be properly terminated
- Domain name entry in sqlnet.ora should not be conflicting with full database name.
- If problem still persists, try to re-create TNS entry in tnsnames.ora.
- At last you may add new entries using the SQL*Net Easy configuration utility.


Related Posts:
- SQLNET: How does Oracle Client connect with Oracle Server
- ORA-12545: Connect failed because target host or object does not exist
- ORA-12560: TNS:protocol adapter error
- ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
- ORA-03135: connection lost contact

15 comments:

  1. is it working for you?

    ReplyDelete
    Replies
    1. Yes, this is problem symptoms and possible causes. You need to find what exact problem you have in your environment.

      Delete
  2. XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = jain-PC)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )
    my tnsnames.ora file is like this wht is the problem in this i am receiving java.sql.SQLException: [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified as an error.

    ReplyDelete
    Replies
    1. you need to check following things:
      - is XE service is running on "jain-PC"
      - is listener on "jain-PC" is configured for XE service
      - are you able to ping "jain-PC"
      - are you able to telnet "jain-PC" at 1521 port

      Delete
  3. I have also same problem like this...
    [Ora]ORA-12154: TNS:could not resolve the connect identifier specified.

    plz suggest me......

    ReplyDelete
    Replies
    1. try the steps mentioned in the article

      Delete
    2. just add (UR=A) in the tnsnames.ora file it may b help u .

      Delete
  4. I always spent my half an hour to read this web site's articles all the time along with a mug of coffee.3 ()

    ReplyDelete
  5. i tested all as u mentioned above, but again the same error message, this time i am using type 2 driver to connect with my oracle database........

    ReplyDelete
  6. Inns will have the ability to call one for you.

    ReplyDelete
  7. Even though this will not have a big impact, it is still something
    we (webmasters) should really look into.

    ReplyDelete
  8. I think the admin of this site is genuinely working hard in favor of his website, for
    the reason that here every information is quality based information.

    ReplyDelete
  9. Hello there, just became alert to your blog through
    Google, and found that it is really informative. I'm gonna watch out for
    brussels. I will be grateful if you continue this in future.

    Many people will be benefited from your writing.
    Cheers!

    ReplyDelete
  10. Hey very interesting blog!

    ReplyDelete