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.

Please like us:

4 Comments


  1. // Reply

    Ed – thanks again! This just solved another issue that I was having as I tested different types of connections to my database. From now on I’m going to start my search here instead of Google!


    1. // Reply

      I’m always glad to hear that my articles are actually helping people solve their problems. Please sign up to follow me by email or social media. I’m putting the finishing touches on an article about the Oracle Transparent Gateway and hope to have it out in a few days.


  2. // Reply

    TWO_TASK is usefull when you are working from a different server from your database.


    1. // Reply

      “TWO_TASK is usefull when you are working from a different server from your database.”

      Well, that is what it is used for. But I think that the actual usefulness of it is debatable.

      Which is more useful?
      This:

      export TWO_TASK=mydb
      sqlplus scott/tiger

      or this:

      sqlplus scott/tiger@mydb

      They both do exactly the same thing.

Leave a Reply

Your email address will not be published. Required fields are marked *