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

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

  1. #11
    Join Date
    Oct 2010
    Posts
    8
    Okay I think I have it working now. I guess the problem was the uppercase vs lowercase BORG vs borg stuff in the tnsnames and listener.ora files. Here is what I have in the end:

    listener.ora
    Code:
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = borg.ourdomain.com)(PORT = 1521))
        )
      )
    tnsnames.ora
    Code:
    borg =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = borg.ourdomain.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = borg)
        )
      )
    And then, as a normal user (not root or oracle user) I run the following command:

    Code:
    > sqlplus system
    Then input my password for the user 'system' and then it successfully gives me the SQL prompt.

    However, if I try this command:

    Code:
    > sqlplus system@borg
    or
    Code:
    > sqlplus system@borg.ourdomain.com
    It asks me for the password and then gives the following error again:

    Code:
    ERROR:
    ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
    Got any clues why?

  2. #12
    Join Date
    Jan 2014
    Location
    New York, NY
    Posts
    1
    I struggled, and struggled, and struggled with this problem. I eventually figured out what was going on. I had installed both server and client on the same machine. This results in two Oracle directories - one for client and one for server. This also results in TWO tns_names directories. Likely the tns_names directory in your client folder will be EMPTY. Thus, any client you use (including java code) can't connect as there is no tnsnames.ora file to use. It won't use the server tnsnames.ora file - it will look for a tnsnames.ora file in the client directory instead and give up when it's not there. When you connect with SQLPLUS, it's bypassing the tnsnames.ora file.

    You've got two choices to fix this problem. One is to simply copy the tnsnames.ora and listener.ora file from the server directory to the client directory. For example, on my system the directories are as follows:

    C:\app\BioReg\product\11.2.0\dbhome_1\NETWORK\ADMIN
    C:\app\BioReg\product\11.2.0\client_1\network\admin

    The better solution is to define the Windows Environment variable TNS_NAMES and enter your server tns_names directory path. This way, you don't have to copy the tnsnames.ora and listener.ora file over to the client folder each time you make a change.

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