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
Tuesday, June 24, 2008
Subscribe to:
Post Comments (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML
11 comments:
Good post. Thank you for showing the easy way.
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
In sql*plus just enter
alter system set service_name=rja.id.local;
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
exit;
And re-connect.
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.
Re-read the post. Try not to use tnsnames.ora as in the post shown.
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!
m gettin the following error
ORA-28547: connection to server failed, probable Net8 admin error
Have a look at
http://arjudba.blogspot.com/2008/10/ora-28547-connection-to-server-failed.html
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.
Post a Comment