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.
[[email protected] dbhome_1]$ lsnrctl status LSNRCTL for Linux: Version 184.108.40.206.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 220.127.116.11.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
- 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.