?
Solved

trouble logging on to Sql*Plus via command and can't log on to Designer (need connect string)

Posted on 2014-08-04
11
Medium Priority
?
972 Views
Last Modified: 2014-08-04
I just successfully installed Oracle Developer but having trouble logging on to Sql Plus from command line, also can't connect via any of the Developer Tools, I keep getting TNS: Connect timeout occurred. This also happens for Sql Plus for Windows.
Oracle forms connect error
However, I can connect via Sql Plus command window when it's run from "Start --> All Programs", but not when I first run the Command window:
Sql Plus connect plus error
0
Comment
Question by:Gadsden Consulting
  • 5
  • 5
11 Comments
 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 600 total points
ID: 40240134
You should check your PATH settings, Your first sql plus is 11.2.0.1.0 where as the second one is 10.1.0.4.2. The first one has a configured tnsnames (in network\admin) and the second one does not,
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40240214
I agree with Gerwin's post.  Looks like you have multiple Oracle products installed.

I wanted to comment on the original question about timeouts.

99% of the time these are due to a firewall issue.  Since you specified a service name (database) in teh sql developer window, it is trying a network connection.  You would need to look a the tnsnames.ora file it is using to see what/where the ORCL instance is that it is looking for.
0
 

Author Comment

by:Gadsden Consulting
ID: 40240220
Ok, that makes sense, but . . . I don't quite get it, wouldn't the TNS listener be listening for anything ? I thought this was kind of a universal resource . . .
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 total points
ID: 40240224
>>wouldn't the TNS listener be listening for anything ? I thought this was kind of a universal resource . . .

It listens for whatever you tell it to or whatever has registered with it.

When you go to make a connection you it looks for the listener wherever you tell it to look.  May be you aren't looking for the listener when you think you are looking.
0
 

Author Comment

by:Gadsden Consulting
ID: 40240275
slightwv,

>>It listens for whatever you tell it to or whatever has registered with it.
-- ok, that kind of makes sense, but it doesn't compute with me . . . Mainly because I'm only going to have ONE tnsnames file on my machine, right ?

Here's my orig path (see notes on lines 5 and 8)
E:\app\DevSuiteHome_1\jdk\jre\bin\classic;
E:\app\DevSuiteHome_1\jdk\jre\bin;
E:\app\DevSuiteHome_1\jdk\jre\bin\client;
E:\app\DevSuiteHome_1\jlib;
E:\app\DevSuiteHome_1\bin;   <-- Sql plus 10.1 found here, but no tnsnames, so dead end, end of PATH search
E:\app\DevSuiteHome_1\jre\1.4.2\bin\client;
E:\app\DevSuiteHome_1\jre\1.4.2\bin;
E:\app\Stephen\product\11.2.0\dbhome_1\bin;  <-- tnsnames found here, also Sql Plus 11.2

%SystemRoot%\system32;
%SystemRoot%;
%SystemRoot%\System32\Wbem;
%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;

C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\;
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\;
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\;
C:\Program Files (x86)\Common Files\ThinPrint\;

C:\Program Files\Java\jdk1.7.0_60\bin

Open in new window

To fix my sql plus from command window issue, I moved E:\app\Stephen\product\11.2.0\dbhome_1\bin;   to the front of my PATH, and now Sql Plus always comes from there and I can connect.

But . . . I still can't connect from the Oracle Developer tools, here:
Oracle forms can't connect
Nor can I connect to the d.b. from Sql Plus for Windows, which is in E:\app\DevSuiteHome_1\BIN\sqlplusw.exe, even though the Listener is now first in PATH

How do I fix this ?
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 600 total points
ID: 40240292
>>Mainly because I'm only going to have ONE tnsnames file on my machine, right ?

You can have as many tnsnames.ora files as you want.  Same for listener.ora files if you have more than one listener from more than one ORACLE_HOME.

>>But . . . I still can't connect from the Oracle Developer tools, here:

SQL Developer sort of has it's own environment.  You should be able to set up a direct connection but I never use that tool so I cannot say how.

If sql developer is being run form the DevSuiteHome path, it still may need a tnsnames.ora file there.  Copy the one from the 11g install under:
E:\app\DevSuiteHome_1\network\admin

>> even though the Listener is now first in PATH

listener doesn't matter.  It can run from any ORACLE_HOME.  Everything just has to point to the correct place.
0
 

Author Comment

by:Gadsden Consulting
ID: 40240322
slightwv,
>>You can have as many tnsnames.ora files as you want.
-- but I only want ONE ! isn't that normal ? it would be goofy to have multiple tnsnames, imo.

-- Note that for SQL Developer the path is "E:\Program Files (x86)\sqldeveloper\sqldeveloper.exe", BUT this works fine, I connected to the d.b. without jumping through hoops.

Here's the Oracle developer tools' tnsnames in E:\app\DevSuiteHome_1\NETWORK\ADMIN
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Stephen-PC.home)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PLSExtProc)
    )
  )

Open in new window

Here's the tnsnames from the 11g d.b.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


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

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Open in new window


I'll play around with this, thanks.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40240327
>>-- but I only want ONE ! isn't that normal ? it would be goofy to have multiple tnsnames, imo.

Not abnormal at all.  You typically have one per Oracle product install.  If you ONLY want one, there is a TNS_ADMIN environment variable you need to set that will tell ALL Oracle products where to locate the tnsnames file:
http://docs.oracle.com/cd/E11882_01/win.112/e10845/ap_net.htm#NTQRF515

Personally, I never use TNS_ADMIN and have been using Oracle for over 20 years.
0
 

Author Comment

by:Gadsden Consulting
ID: 40240338
Ok, I mostly got it going.

- I copied the 11g tnsnames to Oracle Developer location, and I got Oracle Forms to connect to one of my databases, yea !

- I couldn't connect to Oracle forms as "sys as sysdba" ("invalid username / password",
- I did connect as "hr", so not sure why I couldn't connect as "sys as sysdba" (I connect from sql plus and Sql Developer)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40240341
Not an Oracle Forms guy but the space messes up a LOT of Oracle products.

I'm sure if you Google around, you'll see how to connect 'as sysdba' with Forms.

My question is: Why would you want to?

Connecting as SYS is something you typically NEVER do unless you are performing some major database actions.  I don't see Forms as needing to do this.
0
 

Author Comment

by:Gadsden Consulting
ID: 40240347
>>My question is: Why would you want to connect as "sys"
-- I'm just trying to set up my environment, so that is my test connection.

So I got my question answered, but I'm sure I'll be back, thanks !
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
Assume you have an outside contractor who comes in seasonally or once a week to do some work in your office, but you only want to give him access to the programs and files he needs and keep all other documents and programs private. Can you do this o…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question