unable to connect to Oracle remotely, ORA-12514 TNS:listener does not currently know of service requested in connect

Posted on 2005-05-04
Last Modified: 2013-12-11

I have a problem accessing an Oracle instance remotely.

I have two Linux computers, one is running Oracle 10g database server,
the other has Oracle 10g client software installed.

The problem is accessing the instance `test' from the client computer
using sqlplus.

I can access the instance on the server, using sqlplus. However, from
client, I'm getting the following message:


% sqlplus username/password@test

SQL*Plus: Release - Production on Wed May 4 23:03:04 2005

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

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


tnsping reveals that the Net8 connectivity is OK:


% tnsping test

TNS Ping Utility for Linux: Version - Production on 04-MAY-2005 23:06:35

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pula)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test)))
OK (0 msec)


According to Oracle literature, this means that that the instance is
not running, or is not registered with the listener. But the listener
seems to indicate otherwise:


% lsnrctl services

LSNRCTL for Linux: Version - Production on 04-MAY-2005 23:03:37

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

Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      "DEDICATED" established:0 refused:0
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
      "DEDICATED" established:493 refused:0 state:ready
Service "testXDB" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: pula, pid: 2376>
The command completed successfully


Here is listener log in case of successful connection:


04-MAY-2005 23:25:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=pula)(USER=ljubomir))) * (ADDRESS=(PROTOCOL=tcp)(HOST= * establish * test * 0


Here is the log in case of failed connection:


04-MAY-2005 23:26:25 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)(CID=(PROGRAM=)(HOST=basel)(USER=ljubomir))) * (ADDRESS=(PROTOCOL=tcp)(HOST= * establish * test * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor


Any ideas why I cannot connect to the `test' instance remotely?

I tried adding the `test' instance in listener.ora explicitly, and
restarting the listener. This doesn't help - the behavior is exactly
the same.



Question by:ljubomir_buturovic
    LVL 9

    Expert Comment

    I am confuse. The above outputs are the result of which computer. I mean server or client.??

    Server Side
    1. Check whether instance is running on server.
        SQL>select * from v$instance; --issue in server computer loggin as sys
    2. Check whether listener is running on server.
      $lsnrctl status

    Client Side
    1. Check you have proper TNS information in tnsnames.ora
       run netmgr
                 eg: $netmgr
       if there is no information on tree Service Naming
            Add new value giving IP, SID of server computer.
            if a value already exist verify it if it has incorrect value.

     eg: suppose you add name 'TestNew' in netmgr window.
     try issuing command in sqlplus like:
     connect username/password@TestNew

    Note: here the connection identifier is TestNew not test, but TestNew have information of your database server when creating it using netmgr.
    Note2: You dont' have to create listener in your client.

    If above process doesn't work at all here is hardcoded one(issue this in ur sqlplus):

    sqlplus> connect userid/password@'(description=(address=(protocol=tcp)(host=phula)(port=1521))(connect_data=(service_name=test)))';

    Note: phula --> u can give IP ---> i assume this is your database server.
             servicename=test  ---> test is SID in your database server.

    Hope this may help

    Author Comment

    I thought it should be clear from the context where the commands were issued from... anyway:

    - the original sqlplus statement which produces error message is executed on the client host, as indicated above (as I said, I do have connectivity on the server)

    - the tnsping is also executed on the client

    - lsnrctl services command is issued on the server (since that's where the listener is running). Likewise, the listener logs are produced on the server.

    I think the instance is up, and so is the listener... as proven by the logs, and the fact that I can connect to the instance on the server. However, I will try the commands you had suggested and let you know.

    Thanks for your help.


    LVL 16

    Expert Comment

    by:Peter Kwan
    Please check carefully your service name. You can do that by login to your server using SQLPLUS using any account and execute
    the following command:

    SQLPLUS> show parameter service_name

    Copy the value and set it in the SERVICE_NAME of your TNSNAMES.ORA file.
    LVL 21

    Expert Comment

    Hi ljubomir_buturovic,
    those are from my other answer,follow those steps :
    In Your<ORACLE_HOME>\network\admin directory 2 files at least must be available:
    tnsnames.ora and sqlnet.ora

     Comment from oleggold        05/08/2005 10:47AM
    2.Try to edit TNSNAMES.ORA as follows:
    existingSIDdup =
        (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
        (CONNECT_DATA = (SID = existingSIDdup))
    where is Your Db host IP

     Comment from oleggold        05/08/2005 10:49AM
    3.Now ,put attention to the second file sqlnet.ora,
    it can have these lines:
    put # before each one of them

     Comment from oleggold        05/08/2005 10:51AM
    In any case even if You can't connect in remote ,You'll be able to connect from the db host by using:
    set ORACLE_SID=existingSIDdup

    Hope It helps

    Author Comment


    The problem is solved. It was caused by an issue in /etc/hosts file on
    the server.

    The key to resolving the problem is listener.log file. When listener
    is started, it writes a message starting with `Listening on...' in
    this file. This line is helpful in resolving connectivity issues.

    The original /etc/hosts file on the server had:       pula      localhost

    and the listener.log message was:


    This means that the listener is only listening to connections directed
    to host. This prevents remote connections (since you cannot
    specify in a remote connect string).  After modifying
    /etc/hosts to       localhost pula

    the message is:

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(

    and the remote sqlplus works.

    This also means that if the server is using DHCP (as is my case), you
    have to manually modify /etc/hosts each time the address changes. This
    is rather inconvenient, but I'm not sure if it can be avoided. It
    appears that Oracle server is not meant to be run on server with
    dynamic IP address.

    Thanks everyone for your help. However I'm not awarding the points
    since I solved the problem.

    LVL 1

    Accepted Solution

    Submitted to PAQ with points refunded (500)

    Community Support Moderator

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now