ORA : 12154 TNS Error

[srikanthan]

Hi,

I have been working in oracle installed in windows. everythng was working fine... when i shut down the database and loggd in again it showed me the following error message..

ORA-12154: TNS Could not resolve identifier..

CAn anyone help in resolving this issue. I am trying to connect from the same machine in which database is running

thanks,
srikanth

[Bore]

Please describe more detailed what u use to login etc. and give the exact error message

[srikanthan]

Hi Bore,

thanks for your reply,

I connected to database from cmd prompt by typing sqlplus "/as sysdba"..
While performing some operation i need to shut the database so i gave
shut immediate;

after that inadvertantly i exited the command prompt, when i tried loggin in again using sqlplus "/as sysdba" it showed me the following error..

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jun 1 07:00:23 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-12154: TNS:couldnot resove identifier

[srikanthan]

Hi Bore,

thanks for your reply,

I connected to database from cmd prompt by typing sqlplus "/as sysdba"..
While performing some operation i need to shut the database so i gave
shut immediate;

after that inadvertantly i exited the command prompt, when i tried loggin in again using sqlplus "/as sysdba" it showed me the following error..

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jun 1 07:00:23 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-12154: TNS:couldnot resove identifier

[PAVB]

Have you set your ORACLE_SID?

[santoshsingh]

1>i think needs to check out Tnsnames.ora file
whether right Service name has been mentioned or not.

2> check OracleService whether it's running or not .you can find the service in controlpanel->administrative tools->services
the service name will be OracleService

3>Listener is running or not...

[Bore]

santoshsingh,
sorry but when u do conn / as sysdba, none of these is relevant
1) U do not use tnsnames.ora
3) U do not use listener

So point 2 is OK, check if the service is running
also support PAVB,
check how your ORACLE_SID is set, or to eliminate that reason, try the following at the cmd prompt
set ORACLE_SID=YOURSID
echo %ORACLE_SID%
sqlplus / as sysdba

and if that works, then Oracle_sid is the problem

[srikanthan]

Hi Bore,

thanks for your reply and solution...
i restarted the service and gave the command set ORACLE_SID='my sid' and connected again using sqlplus /as sysdba ... it worked...

thank you, thanks a lot for your guidance..
one question : may i know why is that happening..? thanks again..

[Bore]

Well,
first if the service is down, u cannot connect to the database. This is because of the windows architecture. Even when the service is started the database can still be down, but u cannot even connect as sysdba if the service is down
About the Oracle_sid, when u issue
conn / as sysdba
the SQL Plus reads the value of the env variable ORACLE_SID and tries to connect u to the instance with this name. Usually Oracle sid on windows is set by default during the installation and it is set in the registry. U can find something about that in HKEY_LOCAL_MACHINE->Software->Oracle registry entry. We just gave it no chance to be wrong, by setting it explicitly in your OS prompt
And finally, to be precise,
the problem with the stopped service was posted by srikanthan and the problem with the wrong ORACLE_SID was posted by PAVB. I just summarized :-)

Good luck
Boris

[srikanthan]

Hi Boris,

Thanks for your reply..