[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-05-04
7
Medium Priority
?
22,709 Views
Last Modified: 2013-12-11
Hello,

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 10.1.0.3.0 - Production on Wed May 4 23:03:04 2005

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

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

======================================================

tnsping reveals that the Net8 connectivity is OK:

======================================================

% tnsping test

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

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

Used parameter files:
/u01/app/oracle/OraHome_1/network/admin/sqlnet.ora

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 10.1.0.2.0 - Production on 04-MAY-2005 23:03:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pula)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "test" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:493 refused:0 state:ready
         LOCAL SERVER
Service "testXDB" has 1 instance(s).
  Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:972 state:ready
         DISPATCHER <machine: pula, pid: 2376>
         (ADDRESS=(PROTOCOL=tcp)(HOST=pula)(PORT=33056))
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=127.0.0.1)(PORT=54983)) * 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=192.168.0.5)(PORT=32876)) * 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.

Thanks,

Ljubomir


0
Comment
Question by:ljubomir_buturovic
6 Comments
 
LVL 9

Expert Comment

by:Metanil
ID: 13943176
Ljubomir,
I am confuse. The above outputs are the result of which computer. I mean server or client.??

Hints.
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
0
 

Author Comment

by:ljubomir_buturovic
ID: 13946277
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.

Ljubomir


0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 13954838
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 21

Expert Comment

by:oleggold
ID: 13955169
Hi ljubomir_buturovic,
those are from my other answer,follow those steps :
1.
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 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =10.237.237.211)(PORT = 1521))
    (CONNECT_DATA = (SID = existingSIDdup))
  )
where 10.237.237.211 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:
NAMES.DEFAULT_DOMAIN=
and SQLNET.AUTHENTICATION_SERVICES=
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
CONNECT / AS SYSDBA



Hope It helps
Cheers!
0
 

Author Comment

by:ljubomir_buturovic
ID: 13956202
Hello,

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:

127.0.0.1       pula    vectorsciences.com      localhost

and the listener.log message was:

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

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

127.0.0.1       localhost
192.168.0.4     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.

Ljubomir
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 13996786
Submitted to PAQ with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

873 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