Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21

    Post Unanswered: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

    Hi all,
    I am getting the error when I use sqlplus scott@irv-ora-902v

    "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA".

    Oracle version: 11gR2, OS: Linux.

    sqlnet.ora
    ----------
    # sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
    # Generated by Oracle configuration tools.

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT,HOSTNAME)

    ADR_BASE = /u01/app/oracle

    listener.ora
    -----------
    # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = irv-ora-902v)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER = /u01/app/oracle

    tnsnames.ora:
    --------------
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.

    LISTENER_ORCL =
    (ADDRESS = (PROTOCOL = TCP)(HOST = irv-ora-902v)(PORT = 1521))


    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = irv-ora-902v)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl.engdev.local)
    )
    )

    irv-ora-902v =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = irv-ora-902v)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl.engdev.local)
    )
    )


    In the client machine hosts file I configured the IP address for the name of the oralce server which is 'irv-ora-902v'.
    In 'tnsnames.ora' I tried 'SID' instead of 'SERVICE_NAME' still the same error.
    Any early help is appriciated.
    Thanks in Advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when was last time sqlnet worked without error?
    what changed since then?

    issue following OS commands on DB Server

    lsnrctl status
    lsnrctl service

    COPY commands & results then PASTE all back here

    post tail end of listener log file that contains same error code
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21
    Thanks for you qucik response anacedent. Here are the details:

    Listener.log
    -----------
    /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart: Starting Oracle Net Listener
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-MAR-2012 05:50:43
    Copyright (c) 1991, 2009, Oracle. All rights reserved.
    TNS-01106: Listener using listener name LISTENER has already been started
    -----------------------------------------------------------------------------------

    lsnrctl status
    --------------
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-MAR-2012 11:55:29

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC15 21)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 13-MAR-2012 05:45:06
    Uptime 0 days 6 hr. 10 min. 22 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/irv-ora-902v/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC15 21)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=irv-ora-902v)(PORT=1521)))
    Services Summary...
    Service "orcl.engdev.local" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Service "orclXDB.engdev.local" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    The command completed successfully
    -----------------------------------------------------------------------------
    lsnrctl service
    --------------
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-MAR-2012 11:56:33

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

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC15 21)))
    Services Summary...
    Service "orcl.engdev.local" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
    "DEDICATED" established:0 refused:0 state:ready
    LOCAL SERVER
    Service "orclXDB.engdev.local" has 1 instance(s).
    Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
    "D000" established:0 refused:0 current:0 max:1022 state:ready
    DISPATCHER <machine: irv-ora-902v.engdev.local, pid: 4347>
    (ADDRESS=(PROTOCOL=tcp)(HOST=irv-ora-902v)(PORT=55209))
    The command completed successfully
    ------------------------------------------------------------------------------
    I was doing admin work from the server, never try to connect from client machine. When I try to connect the database from client machine I got this error.
    The same tnsname work from the oracle server.....

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    some where near the bottom/end of Listener Log File
    /u01/app/oracle/diag/tnslsnr/irv-ora-902v/listener/alert/log.xml
    a line will contain 12504 at the right margin.

    post excerpt from about 20 lines above 12504 to 20 lines below

    post content of sqlnet.ora file from client; if it exists
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Wink

    Your TNSNAMES format is ever so slightly out... You have an extra closing brace where there shouldn't be... try replacing your tnsnames with the one I provide here:
    Code:
    ORCL = 
      (DESCRIPTION = 
        (ADDRESS = 
          (PROTOCOL = TCP)
          (HOST = irv-ora-902v)
          (PORT = 1521)
        )
        (CONNECT_DATA = 
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl.engdev.local)
        )
      )
    
    irv-ora-902v = 
      (DESCRIPTION = 
        (ADDRESS = 
          (PROTOCOL = TCP)
          (HOST = irv-ora-902v)
          (PORT = 1521)
        )
        (CONNECT_DATA = 
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl.engdev.local)
        )
      )
    This should highlight the importance of formatting. I am starting to form the opinion that badly formatted code, is a bug.... Okay okay; code smell; but it's still really bad.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dayneo View Post
    I am starting to form the opinion that badly formatted code, is a bug. Okay okay; code smell; but it's still really bad.
    I completely agree!

  7. #7
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21
    Thanks for your reply 'dayneo',
    When I copy from vi editor and pasted it here, it changed the format. Still it didnt work.

    Thank you very much ' anacedent ',
    I really appreciate your time. The Oracle was installed by sys admin. I will talk to him to get all the information required and post it back here. Now the priority of my work has changed. I will come back to this forum after a while.
    Thanks everyone for all your help and valuable time.

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @thazanm
    Hmmm.. I'm a dumb ass...
    #1. your tns has the correct braces
    #2. You stated that the same tns worked on the server, but not on the client

    It sounds like the problem must be on the client then, and not actually with the server... based on the fact the tns works on server but not on client. I am trying to reproduce the error on my sandbox env, but so far, no luck.

    What is client OS?

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    What happens if you:
    Code:
    tnsping irv-ora-902v
    from the client?

Tags for this Thread

Posting Permissions

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