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.
Ed Stevens, DBA 