Connection Error ORA 12154

Ive recently installed Oracle 10g on my Windows XP and was able to connect. When i did a shutdown immediate and tried to connect again as a sysdba i get an error:

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

Did a few basic checks like Oracle services, listener and all seems up and running. As im studyin Oracle 10g currently, please explain me in simple language meand guide to what i should do to resolve this problem. Any help is much appreciated!

Below is my TNS, listener details for your reference

TNSNAMES

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
===================

LISTENER

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
================================

Using 'conn sys@orcl as sysdba
password: sysdba'

if you shut it down you listener doesnt know about the database anymore

use sqlplus "/ as sysdba" to start it up

You are right in saying that: But what is the solution to this issue and I get an error when i connect to the web iSQL plus - i do get the same error-

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

Results below from command prmpt:
=======================================
SQL> conn / as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production

C:\Documents and Settings\Nitesh>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 13 15:44:01 2007

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

SQL> conn sys@orcl as sysdba
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
==========================================

On the box where you installed the database...

set oracle_sid=your_oracle_sid
sqlplus "/ as sysdba"
startup

Dyou mean where i installed my Oracle software, if yes then c: drive. not sure what if thats what u mean?

Would you know what causing an error when I get an ORA-12514: when i connect as conn sys@orcl as sysdba and via the web?

This worked for me

output in comman prompt.
=========================================
C:\>net start oracleserviceorcl
The requested service has already been started.

More help is available by typing NET HELPMSG 2182.

C:\>set ORACLE_SID=orcl

C:\>sqlplus sys/sysdba as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 13 17:26:41 2007

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


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

so whats the problem? if you shutdown the database you cannot connect using

sys@orcl as sysdba you have to use / as sysdba

Yes I can use using 'conn / as sysdba' but i cannot connect to iSQL Plus via the internet. I get the same error there. Whats the fix for that?

See screen dump. cheers!

i give up, YOU CANT - that is expected behaviour - is you shut down the database you canot get to it from isqlplus

is normal as there is no listener for it anymore!

If you try to explain me may be i will understand then. I know i cannot connect to isql plus when its shutdown. The error i get is when im connected not when im not??

OK let me try
There is a process, called listener out of the database. It is responcible for getting u connected to the database. When the listener does not knows of about the database because it is down or still have not registered to it, the listener cannot connect u
On the other hand, when logged at the machine where the DB is installed, and u use SQL Plus to
conn / as sysdba
the listener is not used for connecting u. SQL PLus by itself gets to the oracle home directory and does what's necessary
However, if U use
conn sys/password@string as sysdba
the @string part points u to the listener and since listener do not knows of your database, the conn fails
iSQL plus allways goes trough the listener so no chance to pass trough SQL plus
I hope I have not missed many things and described it in a way u can understand

Regards

Ok i do understand that now. So how do I make my Listener register to my database?

Would one solution be to make my default listener which registered as DYNAMIC to be STATIC. So would i have to edit my listener.ora file ?

The database registers to the listener not the listener to the database
If the listener operates on the default port (1521) the db registers authomatically when the db starts up, no need to do anything
When it is down however, it is of cource not registered

Dear,

Stop your listener service using:

>lsnrctl stop
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Use this for your tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Use this for your listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = ORCL)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Start the listener using:
>lsnrctl start

Let us know if you face the same problem.

Paresh you are a star! I did exactly what u said and with further issues I am now able to connect. Every thing seems fine now. Thanks very much.
One more query - my sample schemas are locked, so for unlocking the HR account, i typed ALTER user HR account unlock; got reply user altered. But when i enter username as HR and pass: as hr, i get an error saying invalid username and password. What am i doing wrong?

Quote:

---

Originally Posted by nitesh79

Paresh you are a star! I did exactly what u said and with further issues I am now able to connect. Every thing seems fine now. Thanks very much.
One more query - my sample schemas are locked, so for unlocking the HR account, i typed ALTER user HR account unlock; got reply user altered. But when i enter username as HR and pass: as hr, i get an error saying invalid username and password. What am i doing wrong?

---

Dear Nitesh,

Am not a star, am a humble DBA. good to know things are working fine. WHat i did was just manual registration of your DB with the listener.

For your second problem:

Login from sys as sysdba:

SQL> alter user HR identified by HR;

SQL> conn hr/hr@ORCL

Let me know.

did u ever logged into ur isqlplus after installing the database.

check this :
sql>isqlplusctl status/start/stop

start it if it is stopped ....see if above works...

http://localhost:5560/isqlplus.

i hope ur looking for a way to work with isqlplus:

Paresh, yes u are right, may be i didnt register the password hr for this account. it works now. problem solved. U r a very modest person and this site has some very dedicated people. I would recommend it to all.

By the way im from Mumbai too, down in UK for a better job, hopefully get certified very soon once my training is complete. Hey uve been very helpful, would u mind if could get ure email id, as im able to communicate with u well and hope u cld mentor me during my training and exams. Sorry if im asking for alot of favors. Not to worry, if not.

Thanks to Zakriyamhd too, im now able to conn to isqlplus too, for now atleast :)

By the way one more query for you all: ive noticed that an even called 'emdctl.exe' is running on computer and its using a lot of my precious memory.
Im guessing this running application indicates to me that it cannot communicate with some service. Dont get any pop ups but i think it relates to Oracle performance monitoring related tools. How can i get rid of this?