30 SQL Developer Tips in 30 Days, Day 5: Connectivity Issues
Post Updated on 9 October, 2020.
The most frequently posted question when it comes to Oracle Database, HAS to be around connectivity.
If you’re just starting out working with the database, not being able to connect to said database is a very frustrating experience.
Oracle error messages can be confusing for those without Oracle experience..and this can make getting started even harder.
And not everyone has a DBA they can drag back to their desktop to help them get started.
Before we get started, let’s talk about the #1 problem I see –
You Think You Have a Database, but No.
Oracle SQL Developer is just a client. It doesn’t include a database. You need to have a database. I see people download the tool, go with the default connection properties, add ‘HR’ and ‘oracle’ for username and password, and get stuck.
If you are SURE you have an actual database, it’s now safe to continue.
Now let’s go over a few common connection errors and talk about what they mean and how to ‘fix’ them!
ORA-12545: Connect failed because target host or object does not exist
So the database you want to connect to is up and running on a machine somewhere. This machine has a network address and name. You supply this network information either explicitly via the basic connect or implicitly via the TNSNames entry.
The first thing to do is make sure you have the IP address or network name correct. If that’s good, then see if you can PING the server. You can run PING from your CMD or Terminal program. If that doesn’t work, then the machine is either down or not in your network, or…
ORA-12541: TNS:no listener
We’re making progress. We can get to the machine now. But, there’s no service there to accept our connection request. Almost all database connections are serviced by the Listener.
The two most common issues here are:
- the Listener isn’t up and running
- you’re going to the wrong port
The Listener is configured to ‘listen’ for connection requests on one or more ports. The default port is 1521. But many times it’s not 1521. Sometimes it’s 1522. So make sure you have the right port here:
If this is YOUR personal database, you will want to check to make sure the Listener is up and running. The quickest way to do that is go to cmd or shell prompt and run > lsnrctl status
That will tell if you the listener is up, and if so, what port it’s listening on.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
So you found the server, and you were able to talk to the listener. But when you asked to talk to your database, the listener is telling you that no such database ‘service’ is available.
So either the SID or SERVICE you’re providing here is wrong, or the Listener isn’t servicing that database yet.
SID is the unique identifier for the database. The Service describes what the Listener is using to support connections to that database. You can use either. If you have access to the listener directly, you can run the > lsnrctl status command again to see what’s being serviced.
[oracle@localhost dbhome_1]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 05-SEP-2014 09:40:01 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production Start Date 29-AUG-2014 05:45:56 Uptime 7 days 3 hr. 54 min. 5 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/12.1.0/dbhome_1/log/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "pdb1" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully
The “pdb1” is what I’m looking for here, and what I need to put into the connection details.
Now, if you’re going to connect to a 12c or higher (12, 18, 19, 20…) Multitenant pluggable database (PDB), you HAVE to reference it via the Service. The SID context in a multitentant database will refer to the container database (CDB.) You almost always want to connect to the PDB – that’s where your tables and data will be.
ORA-01017: invalid username/password; logon denied
This error message is usually VERY easy to understand. You are presenting the user and their password to the Database, and the Database is saying, sorry, that combination is not correct.
And yet, I still see lots of confusion. So let’s take a look at what could be happening.
- Obviously make sure your username and password are CORRECT
- Make sure you’re connecting to the RIGHT database – I see this ALL the time, where someone has the right credentials, but they’re ‘knocking at the wrong door’. This is most common where you’re using TNS for connection type, and the wrong tnsnames file is being used. The ‘show tns’ command can help here!
- Make sure your password is or isn’t case-sensitive – assume it IS
- If you’re connecting as SYS, you must use the ‘AS SYSDBA’ option
There’s a dropdown next to the USERNAME form field. - If you’re not on the server and you’re trying to connect as SYS, you need to make sure the DB is setup to allow for that
Also, 95x out of a 100, don’t need to connect as SYS. But again, that’s a different story.
Remember the discussion we just had about SID vs Service above? No?
Â
Â
Especially on 12c and higher versions of the database, you NEED to use the Service Name option. Why? Well, if you use the SID, it’s possible that you will be attempting a connection to the Container Database vs your pluggable database.
And THAT will cause a ORA-01017 as your schema and its user do not exist in the Container Database (CDB).
And also, just get into the habit of using Service Names for your connections, it’s an Oracle best practice.
27 Comments
Thank you it helped me my port was different
I am a student working and I’ve never really used any thing like oracle. When you say use command where am i putting these commands into?
In this post I talk about running the command-line interfaced program, LSNRCTL. You open a CMD or BASH shell, and run the LSNRCTL command. That will give you a prompt where you can issue commands to start or restart the listener.
Before you worry all about that, do you have a database? Have you read this?
i have installed SQL developer, and only one day it worked for me and then its giving me listener problem i googled it and changed my tnsnames and listener file.Now its giving me another error saying that “Network adapter could not established the connection Vendor code 17002”.
same error. after using listen command i am having these error details. what does it mean???
C:\>LSNRCTL
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 – Production on 30-MAR-2015 23:31
:18
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Welcome to LSNRCTL, type “help” for information.
LSNRCTL>
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1523)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 61: Unknown error
LSNRCTL>
It means your Listener isn’t configured correctly. Probably need to check your listener.ora file and fix as necessary. Or if your database is on your desktop/laptop where SQL Developer is running, try the Bequeath connection option, which bypasses the listener.
Try start command : LSNTRCTL>start
this starts the Listner
Hi Jeff. Thanks for the suggestions. The firewalls disabled was just to make see if the firewall was causing the problem. I can’t connect with it disabled so I’m ruling it out for now.
I’m on a Mac. I have to admit I’m not sure what you mean by “get to port 1521 from my machine”. I use AirPort Extreme and have it set to DHCP and NAT and have never had any port connection issues. In AWS console it tells me that port 1521 is the port to use so I put that in Oracle SQL Developer but no joy. Is there another way to get to the port?
Sorry if that’s a noob question – I use SQL in my day job extensively but this is the first attempt at connecting to my own server.
Any help is greatly appreciated!
I’m trying to connect to an amazon AWS RDS Oracle database using Oracle SQL Developer. I have the correct username/password, connection type is “Basic”, role is “default”, Hostname is copy/pasted from AWS, port is “1521”, the SID is the name of my db – “oradb”. I get the same error may above have said “IO Error: The Network Adapter could not establish the connection.” I’m relatively new to this but I have already done most of the things recommended (or suggested as an option) in forums on the internet. At this point I have my firewalls all disabled and have tried various other ports. I’m not sure what to try next.
You can ping your supplied hostname, and you can get to port 1521 from your machine? One of those must not be available.
>>At this point I have my firewalls all disabled
Yeah, I’d probably not do that.
I’m working on migrating an application from windows 2003 to windows 2012 and getting no listener error on application.
The same application with same setting and configuration is working on Win2003. I also checked with DBA to get the TNS entry verified and all look good there.
This application is trying to fetch some reporting data from other app DB.
TNSPING is working fine
Telnet is working fine on the port which is in TNS entry.
can’t check from SQLPlus as I don’t have password for the user which is being used here as it’s in encypted form. I don’t think it’s a password issue either as the same is working on Win2003.
This is the error.
at System.ComponentModel.AsyncCompletedEventArgs.RaiseExceptionIfNecessary()
at Mercer.IPS.Support.Tools.Clients.Silverlight.ToolsServiceReference.GetePrismReportCompletedEventArgs.get_Result()
at Mercer.IPS.Support.Tools.Clients.Silverlight.ViewModels.Tasks.ePrism.ePrismReportViewModel.GetePrismReportCompleted(Object sender, GetePrismReportCompletedEventArgs e)
at Mercer.IPS.Support.Tools.Clients.Silverlight.ToolsServiceReference.ToolsServiceClient.OnGetePrismReportCompleted(Object state)
Caused by: Could not connect to database – Either connection string is incorrect or server is not responding. ORA-12541: TNS:no listener
Please help if you find any solution or any other to I can verify the connection.
I don’t have access to oracle DB. I can access only web server.
I have a hard time helping everyone with their oracle connectivity issues for SQL Dev and Oracle, I’m not sure what I can do for you.
>>ORA-12541: TNS:no listener
Are you sure the server is using the RIGHT TNSNames files? Is it using the same $ORACLE_HOME as TNSPing?
It’s not a password issue. Your application is talking to a server and the server is saying there’s no one here listening on this port for database connections.
sir i cant connect to sql developer as its giving an I/O error .i am currently working in ubuntu.
Need more info nishant. Where is your database in relation to your ubuntu install of SQLDev? Can you ping the database server? Can you tnsping the database listener?
select sys_context(‘userenv’,’db_name’) from dual;
ai get orcl
So my SID is orcl
Thank you for the quick reply and iam grateful for this help. Most blogs are so lame ans asumed that yu know everything theby jumping step. Bur i appreciate this very much it has been over 3 weeks now cannot do anything.
Anyway here is how i connect to sqlplus
sql> connect scott
password: tiger
Connected to
Oracle Database 11g Enterprise Edition
I am using VMware platform
i do not know how to get the orcl but i know iam not using database express edition but enterprise.
Once again thank you for the quick help
well here is what my problem is,
Have log into the database from sqlplus but could not connect to sql developer using hr username and hr password,
role- default
connection type- Basic
Hostname- localhost
Port- 1521
SID- orcl
Then here is the failure notice;
“Status failure: Test Failed: The network Adapter could not establish the connection”
Then i do my lsnrctl command and get this.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=starlink-66)(PORT=1522))
)
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 – Produ
ction
Start Date 04-FEB-2015 17:22:43
Uptime 0 days 23 hr. 6 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\OBIEE\oracle_common\network\admin\listener.ora
Listener Log File c:\app\administrator\diag\tnslsnr\starlink-66\listener
\alert\log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=starlink-66)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary…
Service “CLRExtProc” has 1 instance(s).
Instance “CLRExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL>
your listener is listening on port 1522, not 1521. and unless your machine is ‘starlink-66’, localhost isn’t going to work either
i don’t see a service there for ‘orcl’
how did you connect via sqlplus?
I have the same problem but your answers are not useful .
Please can yu work it step by step shy tiis is not working?
Your question isn’t useful either. Tell me what you’ve tried, what’s not working, what you’re doing exactly.
Hi 🙂 Both SQL Developer and XE database are installed in my c drive windows 7. Please advise what should I do to use XE. Thanks 🙂
what does your connection string/properties look like?
Hi, I’m trying to connect with Oracle Database Express using Oracle SQL Developer. Please help.
My error is: Status: Failure – Test failed: IO Error: The Network Adapter could not establish the connection.
I could connect using cmd
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 12 15:33:38 2015
Copyright 1982, 2014, Oracle. All rights reserved.
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL>
Here is my lsnrctl status
C:\>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 – Production on 12-JAN-2015 16:00
:30
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 – Production
Start Date 12-JAN-2015 14:31:34
Uptime 0 days 1 hr. 28 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File C:\oraclexe\app\oracle\diag\tnslsnr\oit07\listener\alert\log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fe80::a092:afb2:99ae:8d01%13)(PORT=1521)))
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…
The command completed successfully
It says it can’t reach the machine via the network. I notice you’re using an IPv6 address in your listener, which I think is OK, but that might be a problem.
Where do you have SQL Developer installed and where do you have your XE database installed?
I am experiencing the same problem.I have installed the 11g express and sqldeveloper on windows 10 enterprise (same machine). It is possible to connect from sql plus .
lsnrctl status command out put is similar to above. tnsping out put is similar to below C:\Users\Win 10>tnsping 127.0.0.1
TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 – Production on 18-MAR-2016 11:01:28
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora
Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
TNS-12547: TNS:lost contact
Could you please advice on this matter.
Succeeded to connect via SQL Developer to MS SQL Server. But not to Informix. Oracle.com says that SQL support for Informix IDS is scheduled for future release. When? Or is there a way already – if so, what version of ifxjdbc.jar?
Sorry, no way today. Adding support for Informix would only be there to help with Informix migrations to Oracle and we’re not seeing a lot of activity around that right now.