8 Replies Latest reply: Dec 26, 2012 7:56 AM by yoonas RSS

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

    823085
      Hi Guys,

      I have actually resolved this issue but I wonder is this because of an Oracle database defects or something.

      This issue can be reproduced easily, just follow below steps:

      1, download sqlplus instant client and basic lite instant client (Windows XP SP2 32bit, Database Server is RHEL5.8 32bit with 11gR2)
      2, the sqlplus instant client actually does not have all the dll files needed to run the program, this gives me a bit of headache, I wonder which genius in Oracle comes up with this idea? Why not just include all files? Isn't when we download something we are expecting to double click it to run? All right, let's go on.
      3, copy files "oci.dll", "orannzsbb11.dll" and "oraociicus11.dll" from basic lite instance client to the same directory as the sqlplus
      4, create an tnsnames.ora file just like everybody else, here is the file:

      orcl =
      (description=
      (address=(protocol=tcp)(host=192.168.56.58)(port=1521))
      (connect_data=(service_name=orcl.localdomain))
      )

      5, set enviroment variable SQLPATH, TNS_ADMIN, PATH to the path of the sqlplus
      6, now go to command prompt, connect to sqlplus with command:
      sqlplus admin/password1@orcl
      the result is error message:

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

      7, ok, then I tried to use the connection description in the sqlplus command like this:
      sqlplus admin/password1@\" (description=(address=(protocol=tcp)(host=192.168.56.58)(port=1521))(connect_data=(service_name=orcl.localdomain)))\"
      this time it worked perfectly

      8, then after about an hour of online searching, I finally resolved it, I added this line to the listener.ora file on the database server:

      default_service_listener=orcl.localdomain

      9, then remove the "connect_data" from the tnsnames.ora file of my sqlplus client folder. So the new tnsname.ora looks like this:

      orcl =
      (description=
      (address=(protocol=tcp)(host=192.168.56.58)(port=1521))
      )

      10, now I can connect to the database with below command, no more error message
      sqlplus admin/password1
      what happen is that I specified a default service for the listener, so now I can connect. The draw back is that I can only connect to one database service right now.


      The problem here is that when using tnsnames.ora file, the sqlplus seems to unable correctly identify the "service_name" provided in the file. As in the example shows, there is absolutely no problem with the network or any other configurations but the sqlplus parsing the tnsnames.ora file.


      Please let me know if this is due to a defect or something? I downloaded the lastest instant client from Oracle site just a moment ago.
        • 1. Re: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
          sb92075
          on DB Server issue following OS commands

          lsnrctl status
          lsnrctl service

          COPY the results then PASTE all back here
          • 2. Re: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
            823085
            lsnrctl service
            LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2012 11:39:45

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
            Services Summary...
            Service "orcl.localdomain" has 1 instance(s).
            Instance "orcl", status READY, has 1 handler(s) for this service...
            Handler(s):
            "DEDICATED" established:1 refused:0 state:ready
            LOCAL SERVER
            Service "orclXDB.localdomain" 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: localhost.localdomain, pid: 2574>
            (ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=13561))
            The command completed successfully


            lsnrctl status
            LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2012 11:39:30

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

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
            STATUS of the LISTENER
            ------------------------
            Alias LISTENER
            Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
            Start Date 22-DEC-2012 10:46:30
            Uptime 0 days 0 hr. 52 min. 59 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/localhost/listener/alert/log.xml
            Listening Endpoints Summary...
            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
            Services Summary...
            Service "orcl.localdomain" has 1 instance(s).
            Instance "orcl", status READY, has 1 handler(s) for this service...
            Service "orclXDB.localdomain" has 1 instance(s).
            Instance "orcl", status READY, has 1 handler(s) for this service...
            The command completed successfully
            • 3. Re: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
              sb92075
              Eric.Zhou wrote:
              lsnrctl status
              LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2012 11:39:30

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

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
              STATUS of the LISTENER
              ------------------------
              Alias LISTENER
              Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
              Start Date 22-DEC-2012 10:46:30
              Uptime 0 days 0 hr. 52 min. 59 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/localhost/listener/alert/log.xml
              the listener Log File (above) would contain a record show the 12504 error status & details of the connection request it received.

              no remote client can ever connect to any listener that uses "localhost"
              • 4. Re: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                823085
                Finally, just a moment ago I managed to resolve this issue.

                I edited my tnsnames.ora file, I change the host field value from:
                orcl =
                (description = (address = (protoco=tcp)(host=*localhost.localdomain*)(port=1521))
                .....
                to
                orcl =
                (description = (address = (protoco=tcp)(host=*192.168.56.58*)(port=1521))
                .....
                The address "192.168.56.58" are the server's eth0 address.

                I am kinda wondering why the change made here actually worked?
                • 5. Re: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                  823085
                  Sorry, there is a bit confusion here, the tnsnames.ora file I changed are the ones on the server, not the ones for the client. My client use "host=192.168.56.58" since the beginning.

                  What I wondering is why one the database server the server's eth0 address are not equal to server's hostname.

                  Another question is, even if the server's tnsnames.ora using "host=locahost.localdomain", a remote client still able to connect with easy connect naming, but not with the same connect information in local tnsnames.ora?
                  • 6. Re: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                    yoonas
                    Hi,

                    http://www.oracle.com/technetwork/database/enterprise-edition/oraclenetservices-neteasyconnect-133058.pdf
                    http://docs.oracle.com/cd/E14072_01/network.112/e10836/naming.htm#i498306

                    Can you check if an entry is there for "192.168.56.58" in the /etc/host on the database server.

                    Regards
                    Yoonas
                    • 7. Re: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                      823085
                      Hi yoonus,

                      There is no "192.168.56.58" defined in /etc/host file.

                      Thanks
                      • 8. Re: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
                        yoonas
                        Hi,

                        That must the reason at the time of installation installer took your locahost has host name configured everything based on that.

                        http://docs.oracle.com/cd/B28359_01/install.111/b32006/reqs.htm#i1011417

                        Regards
                        Yoonas