Home » SQL & PL/SQL » Client Tools » Problem with connection to Oracle Server Enterprise 11g
Problem with connection to Oracle Server Enterprise 11g [message #633787] Tue, 24 February 2015 17:20 Go to next message
dancko
Messages: 86
Registered: June 2013
Location: italy
Member
Hi to all

I have a problem to connect to oracle server enterprise edition 11g 64bit.

On my pc I have installed oracle Oracle Client 10g 32bit.

I want connect to oracle server enterprise edition 11g on remote machine.


  1. With SQL Developer I connect to oracle server enterprise edition 11g without any problem.
  2. With SQL Plus I do not connect to oracle server enterprise edition 11g and i get this error message: ora-12170 tns connect timeout occurred
  3. With TOAD for Oracle I do not connect to oracle server enterprise edition 11g and i get this error message: ora-01017 invalid username/password logon denied


Can any one tell me why I get the errors 2) and 3) and how to resolve them?

Regards.

[Updated on: Tue, 24 February 2015 17:23]

Report message to a moderator

Re: Problem with connection to Oracle Server Enterprise 11g [message #633788 is a reply to message #633787] Tue, 24 February 2015 17:41 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
Oracle listener logs every connection request it gets.

post (COPY & PASTE) excerpts from listener.log file that shows at least one connection request from each of the 3 clients listed above.
This should contain clues regarding problems being reported.
Re: Problem with connection to Oracle Server Enterprise 11g [message #633789 is a reply to message #633788] Tue, 24 February 2015 17:47 Go to previous messageGo to next message
dancko
Messages: 86
Registered: June 2013
Location: italy
Member
hi BlackSwan

Is the file listener.log located in directory on oracle server machine?

If yes, I can not access to oracle server remote machine.

[Updated on: Tue, 24 February 2015 17:48]

Report message to a moderator

Re: Problem with connection to Oracle Server Enterprise 11g [message #633790 is a reply to message #633789] Tue, 24 February 2015 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
dancko wrote on Tue, 24 February 2015 15:47
hi BlackSwan

Is the file listener.log located in directory on oracle server machine?

If yes, I can not access to oracle server remote machine.


So you have a mystery & we have no clues.
Good Luck & Best Wishes using only SQL Developer
Re: Problem with connection to Oracle Server Enterprise 11g [message #633816 is a reply to message #633787] Wed, 25 February 2015 06:38 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
1. What are your connection properties for your working SQL Developer install? Right-click on the name of the connection then select "Properties".

2. Are you using SQL*Plus for Windows or DOS? If the former, what are your options under HKLM\software\oracle\* set to? If the latter, post the results of running "SET" in a command prompt.

Also, what does your %ORACLE_HOME%\network\admin\tnsnames.ora look like?

3. As per point 1, what are the respective connection properties set to in Toad?

HTH
-g
Re: Problem with connection to Oracle Server Enterprise 11g [message #633818 is a reply to message #633816] Wed, 25 February 2015 06:57 Go to previous messageGo to next message
EdStevens
Messages: 887
Registered: September 2013
Senior Member
gazzag wrote on Wed, 25 February 2015 06:38
1.

2. Are you using SQL*Plus for Windows or DOS? If the former, what are your options under HKLM\software\oracle\* set to? If the latter, post the results of running "SET" in a command prompt.

HTH
-g


Please explain the difference between "SQL*Plus for Windows" and "SQL*Plus for DOS".
Re: Problem with connection to Oracle Server Enterprise 11g [message #633819 is a reply to message #633787] Wed, 25 February 2015 07:02 Go to previous messageGo to next message
EdStevens
Messages: 887
Registered: September 2013
Senior Member
dancko wrote on Tue, 24 February 2015 17:20
Hi to all

I have a problem to connect to oracle server enterprise edition 11g 64bit.

On my pc I have installed oracle Oracle Client 10g 32bit.

I want connect to oracle server enterprise edition 11g on remote machine.



  1. With SQL Developer I connect to oracle server enterprise edition 11g without any problem.
  2. With SQL Plus I do not connect to oracle server enterprise edition 11g and i get this error message: ora-12170 tns connect timeout occurred
  3. With TOAD for Oracle I do not connect to oracle server enterprise edition 11g and i get this error message: ora-01017 invalid username/password logon denied



Can any one tell me why I get the errors 2) and 3) and how to resolve them?

Regards.


YOu are obviously using different connection properties for each.

Your failed sqlplus connection most likely means you were pointing to a non-existent host, or one blocked by a firewall.
Your failed attempt with toad is 100% proof that you reached a database but were useing an invalid username/password. Oracle is too dumb to lie about that.

You obviously need a better understanding of the process of making a connection.

see: (Help! I can't connect to my database )
see: ( Help! I can't connect to my database (part duex) )
see: (ora-12154 TNS:could not resolve the connect identifier specified)
Re: Problem with connection to Oracle Server Enterprise 11g [message #633820 is a reply to message #633818] Wed, 25 February 2015 07:04 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
sqlplusw.exe and sqlplus.exe

The former uses the registry; the latter environment variables. The former is since deprecated but I don't want to take any chances with the OP.
Re: Problem with connection to Oracle Server Enterprise 11g [message #633824 is a reply to message #633787] Wed, 25 February 2015 08:00 Go to previous messageGo to next message
EdStevens
Messages: 887
Registered: September 2013
Senior Member
https://community.oracle.com/thread/3678345
Re: Problem with connection to Oracle Server Enterprise 11g [message #633825 is a reply to message #633820] Wed, 25 February 2015 08:07 Go to previous messageGo to next message
EdStevens
Messages: 887
Registered: September 2013
Senior Member
gazzag wrote on Wed, 25 February 2015 07:04
sqlplusw.exe and sqlplus.exe

The former uses the registry; the latter environment variables. The former is since deprecated but I don't want to take any chances with the OP.



So you think sqlplus.exe uses environment variables instead of the registry?

Where are the ORACLE_HOME, etc, used by the sqlplus.exe in the following?

C:\>set
ALLUSERSPROFILE=C:\ProgramData
AMDAPPSDKROOT=c:\Program Files (x86)\AMD APP\
APPDATA=C:\Users\estevens\AppData\Roaming
CommonProgramFiles=C:\Program Files\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
CommonProgramW6432=C:\Program Files\Common Files
COMPUTERNAME=ESTEVENS
ComSpec=C:\Windows\system32\cmd.exe
DellClientSystemUpdatePath=C:\Program Files (x86)\Dell\ClientSystemUpdate\
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\estevens
LOCALAPPDATA=C:\Users\estevens\AppData\Local
LOGONSERVER=\\ITDNS1
MpConfig_ProductAppDataPath=C:\ProgramData\Microsoft\Windows Defender
MpConfig_ProductCodeName=AntiSpyware
MpConfig_ProductPath=C:\Program Files\Windows Defender
MpConfig_ProductUserAppDataPath=C:\Users\estevens\AppData\Local\Microsoft\Window
s Defender
MpConfig_ReportingGUID=76ECE600-5C4D-48C1-9A8D-AC95EF570DF4
NUMBER_OF_PROCESSORS=8
OS=Windows_NT
Path=C:\app\client\oracle\product\12.1.0\client_32\bin;C:\app\client\oracle\prod
uct\12.1.0\client_64\bin;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Fi
les\Intel\iCLS Client\;c:\Program Files (x86)\AMD APP\bin\x86_64;c:\Program File
s (x86)\AMD APP\bin\x86;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;
C:\Windows\System32\WindowsPowerShell\v1.0\;c:\Program Files (x86)\ATI Technolog
ies\ATI.ACE\Core-Static;C:\Program Files\Intel\Intel(R) Management Engine Compon
ents\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Pro
gram Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program File
s (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\M
icrosoft SQL Server\110\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Micr
osoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools
\Binn\;C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAs
semblies\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\;C:\Program F
iles\Microsoft SQL Server\110\DTS\Binn\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 60 Stepping 3, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=3c03
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\;C:\Program File
s (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\
PUBLIC=C:\Users\Public
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\estevens\AppData\Local\Temp
TMP=C:\Users\estevens\AppData\Local\Temp
USERDNSDOMAIN=<redacted>
USERDOMAIN=<redacted>
USERNAME=estevens
USERPROFILE=C:\Users\estevens
VBOX_MSI_INSTALL_PATH=C:\Program Files\Oracle\VirtualBox\
windir=C:\Windows
windows_tracing_flags=3
windows_tracing_logfile=C:\BVTBin\Tests\installpackage\csilogfile.log

C:\>sqlplus.exe estevens@xxxxx

SQL*Plus: Release 12.1.0.1.0 Production on Wed Feb 25 08:01:52 2015

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

Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL>


Besides, I wouldn't refer to them as "windows" vs "dos". They simply are what they are -- sqlplusw.exe and sqlplus.exe.
Re: Problem with connection to Oracle Server Enterprise 11g [message #633826 is a reply to message #633825] Wed, 25 February 2015 08:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
@gazzag,

Well, why to think about DOS as a concern at all? Are we(Oracle) really concerned about it?

Nice discussion though. However, when was the last time Oracle had to support DOS? Currently, I don't see anyway Oracle would even consider it as a question (what so ever) when dealing with DOS. So, why is this concern about DOS being addressed at all?

[Updated on: Wed, 25 February 2015 08:58]

Report message to a moderator

Re: Problem with connection to Oracle Server Enterprise 11g [message #633847 is a reply to message #633825] Thu, 26 February 2015 03:33 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
It's a bit of both, Ed:

C:\>echo %ORACLE_SID%
%ORACLE_SID%

C:\>sqlplus gazzag

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 26 09:20:31 2015

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

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
XE

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

C:\>set ORACLE_SID=FOO

C:\>sqlplus gazzag

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 26 09:20:59 2015

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

Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:


Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\>echo %ORACLE_HOME%
%ORACLE_HOME%

C:\>sqlplus gazzag

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 26 09:27:14 2015

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

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
XE

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

C:\>set ORACLE_HOME=C:\temp

C:\>echo %ORACLE_HOME%
C:\temp

C:\>sqlplus gazzag
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

It would appear the environment variables overrule the registry. And with legacy systems, one can;t be too careful Smile

(Windows 8.1 - Oracle Database 11g Express Edition Release 11.2)
Re: Problem with connection to Oracle Server Enterprise 11g [message #633848 is a reply to message #633826] Thu, 26 February 2015 03:40 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
@lalit

Oracle used to ship two versions of SQL*Plus for Windows. One Windows client (sqlplusw.exe) - which was awful IMHO - and one command line version (sqlplus.exe), which is probably a more accurate description than "DOS". This is the current supported version so yes, it is relevant.

[Edit: addendum]

[Updated on: Thu, 26 February 2015 03:55]

Report message to a moderator

Re: Problem with connection to Oracle Server Enterprise 11g [message #633859 is a reply to message #633847] Thu, 26 February 2015 07:09 Go to previous messageGo to next message
EdStevens
Messages: 887
Registered: September 2013
Senior Member
gazzag wrote on Thu, 26 February 2015 03:33

It would appear the environment variables overrule the registry. And with legacy systems, one can;t be too careful Smile



Exactly. And while I don't have a sqlplusW.exe to test with, I believe the same held true there as well.
I think when you really get down to it, the only difference between the two versions of sqlplus is the interface itself - one quasi-gui and the other pure command line. And to come back to where we started, I really don't think the flavor of sqlplus has any bearing on the OP's problem. At least not yet, as there are several other far more likely scenarios. But I see the OP has not provided any additional information outside of his opening post.

And as a side, the registry is one of the core reasons I detest Windows. It seems you can never ever be sure of exactly what settings any particular process may be using and where they are coming from. And this from a guy who has been maintaining Oracle databases on Windows longer than he has on *nix.
Re: Problem with connection to Oracle Server Enterprise 11g [message #633860 is a reply to message #633859] Thu, 26 February 2015 07:12 Go to previous message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:

And as a side, the registry is one of the core reasons I detest Windows.

Word Cool
Previous Topic: SP2-0642: SQL*Plus internal error state 2501, context 3:0:0
Next Topic: TOAD
Goto Forum:
  


Current Time: Wed Jan 17 15:08:56 CST 2018

Total time taken to generate the page: 0.02943 seconds