Whats wrong with these tns files ? ORA-12514
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Whats wrong with these tns files ? ORA-12514

Hybrid View

  1. #1
    Join Date
    May 2005
    Posts
    129

    Whats wrong with these tns files ? ORA-12514

    I cannot connect after upgrade to 10.2
    TNS:

    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl.codc182.rqinet.com)
    )
    )


    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = PLSExtProc)
    )
    )

    Listener:

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = /oradata/orabinaries/10.2.0/db)
    (PROGRAM = extproc)
    )
    )

    SQL> show parameter service

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    service_names string orcl.codc182.rqinet.com
    SQL>


    It looks exactly like other boxes yet the listener says it does not handle 'orcl'


    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    )
    )


    [oracle@codc182 admin]$ lsnrctl service

    LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 02-NOV-2005 11:58:25

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=codc182.rqinet.com)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0
    LOCAL SERVER
    The command completed successfully


    SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 2 11:58:43 2005

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

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor


    Enter user-name:

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by roadwarriorDBA
    I cannot connect after upgrade to 10.2
    TNS:

    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl.codc182.rqinet.com)
    )
    )
    Is your SID orcl.codc182.rqinet.com?

    Correct this to the exact way you have set in initfile (SERVICE_NAME = SID) and then connect.

    Always use ip address in HOST to avoid confusion
    Last edited by dbasan; 11-02-2005 at 03:18 PM.
    "What is past is PROLOGUE"

  3. #3
    Join Date
    May 2005
    Posts
    129
    The database says its

    SQL> show parameter service

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    service_names string orcl.codc182.rqinet.com
    SQL>

    I can sqlplus / as sysdba

    I can also sqlplus user/password if I leave the @tnsname out it works fine.

    I just wont get the service going and connect @orcl

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    The name orcl.codc182.rqinet.com comes the way you have defined in the initfile.

    When you are connecting locally you don't need the service name, you can connect trouble free.

    When you do that from remote client/server you have troubles.

    In your case the tnsping has identified the host and the the Listener was unable to connect to the database as it cannot recognize the SID thereafter.

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Says to set the service_name right (to SID) in TNS file and connect.
    Last edited by dbasan; 11-02-2005 at 04:26 PM.
    "What is past is PROLOGUE"

  5. #5
    Join Date
    May 2005
    Posts
    129
    Quote Originally Posted by dbasan
    The name orcl.codc182.rqinet.com comes the way you have defined in the initfile.

    When you are connecting locally you don't need the service name, you can connect trouble free.

    When you do that from remote client/server you have troubles.

    In your case the tnsping has identified the host and the the Listener was unable to connect to the database as it cannot recognize the SID thereafter.

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Says to set the service_name right (to SID) in TNS file and connect.
    The database says thats the service name via show parameter service.

    However I cannot connect locally if I reference the tnsnames entry.

  6. #6
    Join Date
    May 2005
    Posts
    129
    There was a local listener defined in the pfile and it needed to be in the tnsnames. I dont really understand this local listener concept. I had added the local listener back in before but the tns alias is case sensitive until the database auto registers with the listener which takes a couple minutes.

  7. #7
    Join Date
    May 2005
    Posts
    129
    TNSPing works fine. I dont know whats wrong with the listener.

    tnsping orcl

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = codc182.rqinet.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.codc182.rqinet.com)))
    OK (0 msec)
    [oracle@codc182 admin]$ sqlplus scott/tiger@orcl

    SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 2 13:17:40 2005

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

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width