Tuesday, June 24, 2008

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

In this post I will try to show how efficiently we can avoid error ORA-12514. My suggestion is whenever you get this error forget about tnsnames.ora and other stuff related to it. Start fixing problem step by step.

Problem Description:
Whenever you try to connect to database the following error comes.
-bash-3.00$ sqlplus arju/a:1522/dba

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 24 06:35:02 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

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

Cause of The problem:

The services named issued with the connection identifier has not been registered yet.

Solution of The Problem:
After getting above error forget any tnsnames.ora file. Issue lsnrctl status command on the server to which you try to connect like,
-bash-3.00$ lsnrctl status

LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 24-JUN-2008 07:17:56

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC2)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 24-JUN-2008 07:01:52
Uptime 0 days 0 hr. 16 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=neptune)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbase" has 1 instance(s).
Instance dbase, status READY, has 1 handler(s) for this service...
Service "dbaseXDB" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
Service "dbase_XPT" has 1 instance(s).
Instance "dbase", status READY, has 1 handler(s) for this service...
The command completed successfully

Now closely look at the bolded items above , it is host, port, service and optionally instance.
Now use it in the connection descriptor as follows.

sqlplus user_id/password@host:port/service

Like here,
$sqlplus arju/a@neptune:1522/dbase
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 24 07:21:19 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

This method of connecting to database is called Easy Connect Naming Method.

Related Documents:
ORA-12541: TNS:no listener

11 comments:

Lisa said...

Good post. Thank you for showing the easy way.

Roger Jakobsen said...

Hi, good post!
I'm currently struggeling with this error, but when I run lsnrctl status I dont get a lot of instances. Do you know how I can start the needed services?

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 10-SEP-2008 10:53:59

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 10-SEP-2008 10:15:13
Uptime 0 days 0 hr. 38 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
Listener Log File C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=RJA.id.local)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\>


mvh rogerj

Arju said...

In sql*plus just enter
alter system set service_name=rja.id.local;

Roger Jakobsen said...

Thanks for your answer.
I tried to run the command you gave, but I cannot connect: As I try to login to sqlplus in cmd i get:
ORA-01041: internal error. hostdef extension doesn't exist

Arju said...

exit;
And re-connect.

Roger Jakobsen said...

The problem is that I cannot even start sqlplus, heres what happens if I try:
C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on On Sep 10 14:29:11 2008

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

Enter user-name: SYSTEM
Enter password:
ERROR:
ORA-28547: connection to server failed, probable Oracle Net admin error

I am positive that I give the correct username and password.

I think the problem is that I only get 2 service instances up n running after the install. When I run insrctl status i get:
C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 10-SEP-2008 14:32:38

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 10-SEP-2008 14:23:36
Uptime 0 days 0 hr. 9 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
Listener Log File C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=RJA.id.local)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


I have re-installed, re-booted and reinstalled again several times but the problems persist.

Arju said...

Re-read the post. Try not to use tnsnames.ora as in the post shown.

Roger Jakobsen said...

I finally solved the problem I described in earlier comments.

If you want to install oracle xe on a computer that has a dynamic IP configuration, DHCP, you need to install a loopback adapter.

Here is a link to a microsoft guide that actually worked.

http://support.microsoft.com/kb/839013

Best of luck!

rit said...

m gettin the following error
ORA-28547: connection to server failed, probable Net8 admin error

Arju said...

Have a look at
http://arjudba.blogspot.com/2008/10/ora-28547-connection-to-server-failed.html

Anonymous said...

i have the below while doing backup.

Recovery Manager complete.
[Major] From: ob2rman@somyl01 "ovpi" Time: 04/11/12 14:35:56
The database reported error while performing requested operation.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04004: error from recovery catalog database: ORA-12514: TNS:listener does
not currently know of service requested in connect descriptor.

I tried all the ways as per your above post and every thing looks seeming working.