Home » SQL & PL/SQL » Client Tools » Unable to connect to Oracle from Toad
Unable to connect to Oracle from Toad [message #467880] Wed, 28 July 2010 00:03 Go to next message
dearkiran
Messages: 8
Registered: July 2010
Junior Member
Hi,

I installed Oracle 10g XE and Toad 10.5 in my home system.
I am able to connect to Oracle from Oracle XE homepage and XE SQL command line but when I try to connect to Oracle from toad 10.5 I am getting the error 'TNS: could not resolve the connect identifier specified (XE)' .

From toad I tried direct and TNS options but it did not work. My tns ora file looks like below.


XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Anu-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

TNS_ADMIN variable has correct parameter and Toad is directing the correct TNS file. I tried changing hostname with my system's ip but still I am unable to connect to Oracle from Toad.

Can you please help in solving the above problem.

Thanks
Kiran.

Re: Unable to connect to Oracle from Toad [message #467884 is a reply to message #467880] Wed, 28 July 2010 00:39 Go to previous messageGo to next message
Littlefoot
Messages: 19626
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is written in TOAD's Login window, under "Installed Clients" section? There's the "Connect Using" dropdown list. Does it point to XE?

Is there SQLNET.ORA file in the same directory where is TNSNAMES.ORA file? How does it look like?
Re: Unable to connect to Oracle from Toad [message #467935 is a reply to message #467884] Wed, 28 July 2010 05:30 Go to previous messageGo to next message
dearkiran
Messages: 8
Registered: July 2010
Junior Member
Hi,

Thanks for your reply. Please see the attached the screenshot of toad. Both SQLNET and TNSNAMES ora files are in the same dir
'C:\Oracle\app\oracle\product\10.2.0\server\NETWORK\ADMIN\'

The sqlnet.ora file has only below parameter.

SQLNET.AUTHENTICATION_SERVICES = (NTS)


I am unable to findout any error. Can you please suggest.

Thanks,
Kiran.
  • Attachment: toad.png
    (Size: 52.22KB, Downloaded 352 times)

[Updated on: Wed, 28 July 2010 05:42]

Report message to a moderator

Re: Unable to connect to Oracle from Toad [message #467959 is a reply to message #467935] Wed, 28 July 2010 06:09 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ Did you try tnsping from command prompt ?
2/ Can you Connect from SQLPLus ?

if 2/ is Yes then there is no problem in Tnsnames .

[edit : typo ]

[Updated on: Wed, 28 July 2010 06:10]

Report message to a moderator

Re: Unable to connect to Oracle from Toad [message #468088 is a reply to message #467959] Wed, 28 July 2010 18:35 Go to previous messageGo to next message
dearkiran
Messages: 8
Registered: July 2010
Junior Member
Hi,

I can ping TNSPING XE from command prompt and I am able to login from sqlplus but still I am unable to connect to Oracle from Toad.

Re: Unable to connect to Oracle from Toad [message #468090 is a reply to message #468088] Wed, 28 July 2010 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

ALWAYS
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version;

post lines from near the tail end of listener.log showing errors (non-zero status code on right end of record)
Re: Unable to connect to Oracle from Toad [message #468115 is a reply to message #468090] Wed, 28 July 2010 23:33 Go to previous messageGo to next message
dearkiran
Messages: 8
Registered: July 2010
Junior Member
Hi,

My system's OS is Windows 7 and database is Oracle 10g XE.
The result of select * from v$version is below.



Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



I could not find non zero error messages in listener.log.
My listener.log looks like below.


TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production on 29-JUL-2010 12:34:31

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

System parameter file is c:\oracle\app\oracle\product\10.2.0\server\NETWORK\ADMIN\listener.ora
Log messages written to C:\Oracle\app\oracle\product\10.2.0\server\network\log\listener.log
Trace information written to C:\Oracle\app\oracle\product\10.2.0\server\network\trace\listener.trc
Trace level is currently 0

Started with pid=2816
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Anu-PC)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
29-JUL-2010 12:35:32 * service_register * xe * 0
29-JUL-2010 12:35:38 * service_update * xe * 0
29-JUL-2010 12:35:50 * service_update * xe * 0
29-JUL-2010 12:36:14 * service_update * xe * 0
29-JUL-2010 12:36:17 * service_update * xe * 0
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080))(Presentation=HTTP)(Session=RAW))
29-JUL-2010 12:36:20 * service_update * xe * 0
29-JUL-2010 12:36:23 * service_update * xe * 0
29-JUL-2010 12:36:29 * service_update * xe * 0
29-JUL-2010 12:37:20 * service_update * xe * 0
29-JUL-2010 12:46:20 * service_update * xe * 0
29-JUL-2010 12:46:56 * service_update * xe * 0
29-JUL-2010 12:57:32 * service_update * xe * 0
29-JUL-2010 13:07:32 * service_update * xe * 0
29-JUL-2010 13:17:41 * service_update * xe * 0
29-JUL-2010 13:27:41 * service_update * xe * 0
29-JUL-2010 13:33:50 * service_update * xe * 0
29-JUL-2010 13:47:53 * service_update * xe * 0
29-JUL-2010 14:01:02 * service_update * xe * 0
29-JUL-2010 14:01:08 * service_update * xe * 0
29-JUL-2010 14:01:17 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49399)) * handoff * http * 0
29-JUL-2010 14:01:20 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49403)) * handoff * http * 0
29-JUL-2010 14:01:21 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49404)) * handoff * http * 0
29-JUL-2010 14:01:22 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49405)) * handoff * http * 0
29-JUL-2010 14:01:23 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49406)) * handoff * http * 0
29-JUL-2010 14:01:47 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49459)) * handoff * http * 0
29-JUL-2010 14:06:53 * service_update * xe * 0


Please let me know if you require any further information and help me in solving the above problem.

Thanks,
Kiran.


Re: Unable to connect to Oracle from Toad [message #468117 is a reply to message #468115] Wed, 28 July 2010 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
>I am getting the error 'TNS: could not resolve the connect identifier specified (XE)' .
What error NUMBER goes with this message?
If no error in listener.log then request not getting to listener & therefore likely tnsnames.ora lookup failure & not getting off client system
Re: Unable to connect to Oracle from Toad [message #468127 is a reply to message #468117] Thu, 29 July 2010 00:37 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
If I am not Wroing it Should be ORA-12154:could not resolve the connect identifier specified .
Re: Unable to connect to Oracle from Toad [message #468137 is a reply to message #468127] Thu, 29 July 2010 01:19 Go to previous messageGo to next message
dearkiran
Messages: 8
Registered: July 2010
Junior Member
Hi,

The error is 'ORA-12154: TNS: Could not resolve the connect identifier specified (XE)'

Thanks,
Kiran
Re: Unable to connect to Oracle from Toad [message #468311 is a reply to message #468137] Thu, 29 July 2010 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
>The error is 'ORA-12154: TNS: Could not resolve the connect identifier specified (XE)'
This error occurs when alias is not found in tnsnames.ora

multiple tnsnames.ora files may exist on any system. One copy could be correct while a different copy is incorrect.
Re: Unable to connect to Oracle from Toad [message #468336 is a reply to message #468311] Thu, 29 July 2010 14:42 Go to previous messageGo to next message
Littlefoot
Messages: 19626
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But, in the very first post it is stated that there is the TNS_ADMIN variable and that it points to a correct TNSNAMES.ORA file. Or, that's how I understood it.
Re: Unable to connect to Oracle from Toad [message #468342 is a reply to message #468336] Thu, 29 July 2010 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
I guess I need remedial training on how to scroll these windows thingys
Re: Unable to connect to Oracle from Toad [message #468363 is a reply to message #468342] Thu, 29 July 2010 20:59 Go to previous messageGo to next message
dearkiran
Messages: 8
Registered: July 2010
Junior Member
I don't have multiple TNS files in my system and TNS_ADMIN has correct path of TNS file. When I tried direct option in toad, I got below error

'ORA-06413 Connection not open.'

Thanks,
Kiran.
Re: Unable to connect to Oracle from Toad [message #468364 is a reply to message #468363] Thu, 29 July 2010 21:08 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
06413, 00000, "Connection not open."
// *Cause:  Unable to establish connection.
// *Action: Use diagnostic procedures to ascertain exact problem.


Is any type of Operating System Virtualization installed on this system?
When did Toad last work correctly?
Re: Unable to connect to Oracle from Toad [message #468394 is a reply to message #468364] Thu, 29 July 2010 23:52 Go to previous messageGo to next message
dearkiran
Messages: 8
Registered: July 2010
Junior Member
Recently I installed Oracle XE and Toad.OS virtulization is not installed on it. This is the first time I installed toad and is not working in my system.

Thanks,
Kiran.

Re: Unable to connect to Oracle from Toad [message #468558 is a reply to message #468115] Fri, 30 July 2010 11:50 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
open a Command Window then COPY the lines below

SET
lsnrctl status
lsnrctl service
dir /Q C:\Oracle\app\oracle\product\10.2.0\server\dbs
type C:\Oracle\app\oracle\product\10.2.0\server\network\admin\tnsnames.ora
sqlplus scott/tiger@XE

PASTE the lines above into open command window
COPY commands & results, then PASTE all back here
Re: Unable to connect to Oracle from Toad [message #468729 is a reply to message #468558] Sun, 01 August 2010 21:57 Go to previous messageGo to next message
dearkiran
Messages: 8
Registered: July 2010
Junior Member
Hi,

Please find below the result of the above commands in command prompt.


C:\Users\ravi>SET
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\ravi\AppData\Roaming
CommonProgramFiles=C:\Program Files\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
CommonProgramW6432=C:\Program Files\Common Files
COMPUTERNAME=ANU-PC
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\ravi
LOCALAPPDATA=C:\Users\ravi\AppData\Local
LOGONSERVER=\\ANU-PC
NUMBER_OF_PROCESSORS=8
OS=Windows_NT
Path=C:\Oracle\app\oracle\product\10.2.0\server\bin;C:\Windows\system32;C:\Windo
ws;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Progr
am Files\Dell\DW WLAN Card;c:\Program Files\WIDCOMM\Bluetooth Software\;c:\Progr
am Files\WIDCOMM\Bluetooth Software\syswow64;c:\Program Files (x86)\ATI Technolo
gies\ATI.ACE\Core-Static;c:\Program Files (x86)\Common Files\Roxio Shared\DLLSha
red\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 30 Stepping 5, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=1e05
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
ProgramFiles(x86)=C:\Program Files (x86)
ProgramW6432=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\ravi\AppData\Local\Temp
TMP=C:\Users\ravi\AppData\Local\Temp
TNS_ADMIN=c:\oracle\app\oracle\product\10.2.0\server\NETWORK\ADMIN
USERDOMAIN=Anu-PC
USERNAME=ravi
USERPROFILE=C:\Users\ravi
windir=C:\Windows


C:\Users\ravi>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-AUG-2010 12:48
:01

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 - Produ
ction
Start Date                02-AUG-2010 11:35:37
Uptime                    0 days 1 hr. 12 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   c:\oracle\app\oracle\product\10.2.0\server\NETWORK\ADM
IN\listener.ora
Listener Log File         C:\Oracle\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=Anu-PC)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Anu-PC)(PORT=8080))(Presentation=HTT
P)(Session=RAW))
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...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\ravi>lsnrctl service

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-AUG-2010 12:48
:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:3 refused:0 current:3 max:1002 state:ready
         DISPATCHER <machine: ANU-PC, pid: 2632>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Anu-PC)(PORT=49163))
Service "XE_XPT" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

C:\Users\ravi>dir /Q C:\Oracle\app\oracle\product\10.2.0\server\dbs
 Volume in drive C is OS
 Volume Serial Number is 1251-1E7D

 Directory of C:\Oracle\app\oracle\product\10.2.0\server\dbs

26/07/2010  11:17 PM    <DIR>          BUILTIN\Administrators .
26/07/2010  11:17 PM    <DIR>          NT AUTHORITY\SYSTEM    ..
02/08/2010  11:37 AM             2,560 BUILTIN\Administrators SPFILEXE.ORA
               1 File(s)          2,560 bytes
               2 Dir(s)  119,766,269,952 bytes free

C:\Users\ravi>type C:\Oracle\app\oracle\product\10.2.0\server\network\admin\tnsnames.ora
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Anu-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

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

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

C:\Users\ravi>sqlplus system/tiger123@XE

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 2 12:48:01 2010

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


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>



Please let me know if you need any further information.

Thanks,
Kiran

[Updated on: Sun, 01 August 2010 22:00]

Report message to a moderator

Re: Unable to connect to Oracle from Toad [message #468731 is a reply to message #468729] Sun, 01 August 2010 22:14 Go to previous message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
>C:\Users\ravi>sqlplus system/tiger123@XE[/email]
Results show listener & DB are working as expected.
Since I don't use TOAD, you are somebody will have to figure out why it throws error.
The root cause "MUST" be outside of Oracle & local to TOAD, since all else works!

>I don't have multiple TNS files in my system and TNS_ADMIN has correct path of TNS file.

I just HAVE to ask this question.
Are you 100% drop dead certain that TOAD uses TNS_ADMIN to locate tnsnames.ora file?
You have proven that when correct tnsnames.ora is used, then the connection succeeds.
Error message says that the TNS alias was not found.
What is explanation for these irrefutable but opposing observations?

[Updated on: Sun, 01 August 2010 22:38]

Report message to a moderator

Previous Topic: Exporter Tool #429 Error Over and Over...limited documentation
Next Topic: Removing final carriage return from spooled output
Goto Forum:
  


Current Time: Sat Sep 20 01:25:53 CDT 2014

Total time taken to generate the page: 0.09327 seconds