1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by StryderKC, Feb 20, 2009.

  1. StryderKC

    StryderKC Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Hi guys, I am not an absolute newbie, and have been using the oracle database for a long time. All of a sudden today I am getting the error.

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

    This error is obviously related to tnsnames.ora file and not correct Service and SID identifiers. However what's baffling me is that both my tnsnames.ora and listener.ora have the correct entries and even the listener service is running.

    Here's my tnsnames.ora
    and here's the listener.ora

    I am using the service ORCL to connect to the database using SQL Plus. I connected yesterday and it was fine. So i am completely baffled! HAs anyone faced anything like this before?
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    and what does listener status show ? Is the database registered ?
     
  3. StryderKC

    StryderKC Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    lsnrctl stat returns the following

    LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 20-FEB-2009 15:52
    :39

    Copyright (c) 1991, 2004, Oracle. All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
    STATUS of the LISTENER
    Alias LISTENER
    Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
    Start Date 20-FEB-2009 15:52:11
    Uptime 0 days 0 hr. 0 min. 28 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File C:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
    Listener Log File C:\oracle\product\10.1.0\Db_1\network\log\listener.log

    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
    Services Summary...
    Service "OracleTest" has 1 instance(s).
    Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
     
  4. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    So the problem zeroes down to tnsnames. Can you reconfigure it using netmgr and see whether the problem resolves or not ?
     
  5. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Also, you can try to connect with sid.db domain, if you have one.
     
  6. alhanani

    alhanani Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    here you have to type the IP of your client at your server TNS as well copy the ip of your server TNS name on you Client
    that set
     
  7. StryderKC

    StryderKC Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    hi guys yes I had resolved that error. Actually like alhanani said i used the IP of my database server in my tnsnames file and it was fine.
     
  8. alhanani

    alhanani Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    did you configure your sqlnet.ora if not
    ad your ip of the client or commit all the restriction
     
  9. harshpreet

    harshpreet Guest

    IN VERY SIMPLE TERMS. TRY TO INSTALL ORACLE FORMS AND ORACLE 9i IN DIFFERENT DRIVES.... THEN YOU WILL BE HAVING TWO TNSNAMES.ORA FILES..
    1. FOR ORACLE 9i IN NETWORK\ADMIN DIRECTORY
    2. FOR FORMS IN NETWORK\ADMIN DIRECTORY...(IN RESPECTIVE DRIVES)

    THEN WHAT YOU NEED TO DO IS ...OPEN BOTH TNSNAMES.ORA SIMULTANEOUSLY DOR EASY REFERENCE...


    IN FORMS--TNSNAME'S FILE THERE WILL BE ONLY ONE ENTRY BY THE NAME "EXTPROC_CONNECTION_DATA"....WHILE IN ORACLE 9i-TNSNAME FILE THERE WILL BE MANYU ENTRIES...

    WHAT YOU NEED TO DO IS IN ORACLE-9i FILE IS LOOK FOR A DATABASE ALIAS WITH THE NAME OF THE DATABASE WHICH YOU MENTIONED AT THE TIME OF INSTALLING ORACLE 9i i.e. the SID which you mentioned LETS SAY (TEST1)...
    then copy that complete set into FORMS TNSNAMES FILE... SAVE IT...AND YOU ARE DONE

    NOW GO TO FORMS... SCOTT\TIGER AND THE DATABASE NAME=TEST1
    AND YOU ARE DONE...
    IF ANY ISSUES MAIL ME AT er.harshpreetsingh@gmail.com