Help! I can’t connect to my database …

(This is a revised version of an article I originally published at

Some of the most frequently asked questions on the Oracle Technology Network (OTN) forums deal with problems trying to connect to the database. Tracing the problem isn’t rocket science, but I often see people not paying attention to (or not trusting) the very specific error messages and riding off in all directions at once. So let me try to explain a little about how Oracle handles a request to “connect me to my database” and actually locates a database running on a machine on the other side of the planet (or even on the very machine from which the request originated!)

Before digging in, let’s talk about a very simple concept that an amazing number of people struggle with. For purposes of the current discussion there are two “entities”, or processes, involved. First there is the server process. Depending on one’s semantic precision and the context in which the term is used, the “server” could refer to the database server process, or the computer on which that process executes. The second entity is the client process. That is the process that is requesting the connection to (and services from) the database. Again, depending on one’s semantic precision and context, the term “client” could refer to a process or a computer on which the process executes. For our purposes both “client” and “server” mean the process. These processes could be running on any two separate computers, or (and understand this) they could be running on the very same computer. The important thing is the distinction between the two processes.

So let’s take a quick walk down the path from the client to the server. We will dig deeper in future posts. For our purposes, we will use the most common Oracle client program of all: sqlplus. At a command line you issue this statement to start it and connect to your database:

C:> sqlplus scott/tiger@larry

Of course, the first thing that will happen really has nothing to do with Oracle. First, the OS must locate an executable called ‘sqlplus’, load it, and pass it the rest of the command line (scott/tiger@larry) do with as it sees fit. And what sqlplus sees fit is to pass a request to Oralce’s network layer (TNS, Transparent Network Substrate) to make a connection to “larry”, using the userid “scott” and the password “tiger” as its authentication credentials. So TNS has to figure out what is meant by “larry”. By default it will do this by looking in a file called tnsnames.ora. Since we are still at the client making the request, this file must be found on the client machine. By default it will be found in $ORACLE_HOME/network/admin.

Let’s make it easy and suppose our tnsnames file has this entry:

larry =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(SERVICE_NAME = curley)

TNS will look in tnsnames.ora for an entry called ‘larry’. Finding it, a request is sent through the normal OS network stack to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley). Notice where it got this information from the entry in the tnsnames file. (This entry is known as the “connect identifier”.) Also notice that what is going on here is the resolution of an alias (“larry”) to an actual destination. In this respect the tnsnames.ora file serves the same purpose for sqlnet as the OS’s “hosts” file serves for the standard network stack. Or for a less technical analogy, it serves the same purpose as your telephone directory, where the the name of “larry” would be associated with the routing information (telephone number) needed by the telephone network.

All network routing is done by IP address, but all we have provided here is a host name of “myhost” Where is “myhost”) on the network? When the request gets passed from TNS to the standard network stack, the name ‘myhost’ will get resolved to an IP address, either via a local ‘hosts’ file or a DNS server. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora but for ease of maintenance this is not recommended.

Once the ip address is determined, the standard networking process delivers the message to the designated port (PORT = 1521) at the specified ip address. Hopefully, there is an Oracle database listener process at that address and configured to listen on the specified port, and that listener knows about SERVICE_NAME=curley. If so, the listener will spawn a server process to act as the intermediary between your client and the database instance. Communication to that server process will be on a different port, selected by the listener and communicated back to the client. At that point the listener is out of the process and continues to await other connection requests coming in on its configured port.

What can go wrong?

First, there may not be an entry for ‘larry’ in your tnsnames. In that case you get “ORA-12154: TNS:could not resolve the connect identifier specified”. I’ll expand on the various reasons “larry” may not have been found at a later date, but make no mistake, if you receive a ORA-12154, it is an absolute certainty your request never got past this point. You are wasting your time trying to solve this by looking at your listener. If you can’t place a telephone call because you don’t know the number (can’t find your telephone directory – aka “tnsnames.ora” – or can’t find the party you are looking for listed in it – no entry for larry) you don’t look for problems at the telephone switchboard.

Maybe the entry for “larry” was found, but “myhost” couldn’t be resolved to an IP address (neither the local hosts file nor the DNS server had an entry for “myhost”). This will result in “ORA-12545: Connect failed because target host or object does not exist”.

Maybe there was an entry for “myserver” in the local hosts file or the DNS server, but it specified an IP address that does not exist on the network or is otherwise unreachable. This will result in “ORA-12170: TNS:Connect timeout occurred”.

Maybe the IP that is reachable on the network, but there is no listener running on that machine. “ORA-12541: TNS:no listener”

Maybe the IP was good, there is a listener at that address, but it is listening on a different port. Again, “ORA-12541: TNS:no listener”

Maybe the IP was good, there is a listener at myhost, it is listening on the specified port, but doesn’t know about SERVICE_NAME=curley. “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”

Ok, that is how we get *from* the client connection request *to* the listener. What about the listener’s part of all this?

The listener is very simple. It runs on the server machine and it’s job is to listen for connection requests and make the connection (server process) between the client and the database instance. Once that connection is made, the listener is out of the picture. If you were to kill the listener, all existing connections would continue.

The listener is configured with the listener.ora file, but if that file doesn’t exist, the listener is quite capable of starting up with all default values. One common mistake with the listener configuration is to specify “HOST=localhost” or “HOST=127.0.01”. This is a very special ip address, known as the “local loopback” address. LOCALHOST and ip address always mean “this machine on which I am sitting”. So, *all* computers are known as “localhost” or “”. If you specify this address in your listener configuration, the listener will only be capable of receiving requests from the machine on which it is running. If you specified that address in your tnsnames file, the request would be routed to the machine on which the requesting client resides. Probably not what you want.

From here I have a few ideas for future posts, each focusing on potential complications at each step of the process. Another post will use actual examples to deconstruct (break) a working TNS connection request, to demonstrate and prove the various factors.

“Film at eleven”.

32 thoughts on “Help! I can’t connect to my database …

  1. Thanks for this basic concept, most new dba, including me when I started, had problem understanding this concept and yes you presisely said it as it shoudl be.

    Again, thank you.


  2. I am getting a ora 12514 i have 3 tnsname servers defined in the tnsnames.ora file and i am unable to connect can you please guide me on how to solve the problem here is the link on oracle forum
    you can reply on your blog or on the forum i will be thankful

    • OK, I’ve started participating in your thread on OTN. Please let me point out that you made two posts here, just one minute apart. While I want to help people, you must understand that a blog is not a chat room. I only check in here once a day, if that. Your question is much better addressed on an OTN forum, where you were already getting what appears to be some very good assistance.

  3. Hi guys
    I have a serious problem im using Oracle9i on Windows xp Sp3 (working on the local computer ) when i try to connect to my local database it shows me ( tnslsnr.exe has encountered a problem and it will close ) no listener any ideas please would be a pleasure

    • I read through it, and don’t really see anything I can add. I do have some concerns about all those ORACLE_HOMEs on a Windows box, especially since on of them is for XE, which pretty much assumes it is the only oracle product installed.

  4. Why this “ORA-12170 TNS Connet time out occured” error raised?
    I am using oracle database 10g on windows xp.

    • I have no way of knowing why any particular ORA-12170 occurred on someone else’s system. Have you tried to google “ORA-12170”? There only about a bazillion hits for that one. Just off the top of my head I’d say you probably have a firewall issue.

  5. Thanks man…. Was stuck on a very tiny issue that had to do with the difference between the service vs SID and your article gave me such an idea to try….. 2 weeks of searching around for answers…. one article and bingo… 🙂 Cheers

  6. Hi

    Just to check in your example above, is “myserver” a typo? Should it be “myhost” instead? Great article by the way.

    • It was a generic example. I could just as easily have written ‘Maybe there was an entry for “fred” in the local hosts ‘, or ‘Maybe there was an entry for “vblnxsrv01” in the local hosts ‘

  7. You need to add another entry where there isn’t a need for tnsname.ora on the client side. There is something called easyconnect!

    • I am aware of easyconnect, but never thought to write about it because it rarely comes up when people are trying to debug connection failures. I’ll see if I can’t put something together on it this weekend. Thanks for the reminder.

  8. Excellent article that explains this process clearly and efficiently! Wish Oracle did it this well. Thank you. I am now a loyal fan of this site.

  9. I just got a 12560 error on a windows vm while trying to connect as sysdba from the windows cmd. The xe had never finished shutting down. The apparent cause was a 50M listener log file. Shutting down that, renaming log file, starting listener, starting db service and protocol errors mysteriously vanished.

  10. Hi Ed,
    You are a great teacher. I’m trying to learn Oracle on my own. I have installed 12c. This article clarifies all the basic things I want to know about Oracle connection. Thanks for the blog.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s