| Unable to connect to Oracle from Toad [message #467880] |
Wed, 28 July 2010 00:03  |
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 #467935 is a reply to message #467884] |
Wed, 28 July 2010 05:30  |
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 2176 times)
[Updated on: Wed, 28 July 2010 05:42] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Unable to connect to Oracle from Toad [message #468115 is a reply to message #468090] |
Wed, 28 July 2010 23:33 |
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 #468729 is a reply to message #468558] |
Sun, 01 August 2010 21:57 |
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 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
