Home » SQL & PL/SQL » Client Tools » Connect to Oracle DB using Oracle SQL Developer (11g Linux)
Connect to Oracle DB using Oracle SQL Developer [message #681787] Mon, 31 August 2020 10:53 Go to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
From a Windows 10 box I am using using Oracle SQL Developer, attempting to connect to an Oracle DB running Redhat.
The tnsnames.ora file contains the following (the actual has been modified):

orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11.222.3.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = hevy.rodsvr.tvr.com)
(INSTANCE_NAME = hevy)
)
)

When I attempt to connect to the Oracle DB using Oracle SQL Developer, I have the following:
Connect Type: Basic
Hostname: 11.222.3.4
Port: 1521
SID: orcl

I get the following connection error message: "Status : Failure -Test failed: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"

Any idea why it is failing?
Re: Connect to Oracle DB using Oracle SQL Developer [message #681790 is a reply to message #681787] Mon, 31 August 2020 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67560
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

On DB server execute:
lnsrctl services
Note that as you use a basic connection with hostname/port/sid you do not use tnsnames.ora.
Choose a connection of type TNS or instead of giving SID in the basic connection, give the service name that is specified in the tnsnames entry (assuming it is correct).
Have a look at https://docs.oracle.com/database/121/ADMQS/GUID-0DE0C9F4-8800-4142-A755-14B2FAA6624F.htm#ADMQS12420

Re: Connect to Oracle DB using Oracle SQL Developer [message #681791 is a reply to message #681787] Mon, 31 August 2020 12:03 Go to previous messageGo to next message
John Watson
Messages: 8428
Registered: January 2010
Location: Global Village
Senior Member
Your SID is hevy, not orcl.
Re: Connect to Oracle DB using Oracle SQL Developer [message #681793 is a reply to message #681791] Mon, 31 August 2020 13:26 Go to previous messageGo to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
Thanks for the info so far. I am using version 10.2.0.4.0

I believe progress is being made because now, when using the Basic connection type, I am getting the following error:
Status: Failure-Test failed: ORA-01017:invalid username/password; login denied

This failure occurs when I try to connect using SID [hevy] or Service Name [hevy.rodsvr.tvr.com]

The username and password I am using in the SQL Developer connection is the same as the one I use to connect to the Oracle DB using Putty (Linux) or when connecting using WinSCP. I am able to successfully connect with that username and password in Putty and WinSCP.

Any idea as to why the SQL Developer connection does not like the username/password?
Re: Connect to Oracle DB using Oracle SQL Developer [message #681794 is a reply to message #681793] Mon, 31 August 2020 14:22 Go to previous messageGo to next message
John Watson
Messages: 8428
Registered: January 2010
Location: Global Village
Senior Member
You would use PuTTY or WinSCP to logon to a machine running Unix. You use SQL Developer to logon to an Oracle database. Not the same thing at all! And probably not the same username/password, either. You had better describe what you are trying to do in greater detail.
Re: Connect to Oracle DB using Oracle SQL Developer [message #681795 is a reply to message #681793] Mon, 31 August 2020 14:58 Go to previous messageGo to next message
EdStevens
Messages: 1285
Registered: September 2013
Senior Member
As an aside, version 10.2.0.4.0 is long obsolete. And out of support unless you are paying through the nose for extended support. Waaayyy past time to upgrade.

As for "ORA-01017:invalid username/password", oracle is too dumb to lie about that, and it is very definitive. First it means you actually reached a database (as opposed to getting rejected by the listener or the OS network routing), and you very much did supply a usename/password that is invalid for the database you reached. If you had "success" usign the same credentials with something else, then that "somthing else" was NOT the database that rejected this request.

You said it worked with SSH. I suspect, as does John Watson, that your are talking about os credentials there, not database credentials. You could actually use copy and paste to show us a 'successful' connection, and we could then diagnose further.
Re: Connect to Oracle DB using Oracle SQL Developer [message #681796 is a reply to message #681795] Mon, 31 August 2020 16:44 Go to previous messageGo to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
Well, the support person who restored our database (once we got it licensed again after a year of non-use) said "this "x" is the username and this "y" is the password".
I guess, as both replies from John Watson and EdStevens imply, the username\password given me by the support person were for the OS only.

Being rather new to Oracle, if I can connect as SYSDBA in SQLPLUS, can I create a user that would have the credentials needed to connect from SQL Developer?
Re: Connect to Oracle DB using Oracle SQL Developer [message #681797 is a reply to message #681796] Tue, 01 September 2020 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67560
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.

Re: Connect to Oracle DB using Oracle SQL Developer [message #681807 is a reply to message #681797] Tue, 01 September 2020 11:26 Go to previous messageGo to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
In Redhat, I connected to the Oracle DB and connected to SQLPLus using:
$ SQLPlus / AS SYSDBA

I then ran the following statements to create a user named DBAdmin, with the following roles and permissions:
CREATE USER DBAdmin IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, DBA TO DBAdmin;
GRANT CREATE SESSION TO DBAdmin;
GRANT UNLIMITED TABLESPACE TO DBAdmin;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE to DBAdmin;

When I go to SQL Developer and try to connect using DBAdmin, I still receive the same error:
"ORA-01017:invalid username/password"

What might I be missing?

/foru/forum/fa/14377/0/

Re: Connect to Oracle DB using Oracle SQL Developer [message #681808 is a reply to message #681807] Tue, 01 September 2020 11:41 Go to previous messageGo to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
Additionally, when logged into the DB (connected to the DB in Redhat) and using SQLPlus, I can connect and use SQLPlus with user DBAdmin.
Re: Connect to Oracle DB using Oracle SQL Developer [message #681809 is a reply to message #681808] Tue, 01 September 2020 11:50 Go to previous messageGo to next message
John Watson
Messages: 8428
Registered: January 2010
Location: Global Village
Senior Member
Almost certainly case sensitive username and password hassles. Don't try to be clever with mixed case. Just make it upper case everywhere.


--update: and I wonder how you managed top make SQLPlus work.

[Updated on: Tue, 01 September 2020 11:53]

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681811 is a reply to message #681809] Tue, 01 September 2020 12:49 Go to previous messageGo to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
I dropped user DBAdmin and created user DB_ADMIN.
As before, I can connect DB_ADMIN in SQLPLUS, but cannot in SQL Developer. See screenshot of SQLPLUS and SQL Developer.

/foru/forum/fa/14378/0/
Re: Connect to Oracle DB using Oracle SQL Developer [message #681839 is a reply to message #681811] Thu, 03 September 2020 11:50 Go to previous messageGo to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
Any further ideas on how to troubleshoot this connectivity issue?
Re: Connect to Oracle DB using Oracle SQL Developer [message #681842 is a reply to message #681839] Thu, 03 September 2020 12:23 Go to previous messageGo to next message
John Watson
Messages: 8428
Registered: January 2010
Location: Global Village
Senior Member
Case sensitive passwords came in with release 11, with several changes since. It could be that your SQL Developer (which did not exist in release 10) is wanting to use password verifiers that don't work in a release 10 database. Not a problem - don't use it Smile
Re: Connect to Oracle DB using Oracle SQL Developer [message #681858 is a reply to message #681842] Fri, 04 September 2020 09:42 Go to previous messageGo to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
If, you are saying (as a theory), that an all uppercase username and all uppercase password are not able to connect to a 10g database using SQL Developer, what are some other free GUI tools besides SQL DEVELOPER that are available to connect to Oracle?
Re: Connect to Oracle DB using Oracle SQL Developer [message #681859 is a reply to message #681858] Fri, 04 September 2020 10:02 Go to previous messageGo to next message
John Watson
Messages: 8428
Registered: January 2010
Location: Global Village
Senior Member
I am suggesting that your SQL Developer may be too new to permit the old password verification protocol. There are probably ways to make it work.

Why don't you use the iSQLPlus tool that shipped with DB 10g?
Re: Connect to Oracle DB using Oracle SQL Developer [message #681870 is a reply to message #681858] Fri, 04 September 2020 15:26 Go to previous messageGo to next message
rerichards
Messages: 9
Registered: August 2020
Junior Member
I downloaded Toad and like SQL DEVELOPER, it is giving me the same message, "ORA-01017: Invalid username/password; login denied"
And, as before, I can use those same credentials (the username and password are all uppercase) to connect in SQLPLUS.
Re: Connect to Oracle DB using Oracle SQL Developer [message #681875 is a reply to message #681870] Sat, 05 September 2020 01:20 Go to previous message
John Watson
Messages: 8428
Registered: January 2010
Location: Global Village
Senior Member
I wonder why you keep trying to use current tools to access an ancient database. Would you put petrol into a steam engine?
Previous Topic: SqlDeveloper tools in a secure environment
Next Topic: Format issue with NULL values
Goto Forum:
  


Current Time: Wed Dec 02 10:47:23 CST 2020