OraFAQ Forum: Client Tools » Connect to Oracle DB using Oracle SQL Developer

Home » Other » Client Tools » Connect to Oracle DB using Oracle SQL Developer (11g Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Connect to Oracle DB using Oracle SQL Developer [message #681787]

Mon, 31 August 2020 10:53

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?

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681790 is a reply to message #681787]

Mon, 31 August 2020 12:02

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681791 is a reply to message #681787]

Mon, 31 August 2020 12:03

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Your SID is hevy, not orcl.

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681793 is a reply to message #681791]

Mon, 31 August 2020 13:26

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?

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681794 is a reply to message #681793]

Mon, 31 August 2020 14:22

John Watson
Messages: 8962
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.

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681795 is a reply to message #681793]

Mon, 31 August 2020 14:58

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681796 is a reply to message #681795]

Mon, 31 August 2020 16:44

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?

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681797 is a reply to message #681796]

Tue, 01 September 2020 00:34

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes.

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681807 is a reply to message #681797]

Tue, 01 September 2020 11:26

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/

/foru/forum/fa/14377/0/

Attachment: SQLDev_Screenshot.png
(Size: 18.16KB, Downloaded 2717 times)

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681808 is a reply to message #681807]

Tue, 01 September 2020 11:41

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.

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681809 is a reply to message #681808]

Tue, 01 September 2020 11:50

John Watson
Messages: 8962
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

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/

/foru/forum/fa/14378/0/

Attachment: SQLDev_Screenshot.png
(Size: 30.53KB, Downloaded 2621 times)

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681839 is a reply to message #681811]

Thu, 03 September 2020 11:50

rerichards
Messages: 9
Registered: August 2020

Junior Member

Any further ideas on how to troubleshoot this connectivity issue?

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681842 is a reply to message #681839]

Thu, 03 September 2020 12:23

John Watson
Messages: 8962
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

Smile

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681858 is a reply to message #681842]

Fri, 04 September 2020 09:42

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?

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681859 is a reply to message #681858]

Fri, 04 September 2020 10:02

John Watson
Messages: 8962
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?

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681870 is a reply to message #681858]

Fri, 04 September 2020 15:26

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.

Report message to a moderator

Re: Connect to Oracle DB using Oracle SQL Developer [message #681875 is a reply to message #681870]

Sat, 05 September 2020 01:20

John Watson
Messages: 8962
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?

Report message to a moderator

Previous Topic:	SqlDeveloper tools in a secure environment
Next Topic:	Format issue with NULL values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 30 18:22:27 CST 2024