Home » RDBMS Server » Networking and Gateways » Connection Error ORA 12154
Connection Error ORA 12154 [message #237168] Sat, 12 May 2007 17:13 Go to next message
nitesh_79
Messages: 6
Registered: May 2007
Junior Member
(Sorry, similar post did not solve my issue)

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'
Re: Connection Error ORA 12154 [message #237481 is a reply to message #237168] Mon, 14 May 2007 11:17 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi
One reason is default listener registration is DYNAMIC you have to change DYNAMIC to STATIC.
edit your listener.ora file like below 

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
[B](SID_DESC = 
(SID_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\10.1.0\Db_1) )[/B])

C:\>LSNRCTL stop
C:\>REM WAIT FEW SECONDS
C:\>LSNRCTL start
Re: Connection Error ORA 12154 [message #237525 is a reply to message #237481] Mon, 14 May 2007 14:30 Go to previous messageGo to next message
nitesh_79
Messages: 6
Registered: May 2007
Junior Member
Thanks for your reply. I did exactly what u mentioned. I am still not able to connect to isql plus. It hangs up. Even in commnad promt when i connect as 'conn sys@orcl as sysdba' and enter my password it just stops there without a reply. Is it because I changed my SID_Name to 'orcl'?
Re: Connection Error ORA 12154 [message #237527 is a reply to message #237168] Mon, 14 May 2007 14:44 Go to previous messageGo to next message
nitesh_79
Messages: 6
Registered: May 2007
Junior Member
Now im getting the below error when connecting to iSQL Plus.

Is it because I need to edit my TNS file too as done with listener file??

ERROR - ORA-28547: connection to server failed, probable Oracle Net admin error
Re: Connection Error ORA 12154 [message #237577 is a reply to message #237527] Tue, 15 May 2007 00:10 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
ERROR - ORA-28547: connection to server failed, probable Oracle Net admin error


http://www.dbmotive.com/oracle_error_codes.php?errcode=28547

[Updated on: Tue, 15 May 2007 00:11]

Report message to a moderator

Re: Connection Error ORA 12154 [message #237827 is a reply to message #237577] Tue, 15 May 2007 18:12 Go to previous messageGo to next message
nitesh_79
Messages: 6
Registered: May 2007
Junior Member
Ok it seems the ORA-28547 error has resolved by itself. I still get the ORA 12154 error when i connect to iSQL Plus. It seems very obvious that when I bypass the listener by using 'conn / as sysdba' in command prompt I am able to connect. So when connecting via iSQL Plus it uses the info in Listener file to connect to the database. So for some reason it does register to DB by default when installed.

Is there no quick solution to this. Its very annoying.

Like u said to register using dynamic info by changed my SID_name from "orcl". By doing this, when connection it hangs in limgo mode,which means it hangs without a reply from service.

======
LISTENER ORA file

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

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))
)
)
========

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)
)
)
Re: Connection Error ORA 12154 [message #237828 is a reply to message #237168] Tue, 15 May 2007 18:23 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Note:300547.1
Subject: Troubleshooting Guide for iSQL*Plus issues
Previous Topic: listener and password
Next Topic: DB Link issue with different versions of Oracle
Goto Forum:
  


Current Time: Tue Dec 06 04:27:12 CST 2016

Total time taken to generate the page: 0.15234 seconds