Locating Oracle’s Network Configuration Files

(This is a revised version of an earlier article. At that time I was using Oracle 10g client on Windows XP. While we have since moved on to newer versions of Oracle and Windows, the demonstrations using 10g are equally applicable to 11g and 12c installations.)

Basic Client TNS (sqlnet) configuration

Continuing the discussion of “Why can’t I connect to my database?”, I want to dig deeper into each of the potential errors in order of their possible occurrence. That will be a rather lengthy journey, so in this article I will focus on locating the configuration files on the client side of the connection. Since the vast majority of connection problems are presented by people using Windows as the client, all examples here will be from a Windows perspective. What a shame! Unix is actually much simpler.

Before discussing the actual configuration files involved, I’d like to take a short side road for background. In my previous article (“Help, I Can’t Connect to My Database”) I tried to drive home the distinction between client and server processes. Here, I want you to clearly understand that the networking component of Oracle is separate and distinct from both the database itself and the client. The Oracle Transparent Network Substrate (TNS) is a group of executables that occupy the session layer of the standard OSI network model. As such, all network communication between a client and an Oracle database ultimately rely on the standard network stack.

In this article I want to discuss the key configuration files used by TNS on the client, and how to locate them – or more accurately, how TNS locates them! There are two of these files, named “sqlnet.ora” and “tnsnames.ora”. Technically, sqlnet.ora is not required, because every essential parameter it can contain has a default value. So sqlnet.ora may not even exist on your system, but I consider it best practice to create it with key information – even if it is default values – just to remove any ambiguity when troubleshooting a problem. Full documentation of every parameter in sqlnet.ora can be found in the Database Net Services Reference. While there are no significant version dependencies, the 11g version is here, and the 12c version is here. One would also be well advised to spend some time in the Net Services Administrator’s Guide (11g) or Net Services Adminstrator’s Guide (12c), paying attention to how the sqlnet architecture relates to the standard network OSI stack.

Of course, the TNS software needs to know how to locate these files, and has two methods of doing so. The first is to look in the default location, which is %ORACLE_HOME%\network\admin. So if you have

ORACLE_HOME=C:\oracle\product\10.2.0\client_1

your sqlnet config files will be located in C:\app\oracle\product\11.2.0\client_1\network\admin. If you have performed multiple installations of Oracle, you have probably created multiple ORACLE_HOME directories and so would have multiple copies of the sqlnet config files – one for each ORACLE_HOME.

This default location of ORACLE_HOME\network\admin can be overridden by use of the environment variable TNS_ADMIN. For example, if you have

TNS_ADMIN=c:\oranet

TNS will try to find the configuration files in c:\oranet. Only if they are not found there will it look in the default location.

So how does TNS even determine the value of ORACLE_HOME and/or TNS_ADMIN? In a proper operating system (any *nix system) it would simply check the environment variables assigned to that process. But since we’re dealing here with Windows, we also have to check the registry. So let’s look at some examples to see the effect of various settings. We can check the result by using the Oracle utility ‘tnsping’. I won’t go into the details of tnsping here. Suffice it to say that one of the things it does (almost as an aside) is report where it found sqlnet.ora. It also shows the address information it got from tnsnames.ora. We can use those two bits of information to see exactly which copy of a file it is using.

Here’s the setup. I have a laptop running Windows XP-Pro. On it I have installed a standard Oracle 10.2 client, with ORACLE_HOME established at C:\oracle\product\10.2.0\client_1. This is my working client setup that I use every day at the office. For this demo, I’m going to simulate a second ORACLE_HOME by creating C:\oracle\product\10.2.0\client_2\network\, and two directories outside of ORACLE_HOME, c:\oranet\admin_3 and c:\oranet\admin_4.

First, let’s look at my current setup. Checking the registry (HKLM\software\oracle) we see that ORACLE_HOME is set to c:\oracle\product\10.2\client_1

And checking the environment at the command line, we see there is NOT a setting for ORACLE_HOME, leaving the registry as the only influencing factor.

C:\> echo %ORACLE_HOME%
%ORACLE_HOME%

Now, let’s see what tnsping says about where it finds the config files:

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 15-FEB-2
011 18:27:09

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (40 msec)

C:\>

The key thing to observe here are lines 8 and 9

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

This tells us which sqlnet.ora file was found and used. We see that in this case, TNS was looking for its configuration files exactly where we expected – %ORACLE_HOME%\network\admin. Also notice in line 13, “SERVICE_NAME = vlnxora1”. This information comes from the tnsnames.ora file, and in my setup, that service name is unique to the tnsnames file at that location. So we can know that both the sqlnet.ora and tnsnames.ora files were located at the same place.

Now let’s try some overrides. We’ll set ORACLE_HOME as an environment variable at the command prompt and try it:

C:\>set ORACLE_HOME=C:\oracle\product\10.2.0\client_2

C:\>echo %ORACLE_HOME%
C:\oracle\product\10.2.0\client_2

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 16-FEB-2
011 10:40:52

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_2\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora2)))
TNS-12557: TNS:protocol adapter not loadable

C:\>

Now we see in lines 13-14 that sqlnet.ora was found at the ORACLE_HOME we just specified in the environment variable, overriding the specification from the registry.

We also see a new value for SERVICE_NAME at line 18, indicating the location of tnsnames.ora is also dependent on the new ORACLE_HOME.

Don’t worry about the TNS-12557 error. That is a result of the fact that ORACLE_HOME now points to a directory that has no binaries — remember I just set it up as a test for locating sqlnet.ora and tnsnames.ora. It is not a fully functional ORACLE_HOME.

TNS_ADMIN

Next let’s introduce the TNS_ADMIN variable. Just like ORACLE_HOME, it can be set in the registry, or in the command processor environment. For our next test, we’ll use the registry. Here I’ve added TNS_ADMIN to HKLM\software\oracle

Testing again:

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 16-FEB-
011 10:53:31

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
c:\oranet\admin_3\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora3)))
TNS-12557: TNS:protocol adapter not loadable

C:\>

We see that the setting of TNS_ADMIN in the registry overrides ORACLE_HOME in both the registry and the command processor environment.

As one last test, we will add TNS_ADMIN to the command processor environment. This is on top of all of our previous settings:

C:\>set TNS_ADMIN=C:\oranet\admin_4

C:\>echo %TNS_ADMIN%
C:\oranet\admin_4

C:\>tnsping vlnxora1

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 16-FEB-2
011 10:57:12

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oranet\admin_4\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora4)))
TNS-12557: TNS:protocol adapter not loadable

C:\>

And we see that once again the environment setting overrides the registry, and TNS_ADMIN overrides ORACLE_HOME.

Conclusion

The above demonstrations reveal the various settings that control how TNS locates its configuration file (sqlnet.ora) and name resolution file (tnsnames.ora) on a Windows client. It should also be noted that the same rules apply in a *nix environment, except that there is no registry; in a unix environment, all of the settings are controlled by the environment variables. So unix will have far fewer variables to consider when trying to troubleshoot a connection problem.

I started to do some further testing to see how TNS would handle a situation where one of the files was not in the preferred location. Needless to say, with two files to be located, two variables to indicate the location (ORACLE_HOME and TNS_ADMIN) and two places those variables could be defined (registry and session environment), the possible combinations explode geometrically. Life is too short.

At this point you may be asking if you should use the TNS_ADMIN setting or not. The answer, like most everything is …. “it depends”. If you have a simple client installation with a single ORACLE_HOME, there is no reason to set TNS_ADMIN. However, if you have multiple client products, each with its own ORACLE_HOME (say, you require both 32-bit and 64-bit clients) you are faced with maintaining two separate sets of files. There are various ways to approach this problem, but the simplest is to simply pick one location, and set TNS_ADMIN to point to it. As I demonstrated above, if using TNS_ADMIN, the files don’t even have to be in any ORACLE_HOME directory. I’d even argue that this is preferable, as it makes it very clear to anyone examining the system that there is no implied linkage between your TNS config files and any particular ORACLE_HOME.

Further reading

I would urge the reader to spend some time with Google to read up on “OSI Network Model”, keeping in mind that the TNS modules occupy the session layer of the model. One should also spend some time getting familiar with Part One of the Database Net Services Administrator’s Guide. The 11.2 version of that document is here, and the 12.1 version is here.

Charles Hooper has a nice blog article that relates. See Finding a New Home for a Client on Windows.

Postscript:

In reviewing and editing this article for re-publication, I realized that all of the demonstrations were dealing with starting sqlplus from the command line at an already started cmd.exe. I never addressed starting sqlplus or any other client from the context of the Windows “start” menu. Doing so would simply add several more multiplying factors of complexity which, even in review, I see no point in trying to unravel. This just comes back to one of my basic philosophies: “Live by the GUI, die by the GUI”. Perhaps some day when I’m bored and out of ideas I’ll explore this.

Next up: ora-12154 and tns-03505

Film at eleven.

Advertisement

Troubleshooting ora-12154

This is a revised version of an article I originally created using  Oracle 10g client on Windows XP. While we have since moved on to newer versions of Oracle and Windows, the demonstrations using 10g are equally applicable to 11g and 12c installations.

Continuing the discussion of “why can’t I connect to my database?”, I’d like to focus on one particular error – ora-12154. For this discussion and demonstration we will use Oracle’s sqlplus command processor as our client, but the principles apply to any client program that uses sqlnet to connect to the database.

Hardly a day goes by that someone doesn’t post a problem on OTN, reporting they are getting an ora-12154 trying to connect to their database. And invariably they are sent on a wild goose chase, checking everything that can go wrong anywhere along the network stack. And yet, ora-12154 means one thing, and one thing only: The client side of sqlnet could not find the specified connect identifier. Period. And the official description of the error, returned by the Oracle utility “oerr” spells it out:

[oracle@vmlnx01 ~]$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause: A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.

Or more succinctly: “A connection … was requested using a connect identifier (which) could not be resolved into a connect descriptor.” So what is the connect identifier, and how does it get resolved to a connect descriptor? Let’s take the classic, textbook example of a simple client connection request:

c:> sqlplus scott/tiger@orcl

By the rules Oracle uses to parse the command line, the “@” symbol is used to mark the beginning of the connect identifier. So in this example, the connect identifier is “orcl”, and an ora-12154 means an entry for “orcl” could not be resolved to a “connect descriptor” – the IP address, port number, and service name necessary to properly route the request across the network to the Oracle listener. There are several methods available to make this name resolution, but by far the most common is the use of “local naming” – the tnsnames.ora file. This file serves no other purpose, so you should have it fixed firmly in your mind that it is used only by the client process. It is the client’s “telephone directory”. You may see a tnsnames.ora file on the machine hosting your database, but it is still only used by client processes that happen to run on that machine.

With this preliminary information out of the way, let’s dig in and see how many ways we can create (and conversely, fix) an ora-12154. We will first look at a good configuration, then we’ll start taking it apart. My database is running on an Oracle Enterprise Linux server, while my client is running on my laptop, under Windows XP-Pro. My tnsnames.ora looks like this:

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\net
work\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
mytestdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vlnxora1)
    )
  )

Make a connection to the database, just to show that everything is in order.

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:14:38 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Right out of the textbook! So what could go wrong, to generate an ora-12154?

Wrong naming method

The first thing sqlnet has to do is determine what naming method is being used – exactly how it is to resolve the connect identifier to an IP address, port, and service name. To do this, it looks in the file ‘sqlnet.ora’ (on the client machine, of course!) for the parameter NAMES.DIRECTORY_PATH. If we intend to use local naming, we must set this parameter appropriately:

NAMES.DIRECTORY_PATH=(TNSNAMES)

(Note: the string ‘TNSNAMES’ as a value for this parameter is not the name of the file. It is the name of the method to be used for name resolution. The name of the file used for this method is always tnsnames.ora. )

Let’s set it to some other value (albeit a potentially valid one) . . .

C:>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo
rk\admin\sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (LDAP)

. . . and observe the behavior

C:>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:27:46 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

In this example, we told sqlnet to use LDAP naming services to resolve the connect identifier. However, LDAP is not available in my test environment, thus it was not able to resolve “mytestdb”. Lesson: If you want to use local naming, you must specify such in the SQLNET.AUTHENTICATION_SERVICES parameter in the client’s sqlnet.ora file. Let’s fix the problem, then move on:

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo
rk\admin\sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:52:33 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Can’t locate tnsames.ora

The entire subject of locating tnsnames.ora was covered in “Locating Oracle’s Network Configuration Files” . For now, I will create the simplest means of not being able to locate the file – I’ll rename it to something else . . .

C:\>cd C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>ren tnsnames.ora tnsnames.sav

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>dir tnsnames.ora
 Volume in drive C has no label.
 Volume Serial Number is 04C2-AD70

 Directory of C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

File Not Found

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>

. . . and observe the result:

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:57:14 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

Of course, we fix it by providing a (valid) tnsnames.ora file:

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>ren tnsnames.sav tnsnames.ora

C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 15:58:37 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Can’t find connect descriptor in tnsnames.ora

We’ve told sqlnet to use tnsnames, and we’ve made sure sqlnet can find tnsnames.ora. But what if the connect identifier we provide cannot be found in tnsnames.ora? And more importantly, what can cause it to not be found?

Of course, the simplest reason would be that we just gave it the wrong string. In our example file (shown above), we had a single entry, with the connect identifier of ‘mytestdb’. If we provide a different connect identifier it will, of course fail – again with ora-12154:

C:\>sqlplus scott/tiger@fubar

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:05:48 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

But there is another factor that comes into play at this point. There is an optional configuration parameter that we have not yet discussed. Before looking in tnsnames.ora, sqlnet will check sqlnet.ora to see if the parameter NAMES.DEFAULT_DOMAIN has been set. If it has, it’s value will be appended to the connect descriptor supplied by the user – before searching tnsames.ora for the result. Let’s add that parameter to our existing sqlnet.ora . . .

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
# sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\netwo
rk\admin\sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
NAMES.DEFAULT_DOMAIN=acme.com

. . . and observe the result

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:13:42 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

There are two ways to fix this error. Obviously, we can revert back to our previous configuration by removing the NAMES.DEFAULT_DOMAIN parameter from sqlnet.ora. The other would be to add the domain name to the entry in tnsnames.ora.

C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\net
work\admin\tnsnames.ora
#===========================
mytestdb.acme.com =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vlnxora1)
    )
  )

C:\>sqlplus scott/tiger@mytestdb

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:30:57 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

tns-03505

That pretty well covers every means of creating an ora-12154 when using local naming. What about the tns-03505 error? Actually, tns-3505 is exactly the same error as ora-12154, with all the same causes and resolutions. The only difference I’ve been able to discover is that tns-03505 is produced when using the ‘tnsping’ utility. Why Oracle chose to code tnsping this way I don’t know, and I’ve been unable to find definitive documentation on this difference but my own testing bears it out.

Using sqlplus . . .

C:\>sqlplus scott/tiger@fubar

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Feb 26 16:44:33 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ora-12154: TNS:could not resolve the connect identifier specified

Enter user-name:

And using tnsping . . .

C:\>tnsping fubar

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 26-FEB-2
011 16:44:42

Copyright (c) 1997, 2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

tns-03505: Failed to resolve name

C:\>

(Thanks to Sybrand Bakker for pointing out this distinction).

I will discuss uses and mis-uses of ‘tnsping’ in another post.

This’n’that

There is one often overlooked ‘gotcha’ in all this. Remember that when sqlnet is parsing out the connect string, it uses the ‘@’ sign as a meta-character to mark the beginning of the connect descriptor. So if we have this:

c:>sqlplus scott/tiger@orcl

everything is just fine. But suppose there is an ‘@’ sign someplace unexpected. What happens if someone constructs a complex password that includes it?

c:> sqlplus scott/P@ssW0rd@orcl

In this case, sqlnet will take the ‘P’ as the password, and take ‘ssW0rd@orcl’ as the connect descriptor. By now, you can guess the result.

Conclusion

In this article I have attempted to demonstrate every possible cause of an ora-12154 error, as relates to use of tnsnames.ora, for address resolution of a connect descriptor. There is one overriding factor here that should be obvious from the very description of the error, regardless of the naming method used: ora-12154 is a client problem. It has absolutely nothing to do with anything on the server. It has nothing to do with the listener. It has nothing to do with the database. If you are faced with a ora-12154, you can monkey around with your database and/or listener “until the cows come home” and you will not solve your problem. With ORA-12154 your request never left the client process.

Stay tuned for more discussion of sqlnet connection problems.

Dissecting ORA-12545 errors

This is a revised version of an article about ORA-12545 that I originally published while using Oracle 10g client on Windows XP. While we have since moved on to newer versions of Oracle and Windows, the demonstrations using 10g are equally applicable to 11g and 12c installations.

Introduction to ORA-12545

Continuing our discussion of “Why can’t I connect to my database”, I want to focus on “ORA-12545: Connect failed because target host or object does not exist”.

To recap what we’ve covered so far, when an oracle client requests a connection to a database, it has to provide a “connect identifier”, which sqlnet then translates to a “connect descriptor”. The connect descriptor specifies the transport protocol (usually tcp), the ip address of the database server, the port being used by the listener, and the service name the database has registered with the listener. This name resolution is usually done by looking up the connect identifier in the client’s tnsnames.ora file, though there are other methods available as well. Failure to find an entry from which to derive the connect descriptor will result in an ORA-12154: TNS:could not resolve the connect identifier specified, which I explained here.

Once the connect descriptor is determined, that information becomes part of the packet that is passed to the transport layer of the standard OSI network stack and is thus used for standard network routing. This is important to understand. At this point, the routing of the packet to its destination is handled entirely by standard network transport layers.

The Setup

For this demonstration, I am using an Oracle 10.2.0.4 client on Windows XP, connecting to an Oracle 10.2.0.4 database on Oracle Enterprise Linux 5. Name resolution is through tnsames.ora, which looks like this:

fred =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vlnxora1)
    )
  )

Listing 1.

(By the way, I like to use “fred” as an alias name in demonstrations because it should be obvious that it is a totally artificial name that doesn’t have any inherent relationship to anything. As in the above tnsnames example, in a production system I would use the service name (vlnxora1) as the connect identifier, but in demonstrations I want the distinction to be crystal clear.)

The key information we are focusing on is the “HOST = vmlnx01” on line 4.

First, let’s make a good connection to prove that everything is working correctly, then we will break it.

C:\>sqlplus scott/tiger@fred

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 5 09:45:17 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Listing 2.

The Demonstration

We know that in order for a message to be routed across a network, we need an ip address. This is like placing a telephone call. If I want to call Moe, I can’t just pick up my phone and dial “Moe”. The telephone company switchboards don’t know anything about Moe. There has to be some mechanism to translate “Moe” to 1-555-123-4567. Likewise the network routers don’t know anything about “vmlnx01”. For your telephone you would have some sort of directory to tell you (or your smart phone!) what Moe’s number is. In a corporate environment, you probably have a DNS server to tell the network stack that “vmlnx01” is 192.168.160.101. The other mechanism, and the one that trumps a DNS lookup, is a file on the client, named simply “hosts”. On unix, this will be at /etc/hosts. On my Windows XP machine it is at C:\WINDOWS\system32\drivers\etc\hosts. Given Microsoft’s propensity for reshuffling the deck with each new release of Windows, I can’t promise that is where the file should be located on your machine!

My hosts file looks like this:

127.0.0.1 localhost
192.168.160.101 vmlnx01 vmlnx01.vmdomain

Listing 3.

For those not familiar with this file, the format is

ipaddress alias1 alias2 .... aliasN

Listing 4.

All hosts files should have the same first line, equating ip address 127.0.0.1 to the alias “localhost”. All other entries typically have two aliases, one with the unqualified server name, the other with the fully qualified servername.domain. That is by convention and for everyone’s convenience, but the fact is these are just aliases for the ip address, and like any alias can actually be anything you want. I will demonstrate that after taking care of the business at hand.

At this point, we know that we told the network to route our request to ‘vmlnx01’, and by using the local hosts file, it was able to translate ‘vmlnx01’ to ‘192.168.160.101’. Let’s set it so that it can’t make that translation, and see what results. We will do that by removing the entry for vmlnx01 from the hosts file:

127.0.0.1 localhost
192.168.160.101 fubar.vmdomain fubar

Listing 5.

Then test:

C:\>sqlplus scott/tiger@fred

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 5 10:13:45 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12545: Connect failed because target host or object does not exist

Listing 6.

And we have our error. Please notice that we did NOT touch our tnsnames.ora file, which we’ve already proven to be good. This error is simply oracle reporting what the OS network returned. Of course, since it resulted from a mis-match between the HOST parameter in tnsnames and the entries in the hosts file, the proper fix is could be in either file. It is often suggested to avoid this problem by hard-coding the IP address in the tnsnames (HOST=192.168.160.101), but I consider that to be a hack taken by those who do not understand how net name resolution works. It is certainly poor practice to hard-code an IP address any place an alias can be used. Just think of the problems caused by hard-coded IP addresses when the network administrator restructures the net.

Conclusion

This particular error is exactly analogous to “ORA-12154: TNS:could not resolve the connect identifier specified”. With ORA-12154, sqlnet couldn’t find a way to translate “fred” to a complete connect descriptor. With ORA-12545, the OS network layer couldn’t find a way to translate ‘vmlnx01’ to an ip address.

“Film at eleven . . .”

Registering the Oracle database with the listener

In my previous article, ORA-12514: TNS:listener does not currently know of service, I demonstrated the relationships between a client requesting a connection to a database, and the database services known to the listener. In that article I focused only on the immediate cause of the error, deferring the discussion of exactly how databases get registered with the listener. In this article we will take up that discussion and try to clarify the means by which databases come to be known by the listener.

While the examples and documentation references in this article are using Oracle 12c, the same information applies equally to all versions of Oracle at least back to 9i.

The Oracle database listener

The Oracle listener is a process that runs on the database host server – the same machine that hosts the database itself. It serves as a connection broker, and all connection requests that come across the network must first be handled by the listener. If the listener is not up, or if it is up but does not know about the requested database, the connection request will be rejected by the listener without getting anywhere near an actual database.

Before going any further, let me make one point crystal clear. Please burn this into your brain:

One single listener, with the default name of LISTENER, using the default port of 1521, is quite capable of — indeed, was designed to — service multiple databases of multiple versions running from multiple homes.

I often see people trying to create and manage a separate listener for each database on a server. This does nothing to increase performance or security. It only serves to complicate administration and troubleshooting.

You also need to very clearly understand that the database instance and the listener are two completely separate and distinct processes. Creating a database does not create a listener. Neither one (database nor listener) is a sub-process of the other. Just yesterday I had to explain this to a developer in my shop.

Whew! Glad to get that off my chest! Let’s get back to the business at hand.

Database registration

When the listener knows about a database, it is said that the database is “registered” with the listener. Think of the listener as the front desk clerk at a hotel, taking messages for guests. There are two ways he can know who he needs to take messages for. First, he can be given a list of guests. He has no idea how accurate the list is. He doesn’t know if the listed guests actually exist or if they are in the rooms listed. All he can do is say “ok, if someone asks to contact a guest on this list I’ll do my best to connect them.” The other method is for the guests themselves to tell the clerk who they are and what room they are in.

Likewise, there are two methods by which databases are registered with the listener – “static” and “dynamic”. We’ll discuss each in turn.

Static registration

Static registration is like our desk clerk being given a list of guests. In the case of the listener, this “guest list” is the SID_LIST section of the listener.ora file. The listing for each individual, specific SID begins with the SID_DESC identifier under the SID_LIST section.

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME=myfubardb)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = fubar)
    )
    (SID_DESC =
      (GLOBAL_DBNAME=tulsa)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = tulsa)
    )
  )

Fig. 1

In Fig. 1 we see two SIDs listed: “fubar” and “tulsa” (lines 17 and 22). Checking the status of the listener, we see

oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JAN-2016 18:09:42

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-JAN-2016 18:09:22
Uptime                    0 days 0 hr. 0 min. 19 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "myfubardb" has 1 instance(s).
  Instance "fubar", status UNKNOWN, has 1 handler(s) for this service...
Service "tulsa" has 2 instance(s).
  Instance "tulsa", status UNKNOWN, has 1 handler(s) for this service...
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

Fig. 2

Notice the entry for service “myfubardb” (Fig. 2, line 23) maps back to “GLOBAL_DBNAME=myfubardb” in the listener.ora file (Fig. 1, line 15) and is related to instance “fubar”, which maps to “SID_NAME=fubar” in listener.ora (fig, 1, line 17). Further, notice that its status is UNKNOWN. This status of UNKNOWN is the indication that this registration came from the SID_LIST section of listener.ora. It is “unknown” because the listener does not make a check to see if there really is an instance named “fubar” broadcasting a service name of “myfubardb”. The listener, like our imaginary hotel clerk, is just saying “if you ask for a connection to myfubardb, I’ll see what I can do to service it.” In fact, I have no database named “fubar” or “myfubardb”.

Notice also that service “tulsa” has two instances, one UNKNOWN and one READY (Fig.2, lines 26 and 27). Like myfubardb, the UNKNOWN tulsa comes from listener.ora (Fig. 1, line 22); the READY instance comes from the database having registered itself with the listener. This is known as “dynamic registration”, and we will delve into that in just a minute.

Again, for our current discussion, we can ignore the service tulsaXDB. This has special internal use for Oracle.

For the remainder of the discussion, I am going to completely remove listener.ora, then restart the listener so that it has no static registrations and is running with all default values:

oracle:tulsa$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin

oracle:tulsa$ mv listener.ora listener.save

oracle:tulsa$ ls listener.ora
ls: cannot access listener.ora: No such file or directory

oracle:tulsa$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JAN-2016 18:21:55

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

oracle:tulsa$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JAN-2016 18:22:26

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-JAN-2016 18:22:27
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully

oracle:tulsa$

Fig. 3

With no listener.ora file, the listener will start with all default values and support no services (Fig. 3, line 43) until a database process registers itself. Obviously there is no static registration without listener.ora.

Dynamic registration

Dynamic registration is accomplished when the pmon process of the database instance contacts the listener and requests registration. This occurs at instance startup, and every few minutes during the life of the instance. In our hotel clerk analogy, this is a guest periodically dropping by the front desk to inform the clerk of his availability.

There are three database initialization parameters that affect what service name(s) pmon will register with the listener:

DB_NAME

SERVICE_NAMES

DB_DOMAIN

You should look up each one in the Reference Manual and read the descriptions. (Click on the links) Notice particularly in the description of SERVICE_NAMES the following:

If you do not qualify the names in this parameter with a domain, Oracle qualifies them with the value of the DB_DOMAIN parameter. If DB_DOMAIN is not specified, then no domain will be applied to the non-qualified SERVICE_NAMES values.

There is another interaction that is not spelled out in the Reference Manual, but mentioned in the Net Services Administrator’s Guide:

The service name defaults to the global database name, a name comprising the database name (DB_NAME parameter) and domain name (DB_DOMAIN parameter)

Since neither DB_DOMAIN nor SERVICE_NAMES are required parameters, let’s start with an instance with neither of those set, then start observing how service names get constructed with various settings. For each iteration I will do the following:

1. Alter an initialization parameter.
2. Bounce the database (Some of the parameters require it. To keep things clean and consistent, I’ll do it for all of them.)
3. Restart the listener. (To flush the old registrations.)
4. Force a new registration. (The database will register on its own, but that may take up to a minute. Here we force it simply to avoid the wait.)
5. Show the listener status, with the results of the new registration.
6. Show the values of all three parameters, for comparison.

On the first iteration I will show the entire sequence. On subsequent iterations I will show only the key parts.

(Note that in all of the demonstrations, I take the convenience of stopping and starting the listener from within my sqlplus session. If you are not familiar with it, sqlplus has the ability to have the host operating system execute commands. This is done with the ‘host’ directive, implemented with the ‘!’ symbol. See the SQL*Plus User’s Guide and Reference for more information.)

oracle:tulsa$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 23 13:24:14 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter system set service_names='' scope=spfile;

System altered.

SQL> alter system set db_domain='' scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  713031680 bytes
Fixed Size    2928488 bytes
Variable Size  520093848 bytes
Database Buffers  184549376 bytes
Redo Buffers    5459968 bytes
Database mounted.
Database opened.
SQL> !lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:25:57

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

SQL> !lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:26:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 13:26:03
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully

SQL> alter system register;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:26:25

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 13:26:03
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
Services Summary...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_name;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string tulsa
SQL> show parameter service_names;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names     string
SQL> show parameter db_domain;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain     string
SQL>

Fig. 4

In Fig. 4, at lines 91 and 93, we see two service names. Both are associated with the instance “tulsa” and derived their name from the initialization parameter “db_name”.

Next, we add a simple service name. While normal practice might be to make it the same as the db_name, I’ll make it different so that we can trace it to the end result. Remember, it is quite acceptable to have multiple service_names, which we will get to in a moment.

SQL> alter system set service_names='edstevens' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

SQL> alter system register;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:34:25

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 13:33:59
Uptime                    0 days 0 hr. 0 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
Services Summary...
Service "edstevens" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_name;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string tulsa
SQL> show parameter service_names;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names     string edstevens
SQL> show pra  arameter db_domain;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain     string
SQL>

Fig. 5

Now we see that all of the service names derived from db_name are still in place, but we have also added one derived from service_names (Fig. 5, lines 34 and 51).

Next we set db_domain

SQL> alter system set db_domain='acme.com' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

SQL> alter system register;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:39:17

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 13:39:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
Services Summary...
Service "edstevens.acme.com" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsa.acme.com" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB.acme.com" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_name;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string tulsa
SQL> show parameter service_names;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names     string edstevens
SQL> show parameter db_domain;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain     string acme.com
SQL>

Fig. 6

Notice that all service names – those derived from db_name as well as the one derived from service_names – have the value of db_domain appended to them (Fig. 6, lines 34, 36, 38, and 56).

Next we add a second service name, this one qualified with a second domain name. Not something you’d normally do, but useful for demonstrating the interaction of the parameters

SQL> alter system set service_names='edstevens,wiley.coyote.com' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

SQL> alter system register;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 13:42:21

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 13:42:20
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
Services Summary...
Service "edstevens.acme.com" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsa.acme.com" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB.acme.com" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "wiley.coyote.com" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> show parameter db_name;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string tulsa
SQL> show parameter service_names;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names     string edstevens,wiley.coyote.com
SQL> show parameter db_domain;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain     string acme.com
SQL>

Fig. 7

As before, all of the unqualified service names derived from db_name and service_names have been qualified with the value of db_domain. But notice that we have a new service name (Fig. 7, line 40) from the second value supplied from service_names (line 53). Since this was fully qualified in the service_names initialization parameter, the value of db_domain was not applied.

Conclusion

We have explored the relationship between the connect descriptor issued by the client and the services supported by the listener, as well as the factors that control what services the listener supports.

There is one question I’ve not addressed but hopefully is bouncing around in the back of your mind. Every process (like a database instance) that needs to communicate with another process (like a listener) must have some means of locating that process and delivering the message. Have you asked yourself how it is the database instance actually locates the listener? Especially since the listener is specifically expecting to receive messages from the network? I will take up that issue in my next article, discussing the LOCAL_LISTENER initialization parameter.

Film at eleven …

 

The Oracle Transparent Gateway

In this article I will trace the chain of events in establishing a link from an Oracle database to a non-Oracle database, using the Oracle Transparent Gateway. We will see how each configuration point in the chain points to the next, establishing all the links in the chain for an Oracle database to act as a client to a non-Oracle database. I will also show a high-level comparison of how the same functionality is implemented in the reverse situation, where SQL Server is connecting to Oracle.

I first developed this presentation as a response to a statement made by a colleague, regarding the supposed complexity of Oracle. He insisted that Oracle “has too many parts” whereas SQL Server “just automatically connects”. I had to show that regardless of database product, heterogeneous connectivity comes down to the same fundamentals. It is just that various RDBMS products package those functions differently. Before getting into the specifics, let’s take a look at those fundamentals.

Fundamental Number 1: Every database product has its own unique call interface.

This is much deeper than just implementation of the SQL language. It involves the program calls at the binary executable level. Any connection between any two disparate database products must pass through a process to make the necessary translation, to meet the specifications of the receiving binary API. This is the reason ODBC was developed in the first place. If database “A” attempts to connect to database “B”, database “A” is no different than any other client program attempting to connect to database “B”. As far as “B” is concerned, “A” is “just another client” and must be able to communicate with “B”s API.

• Oracle provides the Transparent Gateway to allow its databases to connect to non-Oracle databases.
• Microsoft provides OLE DB Providers to allow its databases to connect to non-MSSQL databases.

Fundamental Number 2: All communication relies on established network protocols

All network communication conforms to the Open Systems Interconnection (OSI) model. In the OSI model, communication between separate computers occurs in a stack-like fashion with information passing from one node to the other through several layers of code. These layers are as follows:

1. Physical layer
2. Data link layer
3. Network layer
4. Transport layer
5. Session layer
6. Presentation layer
7. Application layer

On the client side the communication request enters the OSI stack at the application layer and leaves the client computer at the physical layer. The request arrives at the target (server) computer at the physical layer and works its way back up the stack to the application layer. Further discussion of the relationship between Oracle’s networking components and their place in the OSI model can be found in the Oracle Database Net Services Administrator’s Guide.

Oracle / MSSQL Database Communication Overview

• Oracle provides translation to MSSQL via the Oracle Transparent Gateway.
• MSSQL provides translation to Oracle via OLE DB providers.
• Oracle Gateway and OLE DB Provider serve the exact same purpose. Both come with either a direct host-to-target version, or a generic version that uses ODBC as an intermediary.

For the remainder of this article I will show how each link of the configuration points to the next. While I will be demonstrating with an Oracle database connecting to a Microsoft SQL Server database (MSSQL), you can just as easily substitute any other non-Oracle database for the MSSQL references.

SQL statement references a database link

In Oracle’s implementation of the SQL language, the “@” symbol following a table name reference indicates that the table is in another database. The string following the “@” symbol is the name of a database link (db_link). That link provides the first step from the current Oracle database to the MSSQL database.

Database link to tnsnames.ora

When the SQL engine locates the referenced db_link, the value of the ‘hosts’ column is used as the SQL*Net connection string. This can be either a fully formed network address description or a SQL*Net Net Service Name. If the latter (recommended) it will be resolved just like any other client connection request – usually by looking it up in the tnsnames.ora file. It is important to note that the Oracle database itself is acting as a client, so it will be the tnsnames.ora located on the Oracle host server.

Network Transmission

When sqlnet receives the request from the client, it resolves the actual addressing information from tnsnames.ora, creates an IP packet, and hands it off to the protocol layer of the OSI stack.

Listener Receives IP Transmission

When the listener receives the packet, it sees that the specified SID matches an entry in its SID_LIST configuration – the SID_LIST section of listener.ora. And in that SID_DESC specification, there is a specified program to be launched – dg4odbc. Instead of attempting to spawn a server process to connect the client to a database, the listener will launch the executable db4odbc and pass the name of the requested SID.

Oracle Gateway Bridges to ODBC

When the program otg4odbc receives the request, it uses the specified SID as the basis for identifying the necessary configuration file. In our example, the SID was specified as ‘northwind’, so the name of the configuration file is ‘initnorthwind.ora’. This file, in turn, provides the ODBC Data Source Name (DSN) used to actually access the target database.

At this point the final leg of the journey is made by the ODBC driver specified when defining the DSN ‘northwind’. Configuration of the ODBC DSN is as appropriate for the target database.

Once these connections are made, the final communications path is as follows. The originating database, through its db link, communicates with the Oracle Transparent Gateway for ODBC, via a sqlnet link. The gateway communicates with the ODBC driver. And the ODBC drive communicates with the target database.

Conclusion

If you have followed this carefully, you should have a much better understanding of how the pieces fit together. There are a few points that bear highlighting:

1. We used the database name ‘northwind’ throughout. While it is nice to have the same name at every point, you need to be aware of where the same name is required between two points and where one usage is just an alias for the next. For instance, at the very beginning we had a database link named ‘northwind’ that referenced net service name (tnsnames.ora entry) named ‘northwind’. Technically, we could have made the net service name something else. Oracle would not care. It is just for our own sanity as humans that we keep the same name throughout.
2. The listener is only involved in getting the server process (otg4odbc) started and informing the calling client what port to use for continued communications. Once that is done, the listener is out of the picture. The listener’s port (1521, by default) is only used to contact the listener. It is not used for continuing communication between the client database (Oracle) and the target database.
3. The Oracle Transparent Gateway can reside on any server. It is not required that it be on the same server as the originating Oracle database. Nor is it required that it be on the same server as the target non-Oracle database. In my shop, we have multiple Oracle databases residing on multiple servers. Each database is subject to having links to multiple MSSQL databases, also residing on any of several different servers. To simplify administration, I selected one server to host the Oracle Transparent Gateway for all combinations. This could be one of the Oracle servers. Or it could be one of the MSSQL servers. Or it could be another server that hosts no database at all.

Questions? Feel free to ask. My next article will explain the same process in reverse – when an MSSQL database uses its linked server mechanism to act as a client to an Oracle database.

Film at eleven . . .

A Few Points About Dynamic Registration

In a recent thread on OTN, the OP was having a difficult time understanding dynamic registration. They were confused about the interactions of settings in listener.ora, tnsnames.ora, and the local_listener parameter. After responding to the thread with a summary, I thought it might be worth repeating here, along with a few more references. I actually covered all of these points in earlier articles, “Registering the Oracle Database With the Listener” and “Exploring the Local Listener Parameter“, but sometimes a simply restating key points can help clarify things for some people.

In short, the summary of important points are as follows:

1) The listener itself doesn’t give a flying fig about what is in tnsnames.ora. That file (tnsnames.ora) is used ONLY by client processes. In the case of dynamic registration, the database IS the client process.

2) The listener is quite capable of starting with no listener.ora file at all. In this case it will start with all default values, including the default name of LISTENER and default port of 1521. For most people, most of the time, this is sufficient.

3) If the local_listener parameter is not set (null) the database will send the registration request to port 1521. Notice that 1521 is also the default port of of the listener.

4) If you choose to set local_listener, you can either use a full connect string ((ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1421))) or simply reference an entry in tnsnames.ora, to resolve to the address (server name and port) of the listener.

5) The SID_LIST section of listener.ora has nothing to do with dynamic registration. Quite the opposite. The SID_LIST section is how you implement static registration.

The original thread on OTN may be found here: “Oracle Net Service Configuration“. In that thread, there was also a link to a very nice explanation by Martin Beach on his blog “Martin’s Blog”, “Little things worth knowing-static and dynamic listener registration“

Another ora-12514, and TWO_TASK

I recently assisted a user on OTN with an ora-12514 that was a bit more of a puzzle, and in the end resulted from the “interference” of the TWO_TASK environment variable. My explanation to him looked very much like a good posting, so here’s the improved version.

Background

There are essentially two methods of establishing a client connection to an Oracle database. They are

• Network (TNS)
• Bequeath (inter-process)

Network (TNS) Connections

This is the classic connection, created when the client specifies a tns connect string, as in this example

sqlplus scott/tiger@orcl

Fig. 1

In this case, the existence of the ‘@’ specifies that what follows (‘orcl) is a tns Net Service Name, which TNS will have to resolve to get the routing information. Typically this is an entry in the client’s tnsnames.ora file. That entry will specify a host name or IP address, a port, and a SERVICE (preferred) or SID. There should be an Oracle listener on the specified host, listening on the specified port, and able to service requests for the specified service.

This type of connection goes through the standard network stack, even if the client is on the same machine as the database instance.

Bequeath Connections

The bequeath connection is an inter-process connection. As such, it can only occur when the client and the database instance are on the same machine. This type of connection is made when you do not specify a net service name, as shown here:

sqlplus scott/tiger

Fig. 2

When that type of connection is requested, the target database instance is determined by interrogating the ORACLE_SID environment variable. If ORACLE_SID=orcl you will be connected to the instance ‘orcl’. If ORACLE_SID=mydb you will be connected to the instance ‘mydb’. Of course, this assumes ‘orcl’ or ‘mydb’ are up and running.

The Problem

So how can you get a TNS error, like ORA-12514 or ORA-12154, when you are not specifying a TNS connection? That’s where TWO_TASK comes into play. If you have TWO_TASK set, the Oracle client will take that as a specification for a TNS connection. Setting TWO_TASK=orcl is effectively the same as specifying “@orcl” on your connection request.

Before we get started with the demonstration, there is one other bit of information you need to know. As in many cases, when the client is on Windows, there is a variation to be aware of, and that is that instead of TWO_TASK, the variable name is LOCAL. Most of the literature talks only about TWO_TASK, but when you are dealing with Windows (as most client applications will) you need to substitute “LOCAL” for “TWO_TASK”. I will demonstrate that at the end.

Let’s demonstrate.

First, I’m going to shut down my listener. That will create a situation where only bequeath connections can succeed. Any tns connections will fail due to the listener being down.

oracle:tulsa$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-FEB-2016 16:46:44

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
The command completed successfully

Fig. 3

With that in place, a tns connection will fail

oracle:tulsa$ sqlplus scott/tiger@tulsa

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 16:50:20 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener


Enter user-name:

Fig. 4

And a bequeath connection will succeed

oracle:tulsa$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 16:51:35 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Feb 06 2016 10:29:43 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Fig. 5

Now, let’s throw TWO_TASK into the mix. I’ll set TWO_TASK to the same value as I had specified in my earlier tns connection attempt, but notice that I do not specify anything on my actual connection request. It is exactly the same request as succeeded above.

oracle:tulsa$ export TWO_TASK=tulsa

oracle:tulsa$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 16:53:30 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener


Enter user-name:

Fig. 6

Voila! TWO_TASK imposes an attempt at a tns connection, without explicitly asking for it.

Now, let’s start the listener, allow the instance to dynamically register, and try again with TWO_TASK.

oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-FEB-2016 17:04:07

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-FEB-2016 17:00:29
Uptime                    0 days 0 hr. 3 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
Services Summary...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle:tulsa$ export TWO_TASK=tulsa

oracle:tulsa$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 17:04:57 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Feb 07 2016 16:51:35 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Fig. 7

And with that we can also reproduce the other ‘usual suspects’ of tns errors.

We just saw ‘ORA-12541: TNS:no listener’. How about ORA-12514?

Here’s my tnsnames.ora. Notice I have two net service names, ‘TULSA’ and ‘DALLAS’, each referring to a service name of the same name as the net service name:

TULSA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tulsa)
    )
  )

DALLAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dallas)
    )
  )

Fig. 8

Now, we know from that the tnsnames.ora has a net service name DALLAS (Fig. 8) but it specifies a service name (‘dallas’) that the listener knows nothing about (Fig. 7). So let’s set TWO_TASK to ‘dallas’ and see what happens.

oracle:tulsa$ export TWO_TASK=dallas

oracle:tulsa$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 17:13:09 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

Fig. 9

And if we set TWO_TASK to a value that does not exist in the client’s tnsnames.ora?

oracle:tulsa$ export TWO_TASK=fubar

oracle:tulsa$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 17:14:11 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

Fig. 10

At this point you should be asking “what happens if we set TWO_TASK, but also specify a connect string? (If you are not asking that, you should be asking yourself why you are not asking that!). OK, let’s try it. I’ll set TWO_TASK to a value that we know will return an error, but start sqlplus with a request we know is good

oracle:tulsa$ export TWO_TASK=fubar

oracle:tulsa$ sqlplus scott/tiger@tulsa

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 17:16:04 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Feb 07 2016 17:04:58 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Fig. 11

Windows vs. Linux

I mentioned at the top of the article, when your client is on Windows, the name of the variable is not TWO_TASK but LOCAL.

On a Windows client, first show that neither TWO_TASK nor LOCAL are set:

C:\>set TWO_TASK
Environment variable TWO_TASK not defined

C:\>set LOCAL
LOCALAPPDATA=C:\Users\ed\AppData\Local

C:\>

Fig. 12

Then attempt a connection with no qualifier:

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 9 11:21:56 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:

Fig. 13

With no qualification, the client attempts a bequeath connection to the local database identified by ORACLE_SID. And in this case, my client machine doesn’t have a database at all, so it doesn’t matter what ORACLE_SID is set to, or if it set all. We’d still get the ORA-12560.

Next, set TWO_TASK and try the unqualified connection:

C:\>set TWO_TASK=tulsa

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 9 11:23:23 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:

Fig. 14

Unlike the Linux client, the Windows client ignored TWO_TASK and so the result is the same.

Now we’ll set LOCAL:

C:\>set LOCAL=tulsa

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 9 11:24:56 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL>

Fig. 15

And the result is exactly the same as having set TWO_TASK on a Linux client.

Conclusion

Hopefully by now you have a better understanding of what TWO_TASK (or LOCAL) does, and how it can create a bit of a red-herring in troubleshooting common TNS errors.

So, you might ask, what is the value of TWO_TASK? Quite honestly, I don’t know. I’ve never seen a need for it. I have always thought it looked like a solution in search of a problem. Some will argue that it allows you to establish a connection without having to enter the net service name, but I’d respond, “so what?” Surely those few extra keystrokes are worth the clarity and simplification they bring.

And please understand that my purpose for this article is not to imply that you should be using this feature, or that it is common, or anything of the kind. In fact, my position is that it should only be used when needed to solve a very specific problem. And offhand, I can’t think of what that problem might be. No, my purpose is to make you aware of an often forgotten source of TNS connection problems.

If you want to learn more about TWO_TASK, “Google is your friend”, but you could do worse than to start with Ask Tom.

You can read the full OTN thread that triggered this posting here.

And the official documentation here.

Exploring the LOCAL_LISTENER parameter

In my previous post, Registering the Oracle Database with the listener, I mentioned the role of the initialization parm LOCAL_LISTENER in dynamic registration of the database instance to the listener. Now I’d like to deliver on my promise to explore that piece of the puzzle.

To quickly recap that post, there are two methods by which a listener comes to know what databases it can service. In Oracle terminology, this is referred to as “registering with the listener.”

Static Instance Registration

The first – and older – method is static registration. In this method, the instance is listed in the SID_LIST section of the listener’s configuration file, “listener.ora”. Such a registration would show in the listener.ora like this:

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME=tulsa)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = tulsa)
    )
  )

Fig. 1

And that static registration shows up in the listener status with a status of UNKNOWN:

oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:24:29

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 16:24:25
Uptime                    0 days 0 hr. 0 min. 4 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Fig. 2

At Fig. 2, line 24, the status is UNKNOWN because there is no mechanism to guarantee that the specified instance even exists. The listener just assumes that the instance will be there when a connect request is received. In fact, my database was down when I took the status shown in Fig. 2.

Dynamic Instance Registration

With version 9.0 Oracle introduced the concept of dynamic registration. With this, it is no longer necessary to list the database instance in the listener.ora file. Instead, the database instance contacts the listener directly and registers itself. We can observe the result of that in the listener status. First, I’ll “remove” my listner.ora by renaming it, then restart the listener and see what it says about itself. The listener is quite capable of running without a listner.ora file at all. It will simply start and run with all default values.

oracle:tulsa$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:26:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
The command completed successfully


oracle:tulsa$ mv lsnrctl istener.ora listener.save


oracle:tulsa$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:27:13

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 16:27:13
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully

Fig. 3

So we can see in Fig. 3, line 39 that the listener has started but supports no services. If we try to connect at this point we will get the ora-12514:

C:\>tnsping tulsa

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 23-JAN-2
016 16:29:37

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vblnxsrv02)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = tulsa)))
OK (60 msec)

C:\>sqlplus scott/tiger@tulsa

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 23 16:29:52 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Fig. 4

In Fig. 4, line 1, a tnsping proves that our tnsnames resolution is correct. But at line 23 we see that an actual attempt to connect to the service proves the listener doesn’t know anything about the service “tulsa”.

Now let’s start the instance and check again:

oracle:tulsa$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 23 16:39:03 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  713031680 bytes
Fixed Size    2928488 bytes
Variable Size  520093848 bytes
Database Buffers  184549376 bytes
Redo Buffers    5459968 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:39:26

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 16:27:13
Uptime                    0 days 0 hr. 12 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
Services Summary...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully


oracle:tulsa$ 

Fig. 5

In Fig. 5 we observe that once the instance is started (line 9), when we re-check the listener (line 24) it now knows of service “tulsa”, with a status of READY (line 45). This obviously did not come from listener.ora as I had removed that file. Notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready. And we can prove it by establishing a connection from a remote system:

C:\>sqlplus scott/tiger@tulsa

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 23 16:40:54 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL>

Fig. 6

The LOCAL_LISTENER initialization parameter

So we’ve seen that the listener is able to start up and successfully handle connection requests without a listener.ora configuration file. It does this by using all defaults (including the listener name and port) and the database instance is able to register itself with the listener.

How does the instance know how to contact the listener in order to register itself? It uses the initialization parameter LOCAL_LISTENER. From the Oracle® Database Reference 12c we read

LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners

Let’s see what my instance says about that …

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
SQL>

Fig. 7

OK, so the instance is supposed to use LOCAL_LISTENER to locate the listener so that it (the instance) can register itself with the listener. But I don’t have LOCAL_LISTENER set to anything. Well, it so happens that LOCAL_LISTENER has a default value that dovetails nicely with the default settings of the listener. Again, from the Reference manual:

Default value: (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.

I’ve seldom found a good reason NOT to run the listener with anything other the default name and port, but some people insist, and that’s when we need to adjust LOCAL_LISTENER to match up. So let’s set up a test case.

First, I’ll set my listener to use a non-default port. Notice I’ve also removed the SID_LIST section entirely.

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Fig. 8

And restarting the listener

oracle:tulsa$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:47:56

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 16:47:56
Uptime                    0 days 0 hr. 0 min. 0 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Fig. 9

At this point my listener is up, listening on the non-default port of 1522 (line 28) and knows of no services. With a default setup, I should be able to connect to the database and force a registration. Remember that at this point, my listener is using the non-default port of 1522, while the database is still trying to contact the listener on the default port of 1521.

SQL> show parameter local listener                             show parameter local_listener

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener     string
SQL> alter system register;

System altered.

SQL> 

Fig. 10

I had expected this to return an error, but as you can see, it did not. I also could find no related errors in the alert log. But, as expected, the instance is not registered with the listener:

oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 16:55:04

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 16:47:56
Uptime                    0 days 0 hr. 7 min. 7 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

oracle:tulsa$

Fig. 11

In order to enable the instance to register with the non-default listener, we need to set LOCAL_LISTENER to an appropriate value. Remember from the documentation that “LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners”. So let’s set it:

oracle:tulsa$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 23 17:32:23 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1522))' scope=both;

System altered.

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JAN-2016 17:32:44

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                23-JAN-2016 16:47:56
Uptime                    0 days 0 hr. 44 min. 47 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully


oracle:tulsa$

Fig. 12

So by setting LOCAL_LISTENER to the values appropriate for the listener, it is again able to contact the listener and register its services.

Did it occur that the setting of LOCAL_LISTENER looks a lot like an entry in tnsnames.ora? Remember that the description of LOCAL_LISTENER “specifies a network name that resolves to an address …” (emphasis mine). As a matter of fact, we can use a tnsnames entry instead of hardcoding the address in LOCAL_LISTENER. To do this, we need to create a special entry in the tnsnames.ora file on the server, then set LOCAL_LISTENER to point to that entry. Unlike the usual tnsnames entry that points to a database service, this entry will point to the listener itself. (By the way, it is often said that the tnsnames.ora file is used only by client processes. This use of tnsames by the database instance is no exception. At this point the instance is acting in the role of a client, just as it does when using a database link to access data on another database.)

Let’s create the tnsnames entry, and test it with tnsping. I’ll add the entry FUBAR for this.

oracle:orcl$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TULSA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tulsa)
    )
  )

FUBAR =
  (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1522))

oracle:orcl$ tnsping fubar

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 10:50:26

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1522))
OK (10 msec)

oracle:orcl$

Fig. 13

In reality I’d want to make the tnsnames alias something more meaningful, but here I wanted to use a name that would very obviously not be some reserved or default “magic” value. Notice that since FUBAR is used to locate the listener itself (rather than the services of a database instance) we do not need to include the CONNECT_DATA section.

Now that we have a tnsnames entry that points specifically to the listener, let’s prove it out.

First, we set LOCAL_LISTENER to reference the tnsnames.ora entry:

SQL> alter system set local_listener='fubar' scope=both;

System altered.

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      fubar
SQL>

Fig. 14

Next, I’ll restart the listener, in order to flush the current registrations and start clean.

oracle:orcl$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 10:53:04

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
The command completed successfully

oracle:orcl$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 10:53:10

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                24-JAN-2016 10:53:10
Uptime                    0 days 0 hr. 0 min. 0 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

oracle:orcl$

Fig. 15

Finally, I’ll force a registration of the instance, then recheck the listener status.

SQL> alter system register;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 10:54:47

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                24-JAN-2016 10:53:10
Uptime                    0 days 0 hr. 1 min. 37 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL>

Fig. 16

Conclusion

I’ve shown how it is possible for the listener to operate without the use of the listener.ora configuration file. I have also shown how the database instance registers itself with the listener with both default and non-default settings, and how the instance uses the LOCAL_LISTENER initialization parameter and the tnsnames.ora file to locate the listener for self-registration.

I would also like to point out that for the purposes of this demo, I never mixed static and dynamic registrations. I did that to make it perfectly clear that the two are separate and distinct and not inter-related. However, I also need to point out that this does not mean that the two are mutually exclusive. It is actually rather normal to have both at the same time. When doing so, the listener will report both:

oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-JAN-2016 15:12:35

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                24-JAN-2016 15:11:49
Uptime                    0 days 0 hr. 0 min. 46 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "tulsa" has 2 instance(s).
  Instance "tulsa", status UNKNOWN, has 1 handler(s) for this service...
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle:tulsa$

Fig. 17

At this point I have covered just about the entire range of TNS configuration items that cause the vast majority of Oracle database connection issues. In previous posts I covered the “configuration chain” from the initial client connection request, through the network routing, through the listener, to the database instance. In this post I have shown the configuration issues that lead to the listener knowing what database instances it is supposed to be able to service. Hopefully, this series will be of help to those faced with the original question, “Why can’t I connect to my database?”

ORA-12514: TNS:listener does not currently know of service

In previous articles I have explored various reasons why a client process might not be able to connect to an Oracle database. In Help! I can’t connect to my database … I presented an overview of the process. I followed that with articles going into more depth on the types of problems that can prevent the request from reaching the host server. Now I would like to look at problems that can arise once the request actually arrives at the listener, in particular “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”.

For this demonstration I will generate the error then go through the standard analysis and solution. After that, I will explore some of the interesting factors that flow from this.

The Error – ORA-12514

Before we get into specifics of debugging ORA-12514, let’s create a test case and mention some general principles.

C:\>sqlplus scott/tiger@tulsa

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 18 15:28:47 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

Fig. 1

Of course the very first thing anyone should do when confronted with an Oracle error is to check the official description. There are many sources on the web, but I like to start with ‘oerr’ utility on the server ..

[oracle@vmlnx01 admin]$ oerr ora 12514
12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a service name for a service (usually a database service)
// that either has not yet dynamically registered with the listener or has
// not been statically configured for the listener.  This may be a temporary
// condition such as after the listener has started, but before the database
// instance has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which services are currently known by the listener by executing:
//    lsnrctl services
//  - Check that the SERVICE_NAME parameter in the connect descriptor of the
//    net service name used specifies a service known by the listener.
//  - If an easy connect naming connect identifier was used, check that
//    the service name specified is a service known by the listener.
//  - Check for an event in the listener.log file.

Fig. 2

The error is pretty self-explanatory: “listener does not currently know of service requested in connect descriptor”. Of course, this could be because the client incorrectly specified the service name for the database, or because the listener really doesn’t know of a service that it should know.

The description returned by oerr also outlines a course of action to resolve the problem. By following this outline, we should come to know where the actual problem lies. Let’s fill in the details on each one in turn.

Wait and try again.

One of the features of Oracle database is the ability to register itself with the listener. This is referred to as “dynamic registration”. The listener does not have to be specifically configured to know about the database instance. The database will contact the listener every several seconds and register itself. The “wait and try again” solution is predicated on the assumption that we simply haven’t waited long enough for the database to register itself. Since the database instance performs dynamic registration every sixty seconds (if not more often), the “wait and try again” solution should only work if the error occurred immediately after database startup.

Check which services are known by the listener

Many people try to answer this question by looking at the listener configuration file, listener.ora. However, this is insufficient for one big reason: dynamic registration is not dependent on any listing in listener.ora. And as a result of that, for most cases it is not even necessary to have a listener.ora file at all.

The one reliable way to tell what instances and services the listener knows about is to query it directly, using the lsnrctl utility.

 
oracle:tulsa$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JAN-2016 15:03:12

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                18-JAN-2016 14:43:59
Uptime                    0 days 0 hr. 19 min. 12 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/diag/tnslsnr/vblnxsrv02/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vblnxsrv02.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "tulsa" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
Service "tulsaXDB" has 1 instance(s).
  Instance "tulsa", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle:tulsa$

Fig. 3

We see in figure 3, above, that the listener knows of two services, “tulsa”, and “tulsaXDB”, both associated with the instance “tulsa”. (The service “tulsaXDB” is for special use and is of no real concern for our present discussion.) Notice that the status is listed as “READY”. This is a definitive indication that the registration is dynamic. By contrast, look at the content of listener.ora:

 
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Fig. 4

Notice in Fig. 4 that there is no mention of any service or SID at all, yet we know that the listener has registered service “tulsa” with SID “tulsa”.

At this point in your problem resolution, you must ask yourself if the listener has registered the database you are expecting. If not, you need to resolve that. If the expected database is not reported by the listener, then ORA-12514 is just an indication of a more fundamental problem, which I will discuss in more depth in a future article. In our current case, we want to connect to “tulsa”, and the listener knows about “tulsa”, so we should continue to the next possible problem.

Check the SERVICE_NAME parameter in the connect descriptor

At this point we know that the listener has a registration for “tulsa”. And didn’t we request a connection to “tulsa” in Fig. 1? Well, no, not really. We specified “tulsa” as the net service name, but that is not necessarily what gets passed in the connect descriptor.

The net service name in our connection request (see Fig. 1, line 1) is nothing more than an alias which gets resolved to the final connect descriptor. As described in my previous article Troubleshooting ORA-12154, there are several methods of resolving a net service name to a connect descriptor. In our case, we are relying on resolution via tnsnames.ora, so our request for “tulsa” was located in tnsnames, and information found there was used to construct the final connect descriptor.

Our tnsnames.ora entry for “tulsa” looks like this:

TULSA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = fubar)
    )
  )

Fig. 5

At Fig. 5, line 7, we see that the actual service name specified is “fubar”. Normally, one might expect the SERVICE_NAME specification to be the same as the alias, but this cannot be taken for granted and must be checked. If they do not match, you must ask yourself, “why?” There may be legitimate reasons, or it could be a simple typographical error, or it could be that the person who set it up really didn’t understand what they were doing. In the above case I deliberately made a mismatch for the express purpose of creating an error and making a point.

Of course, we could be looking at the wrong tnsnames.ora. Let’s run this to ground a bit more thoroughly.

First, let’s try a tnsping and see if the reported connect descriptor matches what we saw in our tnsnames.ora

C:\>tnsping tulsa

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-JAN-2
016 15:57:51

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vblnxsrv02)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fubar)))
OK (30 msec)

C:\>

Fig. 6

At line 14, we see the same bogus SERVICE_NAME = fubar, pretty much confirming that we were looking at the correct tnsnames.ora.

As one final nail in the coffin, we can look at the listener log on the database server. The plain text version of this log will be located at $ORACLE_BASE/diag/tnslsnr/<hostname>/listener/trace/listener.log. Looking at my log, I find the following entry:

18-JAN-2016 15:24:20 * (CONNECT_DATA=(SERVICE_NAME=fubar)(CID=(PROGRAM=C:\app\oracle\product\11.2.0\client_1\bin\sqlplus.exe)(HOST=STEVENS-NB-04)(USER=ed))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.1)(PORT=64942)) * establish * fubar * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

Fig. 7

You may have to scroll the above display to the right, but you can clearly see in line 1 that the listener received a request to connect to “SERVICE_NAME=fubar”. The last item on the line logging the request is the return code, which in this case is “12514”. This line is followed immediately by a line explicitly reporting the 12514. At this level, it is reported as a TNS error instead of an ORA error but that distinction is merely a matter of where, within the call stack, the error is being reported. It is still the same error.

The fix

At this point it should be obvious that the root problem is an incorrect specification of SERVICE_NAME in our tnsnames.ora file. Let’s fix that:

TULSA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv02)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = tulsa)
    )
  )

Fig. 8

And prove our fix

C:\>sqlplus scott/tiger@tulsa

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 18 16:22:34 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL>

Fig. 9

Conclusion

In this article I have demonstrated the connection between what the client requests and what the listener expects. The next logical issue was mentioned in passing when we looked at the services registered with the listener. In my next article I will discuss the methods by which a database becomes registered with the listener.

Film at eleven …

tnsping – what it is, what it isn’t

(This is a revised version of an article I originally published at edstevensdba.wordpress.com. At that time I was using Oracle 10g client on Windows XP. While we have since moved on to newer versions of Oracle and Windows, the demonstrations using 10g are equally applicable to 11g and 12c installations.)

Exploring the use of tnsping

Before continuing our exploration of various tns connection errors, let’s take a quick look at the oracle utility ‘tnsping’. We’ll see what it does, what it doesn’t do, and explode a few myths along the way.

The tnsping utility is used to determine if a listener on an Oracle Net network can be reached. A complete description of its use is found in the Net Services Administrators Guide, located with the rest of the Oracle documentation at docs.oracle.com. TNSPING serves, for sqlnet, much the same purpose as does ‘ping’ for the underlying network stack.

Let’s take a look at a simple case. Given this entry in my tnsnames.ora . . .

larry =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vlnxora1)
    )
  )

Figure 1

. . .let’s run some tests.

C:\>tnsping larry 

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2 
011 16:21:22 
Copyright (c) 1997, 2007, Oracle. All rights reserved. 

Used parameter files: 
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora 

Used TNSNAMES adapter to resolve the alias 
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) 
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1))) 
OK (20 msec) 

Figure 2

And we think, “Aha!” I can connect! Well, maybe. Maybe not. I’ll get to that later. First, let’s look at what we can learn from what we see here.

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 14:46:56

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (20 msec)

Figure 3

The first thing we learn, from line 3, is that we are running the 32-bit version of Oracle client 10.2.0.4. Note that this is the version of the client, not the database. They do not have to be the same, and this tells us only about the client.

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 14:46:56

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (20 msec)

Figure 4

From lines 8-9, we see that the network parameter file (sqlnet.ora) used by this client is at C:\oracle\product\10.2.0\client_1\network\admin. This is also a very strong indication that the tnsnames.ora file is also located in that directory. However, there are other influences on the location of tnsnames.ora and on resolving a net service name. See my post on that subject, Locating Oracle’s Network Configuration Files.

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 14:46:56

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (20 msec)

Figure 5

From line 11, we see that we are using the TNSNAMES adapter to resolve the alias, thus we did reference tnsnames.ora. (The alias – or net service name – was ‘larry’). This adapter was chosen based on the value of the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file.

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 14:46:56

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (20 msec)

Figure 6

Lines 12 and 13 show us how sqlnet resolved the alias, or “connect identifier”, into a connect descriptor. We see that the request was routed using the tcp network protocol (PROTOCOL = TCP), to server vlmnx01 (HOST = vmlnx01), and placed at that server on port 1521 (PORT = 1521). This information was gathered from the tnsnames.ora entry for ‘larry’, shown in Figure 1, above.

And from line 14 we see that the response time was 20 milliseconds. Well, not quite. According to the Net Administrators Guide, tnsping “displays an estimate of the round trip time” (emphasis mine).

We also see that it connected to (SERVICE_NAME = vlnxora1). Or did it? We know that network connection requests are handled by the listener, so let’s see what the listener says about service name vlnxora1.

[oracle@vmlnx01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-FEB-2011 15:27:35

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                27-FEB-2011 15:27:26
Uptime                    0 days 0 hr. 0 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@vmlnx01 admin]$

Figure 7

Hmm. Line 20 says “The listener supports no services”. How can this be? Wasn’t our tnsping successful? What does a real connection request do?

C:\>sqlplus scott/tiger@vlnxora1

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Feb 27 15:32:18 2011

Copyright (c) 1982, 2007, Oracle All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

So a tnsping of ‘vlnxora1’ is successful, but an actual connection to the database returns ORA-12514. Of course the listener “does not currently know of service requested”. At the moment, the listener does not know of any services at all. By the way, I made sure the listener did not know of any services. My listener relies on dynamic database registration, and I had stopped the database before starting this test. Not only does the listener not know of any services, there is no database running at all. I could just as easily specified SERVICE_NAME=FUBAR, or SERVICE_NAME=BTZLFLX and received the same result.

And if the listener itself is not running? Let’s stop the listener . . .

[oracle@vmlnx01 ~]$ lsnrctl stop 

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-FEB-2011 15:40:41 

Copyright (c) 1991, 2007, Oracle. All rights reserved. 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) 
The command completed successfully 

[oracle@vmlnx01 ~]$ lsnrctl status 

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-FEB-2011 15:40:50 

Copyright (c) 1991, 2007, Oracle. All rights reserved. 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener 
   Linux Error: 111: Connection refused 
[oracle@vmlnx01 ~]$ 

Figure 8

. . .then test tnsping again

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 15:42:27

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
TNS-12541: TNS:no listener

Figure 9

As we would expect, tnsping reports that there is no listener.

Conclusion

While tnsping is a very useful tool for diagnosing a variety of connection problems, we must be very clear about one important point: It tests only the network name resolution and routing between the client and the listener. It will tell us about the version of client being used. It will tell us the location of the client’s tns configuration files. It will tell us the final connect string derived from our net service name. It will tell us if the request can be routed to the specified host. It will tell us if there is a listener on the specified port at the specified host. But it will not tell us anything at all about the status of any database. This is not a shortcoming of tnsping. We just need to understand that it is a tool for diagnosing Oracle networking issues, and and the network is not the database.

Postscript

After posting the original version of this article on my old site, Joel Garry brought my attention to some very interesting related information. See this article, by Laurent Schneider.