ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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

Hybrid View

  1. #1
    Join Date
    Oct 2010
    Posts
    8

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

    First of all, I've Google'ed the Oracle error to death w/o any luck of getting any of the suggestions to work...

    We have Oracle running on several servers and just got it installed on a new server. But when trying to run sqlplus as system user, we get the error:

    Code:
    ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
    Obviously the first thing I did was look in our tnsnames.ora to make sure the SERVICE_NAME parameter was in there properly:

    (FYI the server name is Borg. Oracle is running on Borg and I'm trying to connect to it locally. This is Oracle 11g Release 2 (11.2.0.1.0)).

    tnsnames.ora:
    Code:
    BORG =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = borg)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = BORG)
        )
      )
    And here is listener.ora
    Code:
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = BORG)(PORT = 1521))
        )
      )
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = BORG)
          (GLOBAL_DBNAME = BORG)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
      )
    
    ADR_BASE_LISTENER = /u01/app/oracle
    Okay so clearly tnsnames.ora is NOT missing the SERVICE_NAME in CONNECT_DATA. It's there and it looks correct to me.

    When researching the error the suggested CAUSE is this:

    Code:
    The listener was not configured with a default service and SERVICE_NAME was missing from the CONNECT_DATA received by the listener.
    and the suggested ACTION is this:

    Code:
    Possible solutions are:
    
    - Configure DEFAULT_SERVICE parameter in LISTENER.ORA with a valid service name. Reload the listener parameter file using reload []. This should be done by the LISTENER administrator.
    
    - If using a service name, Check that the connect descriptor corresponding to the service name in TNSNAMES.ORA has a SERVICE_NAME or SID component in the CONNECT_DATA.
    So let me take those solutions one a time:

    1. I've looked all over the place, even in at the listener.ora in the SAMPLES directory and I don't see anywhere that shows the DEFAULT_SERVICE parameter and how it is used or where to place it in the listener.ora. Does anyone know anything about this? It's suggested to do this in a million places but no where does it say where EXACTLY to place this parameter.

    2. As I've already said, my tnsnames.ora already has the SERVICE_NAME in the CONNECT_DATA.

    So does anyone have any clues here?

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Is the service registered with DB? check the init parameter service_names, it should return BORG other wise please register the service with DB.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Oct 2010
    Posts
    8
    While I know a ton about MySQL, I'm very new to Oracle and how it works. Can you give me some more explaination? Like how do I check for an init parameter called "service_names"? Is it like a environment variable or something? And if it's not there, how do I register the service with the DB?

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Log in to Oracle as DBA user.

    SQL> show parameter local_listener

    should return LISTENER else

    SQL> alter system set LOCAL_LISTENER='LISTENER' scope=both;
    SQL> alter system register;

    SQL> show parameter service_names

    should return BORG else

    SQL> alter system set SERVICE_NAMES='BORG' scope=both;


    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = BORG)(PORT = 1521))
    )
    )
    Also. cross check the host name. Is BORG resolving the host name? You can also try using IP address instead of host name.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Oct 2010
    Posts
    8
    But the problem is that I can't load up sqlplus because thats when I get the above error. Is there any other method to doing what you are describing?

  6. #6
    Join Date
    Oct 2010
    Posts
    7
    I notice that you said you were connecting locally, on the Borg host. Might not be a listener issue at all. Can you please tell us your OS, and paste in exactly what command you are typing to start sqlplus? Also, type this at the OS prompt (assuming unix) and paste in what happens:

    echo $ORACLE_SID
    echo $ORACLE_HOME
    sqlplus '/ as sysdba'

  7. #7
    Join Date
    Oct 2010
    Posts
    8
    Yes I know the Oracle Home and SID are set correctly, I checked those first. Here's what I got:

    Code:
    ORACLE_BASE="/u01/app/oracle"
    ORACLE_HOME="/u01/app/oracle/product/11.2.0/db_1"
    ORACLE_SID="BORG"
    This system is Fedora 11.

    When we've been trying to start sqlplus we've been using the following command:

    Code:
    sqlplus system@borg
    I was unaware of the syntax you used for sqlplus (like I said I've VERY new to Oracle but learning fast) and I just tried it (logged into the server as user 'oracle' was the only way I could get it to work). And sure enough it brought up the SQL> prompt for me, no password required. Is that normal?

    Anyways, I proceeded with your previous instructions:

    Code:
    SQL> show parameter local_listener
    ORA-01034: ORACLE not available
    Process ID: 0
    Session ID: 0 Serial number: 0
    
    SQL> alter system set LOCAL_LISTENER='LISTENER' scope=both;
    alter system set LOCAL_LISTENER='LISTENER' scope=both
    *
    ERROR at line 1:
    ORA-01034: ORACLE not available
    Process ID: 0
    Session ID: 0 Serial number: 0
    
    SQL> show parameter service_names
    ORA-01034: ORACLE not available
    Process ID: 0
    Session ID: 0 Serial number: 0
    Okay so obviously apparently Oracle isn't even running?

    Back at the command prompt:

    Code:
    [oracle@borg db1]$ lsnrctl status
    
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-OCT-2010 18:29:29
    
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=borg.ourdomain.com)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date                20-OCT-2010 13:39:07
    Uptime                    1 days 4 hr. 50 min. 22 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/borg/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=borg.ourdomain.com)(PORT=1521)))
    Services Summary...
    Service "BORG" has 1 instance(s).
      Instance "BORG", status UNKNOWN, has 1 handler(s) for this service...
    Service "borg.ourdomain.com" has 1 instance(s).
      Instance "borg", status READY, has 1 handler(s) for this service...
    Service "borgXDB.ourdomain.com" has 1 instance(s).
      Instance "borg", status READY, has 1 handler(s) for this service...
    The command completed successfully
    I'm not sure why it shows 3 services in the bottom: BORG, borg.ourdomain.com and borgXDB.ourdomain.com.

    Also:
    Code:
    [oracle@borg db_1]$ dbstart
    Processing Database instance "borg": log file /u01/app/oracle/product/11.2.0/db_1/startup.log
    It seems to start up fine without errors

    Code:
    [oracle@borg db_1]$ lsnrctl start
    
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-OCT-2010 18:31:31
    
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    
    TNS-01106: Listener using listener name LISTENER has already been started
    [oracle@borg db_1]$ lsnrctl stop
    
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-OCT-2010 18:31:36
    
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=borg.answeron.com)(PORT=1521)))
    The command completed successfully
    and then

    Code:
    [oracle@borg db_1]$ lsnrctl start
    
    LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 21-OCT-2010 18:31:49
    
    Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    
    Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
    
    TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Log messages written to /u01/app/oracle/diag/tnslsnr/borg/listener/alert/log.xml
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=borg.answeron.com)(PORT=1521)))
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=borg.answeron.com)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date                21-OCT-2010 18:31:49
    Uptime                    0 days 0 hr. 0 min. 0 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/borg/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=borg.answeron.com)(PORT=1521)))
    Services Summary...
    Service "BORG" has 1 instance(s).
      Instance "BORG", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    I appreciate your help. Any insight is helpful. I'm sure it might just be some simple little thing that is the problem. The fact that the SQL prompt it telling me that Oracle isn't running yet dbstart is starting it successfully is kind of odd.

    Also in /etc/oratab I have

    Code:
    # This file is used by ORACLE utilities.  It is created by root.sh
    # and updated by the Database Configuration Assistant when creating
    # a database.
    
    # A colon, ':', is used as the field terminator.  A new line terminates
    # the entry.  Lines beginning with a pound sign, '#', are comments.
    #
    # Entries are of the form:
    #   $ORACLE_SID:$ORACLE_HOME::
    #
    # The first and second fields are the system identifier and home
    # directory of the database respectively.  The third filed indicates
    # to the dbstart utility that the database should , "Y", or should not,
    # "N", be brought up at system boot time.
    #
    # Multiple entries with the same $ORACLE_SID are not allowed.
    #
    #
    borg:/u01/app/oracle/product/11.2.0/db_1:Y
    Which from what I read online this means that Oracle should start up when the machine boots up, so I'm not sure why it isn't.

  8. #8
    Join Date
    Oct 2010
    Posts
    7
    Issuing sqlplus that way gets you in as SYS using OS authentication instead of database authentication. SYS is kinda like root -- not a good idea to use most of the time, but there if you need it. You can set other users up for OS auth if you ever wanted to.

    I have a suspicion you are running into case sensitivity issues. Some of the config files you listed don't care, some do. Can you type this and see if 'borg' is in there as a running database? I think you will see that 'borg' is up and running but 'BORG' is not. Also you can set $ORACLE_SID to borg and then try sqlplus again (both ways). You can try some of the parameters that failed before to see if it is running.

    ps -ef | grep pmon

  9. #9
    Join Date
    Oct 2010
    Posts
    8
    Code:
    [root@borg ~]# ps -ef | grep pmon
    oracle   26079     1  0 18:31 ?        00:00:03 ora_pmon_borg
    root     28679 28619  0 23:05 pts/2    00:00:00 grep pmon
    Set $ORACLE_SID="borg" instead of "BORG" and now we are getting somewhere:

    Code:
    [oracle@borg root]$ sqlplus '/ as sysdba'
    
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 21 23:11:42 2010
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> show parameter local_listener
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    local_listener                       string
    I'll change all the BORG references to borg and see if that makes a difference. More on this tomorrow. Thanks for the help so far.

  10. #10
    Join Date
    Oct 2010
    Posts
    8
    Also one weird thing:

    Code:
    SQL> show parameter service_names
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    service_names                        string      borg.ourdomain.com
    How come the service name comes up as borg.ourdomain.com when in the tnsnames.ora it clearly lists the service name as just "BORG" ? The only place I can see where "borg.ourdomain.com" is defined is an environment variable called $ORACLE_HOSTNAME="borg.ourdomain.com". Is that anything to do with it?

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