• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 23040
  • Last Modified:

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


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.



1 Solution
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
ljubomir_buturovicAuthor Commented:
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.


Peter KwanAnalyst ProgrammerCommented:
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.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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
ljubomir_buturovicAuthor Commented:

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    vectorsciences.com      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.vectorsciences.com pula

the message is:

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pula.vectorsciences.com)(PORT=1521)))

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.

Submitted to PAQ with points refunded (500)

Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now