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.

Oracle Inventory Report

One of the challenges when taking a new position as an Oracle DBA is getting a good survey of the landscape for which you are responsible. Especially in smaller shops with only one Oracle DBA there is often a dearth of reliable documentation – or any documentation at all! An initial look at a server may reveal multiple ORACLE_HOMEs, multiple databases in the same or multiple homes, separate client-only homes … you name it. Anything you can imagine, and perhaps even some things you would never imagine, all done by your predecessor.

Faced with such a situation, I needed to discover the following:

1. What ORACLE_HOMEs had been created
2. What software and version was installed in each ORACLE_HOME
3. Which ORACLE_HOMEs were being used
4. Which ORACLE_HOMEs were not being used and could be safely removed.

Given all the various ways a, um, “creative” DBA can “go off the reservation” I’m not sure there is a 100% foolproof way to discover all the possible installations and relationships. However, if one starts with the assumption that the previous DBA was not deliberately devious but merely inconsistent in placing and naming his ORACLE_HOMEs, I believe that by combining information in the central inventory file with the contents of the oratab file, one can produce a reliable survey. After several partial solutions the light finally came on and I wrote the script presented here.

The end result will be a report that looks like this:

=====================================================
HOST: vblnxsrv02.localdomain
Report time: Fri Feb 26 16:23:26 CST 2016

HOME NAME: OraDB12Home1 IDX: 1

------------------------- oratab usage --------------------------
tulsa:/u01/app/oracle/product/12.1.0/dbhome_1:N
oklacity:/u01/app/oracle/product/12.1.0/dbhome_1:N
-----------------------------------------------------------------
ownership of /u01/app/oracle/product/12.1.0/dbhome_1 is
drwxr-xr-x. 70 oracle oinstall 4096 Feb 22 00:28 dbhome_1
-----------------------------------------------------------------
Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2016-02-26_16-23-29PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2016-02-26_16-23-29PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------

OPatch succeeded.

Listing 1.

Line 2 gives the name of the host server being reported on.

Line 3 gives the time the report was created

Line 5 gives the reference name of the ORACLE_HOME, as recorded in the central inventory.

Lines 8-9 show the oratab entries that reference this ORACLE_HOME.

Line 12 shows the Linux ownership profile of this ORACLE_HOME.

Line 14 begins an opatch lsinventory report on this ORACLE_HOME.

If the host has multiple ORACLE_HOMEs, the above would be repeated for each home.

In keeping with the core “mission” of this blog, I don’t want to just give you code and say “here, use this”. Rather I want to educate on how and why things work the way they do. So in this article I will detail how I wrote the shell script that generates this report and explain its inner workings. Hopefully you will learn some things about shell scripting and about how the Oracle inventory is put together.

Assumptions and Methodology

In developing this script, I assumed a global inventory which is identified by the file /etc/oraInst.loc. (I believe that on Solaris, this would be /var/opt/oraInst.loc). This file serves to point to the actual Oracle inventory location, which will enumerate all the installed ORACLE_HOME directories. I also assumed that the oratab file (/etc/oratab on most *nix systems, /var/opt/oratab on Solaris) enumerates all of the installed databases and ASM homes. I am fully aware that there is nothing within Oracle that forces the oratab file to be devoid of either sins of commission or sins of omission. That is, it is quite possible for oratab to NOT list an existing, running database. It is equally possible for it to have entries for databases that do not exist at all. By combining information found in the central inventory with information found in oratab, we are able to produce a report that gathers all all of the key information about every ORACLE_HOME on the server.

The process

Creating the Oracle Installation Inventory

Step One – Locate the Oracle Inventory

The first thing we need to do is locate the Oracle central inventory. You might think that it is always located in the same place, and most of the time you might be correct. But we can do better than that. The file /etc/oraInst.loc contains the pointer to the inventory, wherever it may have been placed. The oraInst.loc file itself looks like this:

oracle:tulsa$ cat /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

Listing 2.

In reality, the inventory is actually a set of sub-directories and files that are located starting with the directory indicated by the entry “inventory_loc”. Starting from that point, the file “../ContentsXML/inventory.xml” contains the information we need. On my test machine, that file looks like this:


<!-- Copyright (c) 1999, 2011, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->

11.2.0.3.0
2.1.0.6.0

Listing 3.

(The sharp-eyed reader may begin to notice some discrepancies between the inventory shown in Listing 3 and that used in the rest of the article. This was the inventory I worked with when I first wrote this presentation, but was not available to me on an existing system when editing for this article. I include it here because it presents some of the complexities that could exist and had to be accounted for.)

Since we know that the file “oraInst.loc” contains a pointer to the directory containing the Oracle installation inventory, and we know the name of the actual inventory file within that designated location, we can combine the use of ‘grep’ and ‘cut’ to extract the location from oraInst.loc and assign it to an environment variable. That variable can then be used as a fully qualified name to the specific inventory file we need. Armed with this knowledge, let’s start building our script

#!/bin/sh
runlog=$(basename $0)_$(date +%Y%m%d_%H%M).log
orainstloc=$(grep inventory_loc /etc/oraInst.loc | \
cut -d "=" -f2)/ContentsXML/inventory.xml

Listing 4.

Line 1 simply directs that the script will be processed by the shell command processor ‘sh’. This will override any other settings that might have caused it to be processed by other shells, such as bash, ksh, etc. I put it at the beginning of all of my scripts so that I can be sure which shell will be used, and thus know which variant of the shell scripting language to use.

Line 2 establishes the name of a log file and assigns it to a variable to be used later. The value is constructed of three components. The assignment ‘$(basename $0)’ returns the name of the script itself. The assignment ‘$(date +%Y%m%d_%H%M) returns the current date and time in yyyymmdd_hhmi format. The third component is the literal ‘.log’. Again, this is a line I use in all of my shell scripts to generate a name for a log file without having to hard-code it for every script.

Lines 3 and 4 are actually a multi-step process that we need to deconstruct. While we show two lines of code this is actually one command, continued across two lines simply to make it easier to read and understand. The backslash at the end of line 3 is a continuation character, telling the shell processor that the command is continued on the next line.

Notice that, overall, the command is assigning some value to the environment variable “orainstloc”. Notice further that what is being assigned is actually two strings concatenated together. The first string is whatever is returned by the commands enclosed in the ‘$()’ assignment: $(grep inventory_loc /etc/oraInst.loc |cut -d “=” -f2) and the second is the literal ‘/ContentsXML/inventory.xml’.

Digging further, the command returning the first part of the string, enclosed by $(), is itself actually using two commands to return a value. The ‘grep’ command reads the file /etc/oraInst.loc and returns the entire entire line that contains the string ‘inventory_loc’. As we saw in Listing 2, line 2, that is the string:

inventory_loc=/u01/app/oraInventory

That string is then piped to the ‘cut’ command, where we define the equal sign as a field delimiter, (-d “=”) and extract the second field (-f2). That will return the value

/u01/app/oraInventory

If we were to execute just the above statement at a command prompt then show the value assigned we see this (note that the “>” at the beginning of line 2 is the shell continuation prompt, not part of the command itself):

oracle:tulsa$ orainstloc=$(grep inventory_loc /etc/oraInst.loc | \
> cut -d "=" -f2)/ContentsXML/inventory.xml

oracle:tulsa$ echo $orainstloc
/u01/app/oraInventory/ContentsXML/inventory.xml

Listing 5.

So at this point, we have determined the fully qualified name of the inventory file and assigned that to an environment variable, “orainstloc”. The only hard-coded assumption is the location of the oraInst.loc file.

Step Two – Parse the Oracle Inventory

Now that we have determined the location of the inventory file, we need to extract from it the locations of every ORACLE_HOME.

We are only interested in information from the lines in the <HOME_LIST> section. Notice in Listing 3 lines 10-12, that there are a total of three separate ORACLE_HOMEs listed. Further, if you scroll to the right you will see that one of them is tagged as having been removed (REMOVED=”T”). We can get the information we need by using grep to select only the lines that begin with ‘HOME_NAME’, and from that list remove the lines that include ‘REMOVED’. We accomplish that with the next line we add to our script:

#!/bin/sh
runlog=$(basename $0)_$(date +%Y%m%d_%H%M).log
orainstloc=$(grep inventory_loc /etc/oraInst.loc | \
cut -d "=" -f2)/ContentsXML/inventory.xml
grep "HOME NAME=" $orainstloc |grep -v "REMOVED"

Listing 6.

At line 5, ‘grep’ is used to read the file indicated by the environment variable ‘orainstloc’ (which we had assigned in the previous command) and extract only those lines that contain the string ‘HOME NAME=’. That list is piped to a second grep with the ‘-v’ switch, which tells grep to return every line that does not contain the specified string, ‘REMOVED’.

After completing the above step the final ‘grep returns the following


Listing 7.

Notice that I did not actually capture that output by redirecting it to a file. Instead, I am going to just pipe that output to the next step.

Step 3 – Create a Script to Write the Report

Now that we have captured the names and locations of all of the installed ORACLE_HOMEs, we can leverage the power of the ‘awk’ utility to use that information as the basis for creating a second script that will actually create our report on each ORACLE_HOME.

This step is rather lengthy, but it is worth following in detail.

Awk searches one or more files (or other input stream) for records that match the specified patterns. When a match is found it performs specified actions using the matching records. In our usage here, we provide no matching pattern, so it processes every record in the input stream. To do that, my script now looks like this:

#!/bin/sh
runlog=$(basename $0)_$(date +%Y%m%d_%H%M).log
orainstloc=$(grep inventory_loc /etc/oraInst.loc | \
cut -d "=" -f2)/ContentsXML/inventory.xml
grep "HOME NAME=" $orainstloc |grep -v "REMOVED" | \
awk -F\" '{print \
  "echo ==================================================" RS \
  "echo HOST: $(hostname)" RS \
  "echo Report time: $(date)" RS \
  "echo " RS \
  "echo HOME NAME: " $2 "   IDX: " $8 RS \
  "echo "  RS \
  "export ORACLE_HOME=" $4 RS \
  "echo ------------------ oratab usage ------------------" RS \
  "grep $ORACLE_HOME: /etc/oratab" RS \
  "echo --------------------------------------------------" RS \
  "echo ownership of " $4 " is " RS \
  "export mybase=$(basename $ORACLE_HOME)" RS \
  "ls -l " $4"/.. |grep -v total|grep $mybase" RS \
  "echo --------------------------------------------------" RS \
  "$ORACLE_HOME/OPatch/opatch lsinventory"}'
echo Report file is at $runlog
exit

Listing 8.

It is very important to keep in mind that here we are using a script to write a script. We must pay close attention to what commands are used to create the second script, and what commands are becoming that second script, to be executed at a later step.

On line 5 I have added a pipe to accept the output of the grep commands, and a line continuation. Using the pipe, the output of the final ‘grep’ on line 5 becomes the input stream for the ‘awk’ command on line 6. With the line continuations, lines 5 through 20 become one lengthy, complex command.

The back-slash at the end of each line is a continuation mark, to tell the shell processor that this command is continued on the following line. This separation of lines is only for our own sanity in reading the awk script. Awk itself never sees these breaks.

awk -F\" '{print \

Line 6 specifies the ‘awk’ command itself. The -F switch indicates that we are providing a character to be used as a field delimiter when processing the records of the input stream. In our case we want the field delimiter to be a double-quote mark. Since the double-quote itself has special meaning to the shell processor, we have to ‘escape’ it with the backslash to tell the shell processor to treat it as any other character. The remainder of line 6 begins the actual command sequence to awk, telling it to print whatever follows.

Lines 7 through 21 indicate what awk is to print (write) to the output stream. Since we are using awk to create a secondary shell script, “what” we are printing is a series of shell commands. For convenience, I have made a separate line in my awk script for each line of the output stream. In order to actually create separate lines (separate commands) in the script being created, each ends with the awk built-in variable RS. This indicates to include a record separator in the output stream, so whatever follows the RS will be on a new line in the output. Scanning lines 7 through 21 you can easily see the script that we are building.

My detail description of lines 7 through 21 will address that line in the context of its role in the script being created by awk.

  "echo ==================================================" RS \

Line 7 will ‘echo’ a visual break before each section in the final report. A ‘section’ is all of the information being reported on a given ORACLE_HOME, as listed in the inventory.

  "echo HOST: $(hostname)" RS \

Line 8 will report the value of whatever is returned by the command ‘hostname’.

  "echo Report time: $(date)" RS \

Line 9 will write a command to report the current date and time

  "echo " RS \

Line 10 will echo a blank line.

  "echo HOME NAME: " $2 "   IDX: " $8 RS \

In Line 11 awk will include the values of fields 2 and 8 (of the input record that was piped from grep on line 5) when it writes the ‘echo’ command. A close examination of the input file shows field 2 to be the name of the ORACLE_HOME being processed. Note that this is just a reference name, not the actual ORACLE_HOME directory specification. Field 8 is the index for this ORACLE_HOME within the inventory.

  "echo "  RS \

Line 12 will echo a blank line in the final report.

  "export ORACLE_HOME=" $4 RS \

In line 13 awk will include the value of field 4 from our input record when it writes the ‘export’ command. A close examination of the input file shows field 4 to be the actual ORACLE_HOME directory specification.

  "echo ------------------ oratab usage ------------------" RS \
  "grep $ORACLE_HOME: /etc/oratab" RS \
  "echo --------------------------------------------------" RS \

Lines 14 and 16 write some visual separators to enclose the output of line 15.

Line 15 writes a ‘grep’ command, to print the lines from /etc/oratab that relate to the ORACLE_HOME being processed.

  "echo ownership of " $4 " is " RS \

Line 17 again uses field 4 of our input record, along with the string literal ‘ownership of ‘.

  "export mybase=$(basename $ORACLE_HOME)" RS \

Line 18 sets an environment variable, ‘mybase’ to the value of the ORACLE_HOME directory, stripped of its parent directories. This is then used in the next line of the script being written.

  "ls -l " $4"/.. |grep -v total|grep $mybase" RS \

Line 19 produces a listing of the directory that is the immediate parent of $ORACLE_HOME, stripped of all the directories except $ORACLE_HOME. The final result shows the access profile of $ORACLE_HOME.

  "echo --------------------------------------------------" RS \

Line 20 produces another visual break in the final report.

  "$ORACLE_HOME/OPatch/opatch lsinventory"}'

Line 21 executes the oracle ‘lsinventory’ utility to report the full inventory information for the ORACLE_HOME being processed.

So at this point our script has dynamically created created a set of shell commands that themselves will create our report. If we were to capture those commands in a file (which we did not do), the file would look something like this, for an installation with a single ORACLE_HOME:

echo =====================================================
echo HOST: $(hostname)
echo Report time: $(date)
echo
echo HOME NAME: OraDB12Home1   IDX: 1
echo
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
echo ------------------------- oratab usage --------------------------
grep $ORACLE_HOME: /etc/oratab
echo -----------------------------------------------------------------
echo ownership of /u01/app/oracle/product/12.1.0/dbhome_1 is
export mybase=$(basename $ORACLE_HOME)
ls -l /u01/app/oracle/product/12.1.0/dbhome_1/.. |grep -v total|grep $mybase
echo -----------------------------------------------------------------
$ORACLE_HOME/OPatch/opatch lsinventory

Listing 9.

Step 4 – Execute the Commands to Write the Report

In Listing 8 I showed how to create use awk to create a set of commands, using informatin found in the records that we extracted from the inventory file. As mentioned, we did not actually write those generated commands to a file and did nothing to execute them. In an earlier edition of this script, I redirected the output of awk to a file, then executed that file. That presented a few other problems for a later enhancement, so I replaced the redirection to a file with a pipe to ‘sh’, the shell command processor. With that, along with a couple of other changes that went along with it, my complete script looks like this:

#!/bin/sh
runlog=$(basename $0)_$(date +%Y%m%d_%H%M).log
orainstloc=$(grep inventory_loc /etc/oraInst.loc | \
cut -d "=" -f2)/ContentsXML/inventory.xml
grep "HOME NAME=" $orainstloc |grep -v "REMOVED" | \
awk -F\" '{print \
  "echo ==================================================" RS \
  "echo HOST: $(hostname)" RS \
  "echo Report time: $(date)" RS \
  "echo " RS \
  "echo HOME NAME: " $2 "   IDX: " $8 RS \
  "echo "  RS \
  "export ORACLE_HOME=" $4 RS \
  "echo ------------------ oratab usage ------------------" RS \
  "grep $ORACLE_HOME: /etc/oratab" RS \
  "echo --------------------------------------------------" RS \
  "echo ownership of " $4 " is " RS \
  "export mybase=$(basename $ORACLE_HOME)" RS \
  "ls -l " $4"/.. |grep -v total|grep $mybase" RS \
  "echo --------------------------------------------------" RS \
  "$ORACLE_HOME/OPatch/opatch lsinventory"}' | sh |tee -a $runlog
echo Report file is at $runlog
exit

Listing 10.

At line 21 I have added a pipe to send the output of the awk command (that began on line 6) to the command processor ‘sh’. This allows us to create the command script without actually writing it to a file. I have also added a pipe to the ‘tee’ command so that as the script is processed by sh, the output goes both to stdout (the screen) and to a file. The name of the file is indicated by the value of the variable ‘runlog’, which we had set on line 2.

On line 22, I simply report the name of the log file that was created on line 21.

Conclusion

I trust others might find this script useful. I’m quite certain that those more fluent in shell scripting than I (particularly in the use of awk) will find some of my coding to be rather primitive and can offer a more elegant solution based on this work. I would be more than happy to see any substantive improvements.

I am going to start working on a functional equivalent for Windows installations. Given the limitations of the Windows command processor and the complexity of the Windows registry, it will be rather more challenging.

If you have any observations or questions, please feel free to add a comment.

Configure the VirtualBox Network

In “Create a private Oracle test lab” I made the case that there is no excuse for someone who considers themselves to be a professional DBA to not have their own personal database for testing and learning. I pointed out that by using Oracle’s VirtualBox product, Oracle Linux, and Oracle database under the terms of the developer license agreement, one could create their own private test system legally and at no cost. In this article I will detail how to configure the virtual machine network components so that you can deal with them in exactly the same manner as a live server in your organization’s data center.

When I first started working with VirtuaBox the first problem I ran into was dealing with the network configuration and the differences between VirtualBox and VMware, which I had been using previously.

I am not a networking expert and it is not my intent to give a detailed comparison between the way it is handled in VMware vs. VirtualBox. Rather, I simply want to lay out what I discovered I needed to do to get my virtual servers running under VirtualBox to network according to my requirements.

My requirements for the virtual machine network

When I create a vm on my desktop, have four fundamental, non-negotiable requirements:

  1. I must be able to work with, access, and manage the VM exactly as I would any real server in my data center. That means it has to be accessible from my desktop OS using exactly the same tools I use with my live database servers: putty for my ssh client, sqlplus, and any GUI database access tool like Toad, SQL-Navigator, SQL Developer, etc.
  2. The VM must be able to access the internet to download OS packages from Oracle’s public yum server.
  3. The VM must have a fixed IP address. You really can’t run a server (and that’s what this vm is) without a known, fixed IP address.
  4. The VM must be invisible to my network administrators. It cannot occupy an IP address on my company’s or ISP’s network. The “network nazis” must never know it’s there. It’s not that I’m hiding what I’m doing, but rather that I want that level of isolation to protect the wider network from anything I might do.

Terminology

Before continuing, let me define some of the acronyms and abbreviations I will be using.

VM – when I use the term “VM” (or “vm”) I am simply using it as an abbreviation for the term “virtual machine”. I do not use it to refer to any product from VMware or the VMware corporation itself. Also, I use the term “VMs” as the plural of “VM”. This is not to be confused with Digital Equipment Corporation’s ‘vms’ operating system. A lot of people would avoid this ambiguity by using “vm’s” as the plural of “vm”, but I was too well schooled in English grammar to use a possessive as a plural.

VBox – I use the term “VBox” as common shorthand for “VirtualBox”. It is quite common on Vbox message boards to refer to it as simply “VB”. That is fine within the context of those forums, but in a broader sense I fear it may be confused with Microsoft’s Visual Basic, which is also widely referred to as “VB”.

Network Modes

Before detailing my virtual machine network configuration I need to explain the different network modes in use with a virtualization product. These are specified as a property of the virtual NIC on the vm, before any operating system is installed and configured.

NAT (Network Address Translation)

With NAT, the guest (virtual) OS has it’s own IP address, but communicates to the outside world thorough the host OS’s IP address. Requests are translated from the guest IP address to the host’s address before the host passes the request on. Messages received back are translated back to the guest OS’s IP address for the final leg of the trip. Honestly, I studied the details once, but have since forgotten them.

Bridged

Using a bridged adapter, the VM has an IP address that actually occupies a space on the host’s network. As such it communicates with the network under its own credentials and is visible (and controllable) by the network administrators.

Host Only

With a host only adapter, the vm can communicate only with the host OS or other vm’s running on the same hostonly adapter. This provides complete isolation of that NIC from anything outside of the host machine.

Setup the Virtual Machine

With VMware, meeting my four requirements for configuring the virtual machine network was so simple I really just took it for granted and never gave it a thought. When you install VMware on your desktop two network adapters are created. VMnet1 is configured for ‘hostonly’ connections. VMnet8 is configured for NAT. I knew that I wanted to hide behind NAT, so gave my first vm an IP address in the subnet controlled by VMNet8 and everything “just worked”. I never gave it another though.

When I started to use VBox I immediately ran into problems. After a lot of trial and error and, um, “animated” discussions on the VBox user’s forum, I found that VBox implemented it’s networking entirely differently than VMware, and that impacted how the different configurations like NAT behaved.

So, without further ado, let’s get into how I set up my virtual machines running under VBox. I am running VirtualBox on a Windows 7 Home Premium laptop, 64-bit.

Install VirtualBox

The installation of Vbox itself will create a network adapter on the host OS. This can be seen by opening a command prompt and executing the ‘ipconfig’ command:

C:\>ipconfig

Windows IP Configuration


Wireless LAN adapter Wireless Network Connection 2:

   Media State . . . . . . . . . . . : Media disconnected
   Connection-specific DNS Suffix  . :

Ethernet adapter Bluetooth Network Connection:

   Media State . . . . . . . . . . . : Media disconnected
   Connection-specific DNS Suffix  . :

Wireless LAN adapter Wireless Network Connection:

   Connection-specific DNS Suffix  . :
   Link-local IPv6 Address . . . . . : ****::****:****:****:1232%12
   IPv4 Address. . . . . . . . . . . : ***.***.1.3
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . : ***.***.1.1

Ethernet adapter Local Area Connection:

   Media State . . . . . . . . . . . : Media disconnected
   Connection-specific DNS Suffix  . :

Ethernet adapter VirtualBox Host-Only Network:

   Connection-specific DNS Suffix  . :
   Link-local IPv6 Address . . . . . : fe80::70a3:748a:95b4:917a%30
   IPv4 Address. . . . . . . . . . . : 192.168.56.1
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . :

Fig. 1

Reading this from the top down, we see the following

Line 16 , “Wireless LAN Adapter Wireless Network Connection” lists the information assigned to the host OS (Windows 7) by the network to which it is currently connected. This is a DHCP assigned address and at the time of this snapshot, my primary connection was to my ISP (key details are masked with asterisks).

Line 29. “Ethernet adapter VirtualBox Host-Only Network:”. This is the adapter created by the installation of Vbox. Please note the IPv4 address of 192.168.56.1. at line 33. This will be important later on.

I will leave the details of exactly how this adapter functions to people who are more qualified than I. As explained to me, I simply think of it as being the routers to which my vms are connected. The adapter also acts as a DHCP server, assigning IP addresses to any NIC that is so configured.

Creating the virtual machine

At this point, we have simply installed the Vbox product. Doing so has also created the network environment in which our VMs will operate. They will all exist on the same subnet as our Ethernet adapter VirtualBox Host-Only Network – that is 192.168.56.

Now comes the ‘tricky’ part. As mentioned above, with VMware I simply configured my vm with a fixed IP in the same subnet as the VMware NAT adapter – the Vmnet8 adapter, which was at 192.168.111.1. What I found with Vbox was that the NAT configuration requires a DHCP address and will not accept a fixed, manually assigned address. The solution to my four fundamental requirements was to create my vm with two virtual Network Interface Cards (NIC). The first would be configured for DHCP for NAT connections, the second with a fixed IP for hostonly connections. Here’s how I did it.

In this example, I have just created a new virtual machine, named ‘vblnxsrv99’. I have not yet installed an operating system. We can think of ‘vblnxsrv99’ as a physical server that we have just unpacked and set on the workbench in the data center.

vboxnet01

Fig. 2

Now let’s open the network configuration for this machine. Click on the “Network” link in the configuration area:

vboxnet02

Fig. 3

By default, Adapter 1 will already be enabled and configured for NAT. We can leave that one alone.

Configuring virtual machine network adapter 1

Fig. 4

Now we need to add a second Network Interface Card (NIC) to this virtual machine. This is the virtual equivalent of opening the server and plugging in another network card.

Click on the “Adapter 2” tab to bring up the configuration for this second NIC:

Selecting virtual machine network adapter 2

Fig. 5

Check the “Enable Network Adapter” box, and select “Host-only adapter” from the drop-down list. The “Name” will automatically be filled.

Configure virtual machine network adapter 2

Fig. 7

Click “OK” and your machine is ready to have an operating system installed.

Configuring the Guest Operating System

Now that we have a virtual machine prepared with the NICs, we will need to properly configure the OS we are going to install. We can now treat this virtual server just as if it were a physical server in our data center, mounted in its rack and connected to the router.

I will show the OS configuration for Oracle Linux 6, bypassing the details of the full installation and focusing only on the network configuration done during the installation process. Please understand that this is just a convenience during the OS installation. Just as on a physical machine you can add, remove, and reconfigure NIC’s and other networking configurations after the OS is installed and initially configured, these same operations could also be done after the fact on this vm.

During the installation of Oracle Linux you will arrive at the “Network Configuration” dialog. At this point supply your host name (replacing the default of ‘localhost’) and select “Configure Network”.

Preparing to configure the virtual machine network

Fig. 8

Select eth0 and “edit”.

Prepare to configure virtual machine network adapter eth0

Fig. 9

Under the IPv4 Settings, select “Connect automatically” and for “Method” select “Automatic (DHCP), which is the default.

Configure virtual machine network adapter eth0

Fig. 10

Back at Network Configuration, select adapter eth1 and “Edit”

Prepare to configure virtual machine network adapter eth1

Fig. 11

Under the IPv4 Settings tab, select “Connect Automatically”. For the “method”, select “manual”. Click to “add” an address and supply the IP address you wish this server to have. Supply a netmask of ‘255.255.255.0’ and you’re done.

configure virtual machine network adapter eth1

Fig. 12

IMPORTANT: please note that the IP address assigned must be in the same subnet as the VirtualBox Host-Only ethernet adapter that we noted on the host OS. In this example that adapter is at 192.168.56.1. I have given this server an IP address of 192.168.56.99

With both NICs configured to the OS, we can accept the settings and continue with the rest of the operating system installation.

Once the installation of the OS is complete, we can check the network configuration of the vm’s OS. At this point I can already start using my standard desktop tools to connect to the vm. I use putty as my ssh client to establish a connection, using the fixed IP we assigned the server. In this case that is 192.168.56.99

Once connected, let’s check the configuration with the ‘ifconfig’ command:

[root@vblnxsrv99 ~]# ifconfig
eth0      Link encap:Ethernet  HWaddr 08:00:27:D0:4B:2F
          inet addr:10.0.2.15  Bcast:10.0.2.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fed0:4b2f/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:15 errors:0 dropped:0 overruns:0 frame:0
          TX packets:27 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:2834 (2.7 KiB)  TX bytes:2626 (2.5 KiB)

eth1      Link encap:Ethernet  HWaddr 08:00:27:27:77:8C
          inet addr:192.168.56.99  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe27:778c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:4264 errors:0 dropped:0 overruns:0 frame:0
          TX packets:148 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:416226 (406.4 KiB)  TX bytes:20768 (20.2 KiB)

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame:0
          TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:0 (0.0 b)  TX bytes:0 (0.0 b)

[root@vblnxsrv99 ~]#

Fig. 13

Remember that we configured eth0 to the NAT adapter and using DHCP. The IP address you see at line 3 is the DHCP assigned address of 10.0.2.15. While it is true that in this limited environment that will probably always be the address assigned, as a matter of practice you can never count on what address will be assigned by DHCP, so this is not the address we use to connect to the server. We have it only to support the NAT adapter so that we can reach the internet to download packages.

The NIC we are interested in is eth1, which we see at line 11, with the IP address we assigned to it (192.168.56.99) shown at line 12.

We have one bit housekeeping left. For reasons I don’t understand, when Oracle Linux installation (5.x versions) creates the local hosts file /etc/hosts, it assigns the host name to the local loopback address. We can see this here:

[root@vblnxsrv99 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1  vblnxsrv99.vbdomain vblnxsrv99 localhost.localdomain localhost
::1  localhost6.localdomain6 localhost6
[root@vblnxsrv99 ~]#

Fig. 14

And in Oracle Linux 6, it doesn’t include an entry for the server name at all:

[root@vblnxsrv99 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@vblnxsrv99 ~]#

Fig. 15

We want to associate the server name with the fixed IP address we assigned, so modify the hosts file by adding a line for the fixed IP address, and reassigning the host name to that address (this from Oracle Linux 6):

[root@vblnxsrv99 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.56.99  vblnxsrv99.localdomain vblnxsrv99
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@vblnxsrv99 ~]#

Fig. 16

Please understand that the hosts file is used by client processes to resolve a name to an IP address. Therefore, as far as fundamental server operations are concerned, it is not necessary to have this entry in the file. However, there are other services that will need it, most notably (for us) is the Oracle software to be installed later. Both the database listener and the dbcontrol get default configuration information from this entry.

Conclusion

And there you have it. A Linux machine running under VirtualBox with it’s networking configured to our specification. It can reach the internet for downloading of packages, which will be needed when you start to install the Oracle database. It is completely hidden from our corporate and public networks. And we can reach it from our desktop operating system using the very same tools as any server in our data center. If you get to this point, there is no reason you should not be able to treat this server just like any other server in terms of installing and configuring Oracle databases – or anything else for that matter.

Allocation of extents in multi-file tablespaces

A recurring question I see around the ‘net has to do with the extent allocation in tablespaces with multiple data files. The question is, does oracle completely fill one data file before beginning to use the next, or does it use the files in a balanced, round-robin fashion? Various resources give conflicting answers. A few years ago I put together a test to discover for myself how Oracle handles this. I’ve pulled that test (which was originally run on Oracle 9.2) out of my archives and ran it again on 12.1, with the same results.

This article will present the results of those tests.

Methodology

The test methodology is as follows:

Step 1 – create a tablespace with three small datafiles.

Step 2 – create a table with sufficiently large rows as to force multiple extents with a relatively small number of rows.

Step 3 – insert enough rows into the table to force a sufficient number of extents to force multiple extents

Step 4 – execute queries to map the allocation of the extents.

Trial 1

First, we’ll create a tablespace with three small datafiles.

SQL> create SMALLFILE tablespace bubba_ts
  2        datafile '/oradata/tulsa/bubbatbs_01.dbf'
  3           size 1m autoextend off,
  4         '/oradata/tulsa/bubbatbs_02.dbf'
  5           size 1m autoextend off,
  6         '/oradata/tulsa/bubbatbs_03.dbf'
  7           size 1m autoextend off
  8         EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
  9  ;

Tablespace created.

Next, create a table with a large row size, so as to easily force additional extents. While it is not normally recommended to use CHAR instead of VARCHAR2, in this case it is a convenient way of insuring that every row takes a large amount of space, regardless of the actual data.

SQL> create table bubba.rowid_test
  2         (
  3         key_col number,
  4         big_col1 char(2000),
  5         big_col2 char(2000),
  6         big_col3 char(2000)
  7         )
  8  ;

Table created.

Next, insert enough rows to force the additional extents. In actuality, this took several trials to come up with a number of rows that was both large enough to produce the desired result and yet small enough to remain convenient for the demonstration.

SQL> insert into bubba.rowid_test (
  2                 key_col,
  3                 big_col1,
  4                 big_col2,
  5                 big_col3
  6                )
  7          select level,
  8                 'xxxxx',
  9                 'xxxxx',
 10                 'xxxxx'
 11          from dual
 12          connect by level  select 'row num: '||a.key_col||
  2         '   file: '|| f.file_name as "File List"
  3  from bubba.rowid_test a
  4  join dba_data_files f
  5    on f.file_id = dbms_rowid.rowid_to_absolute_fno(a.rowid,'BUBBA','ROWID_TEST')
  6  order by a.key_col
  7  ;

File List
--------------------------------------------------------------------------------
row num: 1   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 2   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 3   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 4   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 5   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 6   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 7   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 8   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 9   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 10   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 11   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 12   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 13   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 14   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 15   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 16   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 17   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 18   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 19   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 20   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 21   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 22   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 23   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 24   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 25   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 26   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 27   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 28   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 29   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 30   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 31   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 32   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 33   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 34   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 35   file: /oradata/tulsa/bubbatbs_01.dbf
row num: 36   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 37   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 38   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 39   file: /oradata/tulsa/bubbatbs_03.dbf
row num: 40   file: /oradata/tulsa/bubbatbs_03.dbf

40 rows selected.

And finally, another check of simply the extents allocation. Again, it is easy to see the round-robin usage/allocation pattern

SQL> col file_name for a30
SQL> select e.extent_id,
  2         e.file_id,
  3         f.file_name
  4  from dba_extents e,
  5       dba_data_files f
  6  where e.owner = 'BUBBA'
  7    and   e.segment_name = 'ROWID_TEST'
  8    and   e.file_id = f.file_id
  9  order by e.extent_id
 10  ;

 EXTENT_ID    FILE_ID FILE_NAME
---------- ---------- ------------------------------
         0          5 /oradata/tulsa/bubbatbs_01.dbf
         1          8 /oradata/tulsa/bubbatbs_03.dbf
         2          7 /oradata/tulsa/bubbatbs_02.dbf
         3          5 /oradata/tulsa/bubbatbs_01.dbf
         4          8 /oradata/tulsa/bubbatbs_03.dbf
         5          7 /oradata/tulsa/bubbatbs_02.dbf

6 rows selected.

Trial 2

In the previous trial I created the tablespace with “EXTENT MANAGEMENT LOCAL UNIFORM SIZE”. Let’s change that to “EXTENT MANAGEMENT LOCAL AUTOALLOCATE” and observe the difference:

SQL> create smallfile tablespace bubba_ts
  2        datafile '/oradata/tulsa/bubbatbs_01.dbf'
  3           size 1m autoextend off,
  4         '/oradata/tulsa/bubbatbs_02.dbf'
  5           size 1m autoextend off,
  6         '/oradata/tulsa/bubbatbs_03.dbf'
  7           size 1m autoextend off
  8         extent management local autoallocate
  9  ;

Tablespace created.

After repeating the same data load procedure as before, the queries to see the usage pattern now show we’ve only used one datafile:

SQL> select 'row num: '||a.key_col||
  2         '   file: '|| f.file_name as "File List"
  3  from bubba.rowid_test a
  4  join dba_data_files f
  5    on f.file_id = dbms_rowid.rowid_to_absolute_fno(a.rowid,'BUBBA','ROWID_TEST')
  6  order by a.key_col
  7  ;

File List
--------------------------------------------------------------------------------
row num: 1   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 2   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 3   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 4   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 5   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 6   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 7   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 8   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 9   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 10   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 11   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 12   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 13   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 14   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 15   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 16   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 17   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 18   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 19   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 20   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 21   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 22   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 23   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 24   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 25   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 26   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 27   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 28   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 29   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 30   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 31   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 32   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 33   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 34   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 35   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 36   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 37   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 38   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 39   file: /oradata/tulsa/bubbatbs_02.dbf
row num: 40   file: /oradata/tulsa/bubbatbs_02.dbf

40 rows selected.
SQL> select e.extent_id,
  2         e.file_id,
  3         f.file_name
  4  from dba_extents e,
  5       dba_data_files f
  6  where e.owner = 'BUBBA'
  7    and   e.segment_name = 'ROWID_TEST'
  8    and   e.file_id = f.file_id
  9  order by e.extent_id
 10  ;

 EXTENT_ID    FILE_ID FILE_NAME
---------- ---------- ------------------------------
         0          7 /oradata/tulsa/bubbatbs_02.dbf
         1          7 /oradata/tulsa/bubbatbs_02.dbf
         2          7 /oradata/tulsa/bubbatbs_02.dbf
         3          7 /oradata/tulsa/bubbatbs_02.dbf
         4          7 /oradata/tulsa/bubbatbs_02.dbf
         5          7 /oradata/tulsa/bubbatbs_02.dbf

6 rows selected.

Conclusions

So, in this case, as in most things, the answer is “It depends”. At least with the variables I was controlling for, it depends on if the tablespace is defined as UNIFORM extents, or AUTOALLCOATE extents. I have not explored the behavior with dictionary managed tablespaces simply because they were rendered obsolete by the use of locally managed tablespaces. Anyone still using dictionary managed tablespaces needs to be more concerned about migrating to locally managed than how oracle manages those obsolete structures.

I think it also interesting that in both cases the first extents were allocated in the second file of the tablespace. This seemed consistent across multiple trials. I cannot explain this behavior.

Finally, what is the practical value of knowing this? What administrative decision will you make based on these results? Honestly, nothing comes to mind. But for some reason this question comes up now and again. And when it does the responses seem to be based on speculation or an overly simplified “proof”. So I decided to try a more thorough test to at least prove the behavior, if not the reasoning. In the 12c Concepts manual, the section on extents (here) mentions that while a given extent must be in a single data file, different extents of the same segment may be in different data files but it does not explain how that decision is made.

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?”

But I want to store the date in format …..

Taking a breather from my exploration of TNS connection issues, I’d like to take a quick romp through the use (and mis-use) of date formatting. There are no deep mysteries revealed here, and everything is quite well documented. But judging from traffic on popular Oracle forums, it appears this is an area that leaves a lot of confusion. I will try to address those points of confusion directly, and provide examples that will, hopefully, clarify the documentation by connecting some of the dots that people seem to miss.

A very frequent question on OTN is some variation on this:

I have a column, birthdate, that looks like 23-Mar-2011. How do I get Oracle to store the date in the format 2011/03/23?

And the short answer is, “you don’t.”

Invariably, when people ask this type of question what they really want is to be able to display the date in their chosen format. They often mistakenly believe they have to store it in a given format in order display it in that format.. They don’t understand that (1) a column defined as a DATE stores the data in Oracle’s internal format and (2) that column can be displayed in any number of formats in spite of (actually, because of) that internal format.

The internal format is has been documented since at least Oracle 9i, in the Call Interface Programmer’s Guide. The 12c version of that document is found here, with the specific description of the DATE format internal storage located here. The rest of this article will attemp to bring some clarity and understanding to the practical aspects of dealing with dates in an Oracle database.

Using proper data type

In order to understand “you don’t” store the date in whatever-format-you-say, you have to understand the concept of DATA TYPE. (I’ll save the rant for a separate post).

Anything we see in the written (or printed, or displayed-on-a-screen) is nothing but a set of symbols – “characters”. But dates (and numbers) are not characters, they are concepts. When we write a date or a number on paper (or display it on a screen) we not are writing the date itself, but a character representation of a date.

Almost all programming languages have mechanisms for declaring the TYPE of data we are dealing with. The data type defines

  • the possible values for data,
  • the operations that can be done on the data
  • the way the data of that type is stored.

It occurs to me that the definition of a data “type” sounds similar to the definition of an object in object oriented programming – even though data typing has been around much longer that OOP. Oracle has a rich set of data types, all documented in the SQL Reference Manual.

Think about the power this simple concept brings to programming. If I treat everything as a character string, there is nothing to keep me from saying my birthday is “fred”. Or that your salary is “fubar”. There is no way to give you a raise by computing “emp_sal *5”, because internally there is no functional difference between “50000” and “get a pink slip”. Or if I declare the column DAY_OF_BIRTH as a NUMBER, there is nothing to keep me from saying my birthday is 1, or 999999999999. Or entering numbers that look like dates, but certainly don’t get treated like dates, and have no integrity as dates.

In my first drafts of this post I spent quite a bit of time at this point making the case for using the proper data type (DATE) for dates. For the sake of brevity I’ll defer that discussion to another post. For now let’s just assume that your date data is properly typed as DATE, and discuss how to deal with that to get the results you want.

Oracle’s DATE and TIMESTAMP Types

When a column is declared as a DATE or TIMESTAMP, Oracle stores that data in an internal format that is very efficient for dealing with a very large range of dates and times. Dan Morgan has a more through explanation of the details of this internal format at http://psoug.org/reference/datatypes.html. What’s important for us to know is that 1) the data is stored in Oracle’s format, not ours and 2) it includes both date and time. For DATE the time component is down to the second, but not fractions of seconds. TIMESTAMP can store date and time down to billionths of seconds (9 decimal places). TIMESTAMP also has some variants (TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE), but we can ignore these for the sake of the current discussion.

Please note that through the remainder of this article everything said about DATE applies equally to the TIMESTAMP types.

NLS_DATE_FORMAT

When people say Oracle isn’t storing the date in the format they wanted, what is really happening is Oracle is not presenting the date in the character string format they expected or wanted.

When a data element of type DATE is selected, it must be converted from its internal, binary format, to a string of characters for human consumption. The conversion of data from one type to another is known as known a “conversion”, “type casting” or “coercion”. In Oracle the conversion between dates and character strings is controlled by the NLS_DATE_FORMAT model. The NLS_DATE_FORMAT can be set in any of several different locations, each with its own scope of influence.

The weakest setting of NLS_DATE_FORMAT is in the database initialization parameters. Ultimately, all formatting is in the hands of the client process, but lacking any other sources, the client will get its value of NLS_DATE_FORMAT from the database. The reason I say this is the weakest setting is because it is overridden by several other settings, and does not override any settings itself. Setting NLS_DATE_FORMAT in the initialization parameters will be good only until it is overridden by setting it somewhere else, like . . .

Setting it as an OS environment variable on the client machine. In unix that is “export NLS_DATE_FORMAT=somevalue”. In Windows it can be set at either the system environment variables or the command prompt in a command session. I’d think it could also be set in the registry (see http://support.microsoft.com/kb/104011) but I’ve not been able reproduce it. Between setting it at the system environment variables and at the command prompt, the command prompt setting will take precedence.

I won’t spend any time explaining or demonstrating how to set it at the OS because this is, in turn, overridden by . . .

Setting it at the oracle client session level with the statement ‘ALTER SESSION SET NLS_DATE_FORMAT=<somesetting>’. That, in turn, is overridden by . . .

Use of the ‘to_date‘ and ‘to_char‘ functions at the individual sql statement.

Considering the long chain of overriding settings, you should never depend on a setting outside of your own immediate control. That means you will always do one of the following:

1) ALTER SESSION SET NLS_DATE_FORMAT=’whatever format model you want’;
or
2) Proper use of TO_CHAR and TO_DATE at the individual sql statement

I prefer the second, so there is never any ambiguity or question when looking at an individual SQL statement.

Just to drive home the point, let’s do a little demonstration of the various settings. First, create a table with two columns, one a proper DATE and the other a character data type (VARCHAR2), and insert a single row with the same data in both columns. Note that I am inserting sysdate into both columns. Since sysdate returns the current system date-time as a DATE, the insertion into BIRTHDATE_CHR (a varchar2) will cause an implict conversion to a character string.

SQL> create table mytest (
  2  birthdate_dte date,
  3  birthdate_chr varchar2(30)
  4  )
  5  ;

Table created.

SQL> insert into mytest values (sysdate, sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL>

Fig. 1

Nex, repeatedly select that row after altering the session value of NLS_DATE_FORMAT. At each point, notice the difference in the returned value of BIRTHDATE_DTE vs. BIRTHDATE_CHR.

SQL> select * from mytest;

BIRTHDATE BIRTHDATE_CHR
--------- ------------------------------
06-FEB-16 06-FEB-16

1 row selected.

SQL> alter session set nls_date_format='yyyy/mm/dd';

Session altered.

SQL> select * from mytest;

BIRTHDATE_ BIRTHDATE_CHR
---------- ------------------------------
2016/02/06 06-FEB-16

1 row selected.

SQL> alter session set nls_date_format='dd/mm/yy hh:mi:ss';

Session altered.

SQL> select * from mytest;

BIRTHDATE_DTE     BIRTHDATE_CHR
----------------- ------------------------------
06/02/16 09:51:31 06-FEB-16

1 row selected.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from mytest;

BIRTHDATE_DTE       BIRTHDATE_CHR
------------------- ------------------------------
06/02/2016 09:51:31 06-FEB-16

1 row selected.

SQL>

Fig. 2

Oracle datetime functions

There is a second method of controlling the format of a selected DATE data element. Even a session level setting of NLS_DATE_FORMAT can be overridden by use of the datetime functions TO_CHAR and TO_DATE at the individual sql statement. This explicit control cannot be overridden by anything else. That is why it is my preferred method, and the one I press on anyone I am training.

The TO_DATE function takes a character string and a description of that string to return a DATE. It is most commonly used when inserting or updating a DATE. More on that later.

The second datetime function is TO_CHAR. It takes a DATE and a format string to return a character string representation of the date, formatted according to the format string. This format string is formed exactly the same as when setting NLS_DATE_FORMAT.

At this point some people may still be harboring the notion that what I did with ALTER SESSION SET NLS_DATE_FORMAT was somehow altering the data in the table (in spite of what ALTER SESSION really means) but this next example should make it crystal clear that we are simply formatting the data for presentation.

SQL> select birthdate_dte bday1,
  2  to_char(birthdate_dte,'yyyy/mmdd') bday2,
  3  to_char(birthdate_dte,'dd/mm/yy hh:mi:ss') bday3,
  4  to_char(birthdate_dte,'dd/mm/yyyy hh24:mi:ss') bday4
  5  from mytest;

BDAY1     BDAY2     BDAY3             BDAY4
--------- --------- ----------------- -------------------
06-FEB-16 2016/0206 06/02/16 09:51:31 06/02/2016 09:51:31

1 row selected.

SQL>

Fig. 3

NLS Datetime Format Elements

The string of characters defining the format we want for the character representation of the date (the “format model”) is made up of “format elements”. They are fully documented in the SQL Reference Manual, under “Format Models“. Just for fun, let’s play around with some of the lesser used elements.

SQL> -- D returns Day of week (1-7). Depends on the NLS territory
SQL> select to_char(birthdate_dte,'yyyy/mm/dd hh24:mi:ss') bd1,
  2         to_char(birthdate_dte,'D') bd2
  3  from mytest;

BD1                 B
------------------- -
2016/02/06 09:51:31 7

1 row selected.

SQL> -- DAY returns Name of day.  Is case sensitive
SQL> select to_char(birthdate_dte,'DAY') bd2,
  2         to_char(birthdate_dte,'Day') bd3
  3  from mytest;

BD2       BD3
--------- ---------
SATURDAY  Saturday

1 row selected.

SQL> -- DDD returns Day of year (1-366)
SQL> select to_char(birthdate_dte,'DDD') bd2
  2  from mytest;

BD2
---
037

1 row selected.

SQL> -- DY Abbreviated name of day, case sensitive
SQL> select to_char(birthdate_dte,'DY') bd2,
  2         to_char(birthdate_dte,'Dy') bd3
  3  from mytest;

BD2 BD3
--- ---
SAT Sat

1 row selected.

SQL> -- IW Week of year (1-52 or 1-53) based on the ISO standard
SQL> select to_char(birthdate_dte,'IW') bd2
  2  from mytest;

BD
--
05

1 row selected.

SQL> -- RM Roman numeral month (I-XII; January = I)
SQL> select to_char(birthdate_dte,'RM') bd2
  2  from mytest;

BD2
----
II

1 row selected.

SQL> -- YEAR Year, spelled out, case sensitive
SQL> select to_char(birthdate_dte,'YEAR') bd2,
  2         to_char(birthdate_dte,'Year') bd2
  3   from mytest;

BD2
------------------------------------------
BD2
------------------------------------------
TWENTY SIXTEEN
Twenty Sixteen

1 row selected.

SQL> -- we can even insert text of our choosing:
SQL> select to_char(birthdate_dte,'"I was born in " YEAR') bd2
  2  from mytest;

BD2
---------------------------------------------------------
I was born in  TWENTY SIXTEEN

1 row selected.

SQL>

Fig. 4

Inserting data with to_date

Up to this point we have only discussed selecting data. Everything we’ve talked about applies in reverse with the TO_DATE function. This function takes a character string and a format model describing that character string, and returns an internal DATE format. It is used for INSERTing and UPDATEing data as it is stored in a table. From what you’ve seen to this point, a quick example should make it clear.

SQL> truncate table mytest;

Table truncated.

SQL> insert into mytest values (to_date('20110506','yyyymmdd'),null);

1 row created.

SQL> insert into mytest values (to_date('01-JAN-1953','dd-MON-yyyy'),null);

1 row created.

SQL> insert into mytest values (to_date('05/04/63 13:23:47','mm/dd/yy hh24:mi:ss'),null);

1 row created.

SQL> select to_char(birthdate_dte,'dd-Mon-yyyy hh24:mi:ss')
  2  from mytest;

TO_CHAR(BIRTHDATE_DT
--------------------
06-May-2011 00:00:00
01-Jan-1953 00:00:00
04-May-2063 13:23:47

3 rows selected.

SQL>

Fig. 5

Conclusion

Hopefully by this point you will see that storing data as a date and presenting that data in a result set are two different things. A couple of final points need to be made.

First, the to_char function is used to convert from a date to a character string. It makes no sense to give it a character string as an input argument. Conversely, the to_date function converts a character string to a date. It makes no sense to give it a date (datatype) as an arguement, yet we often see something like this:

sql> select to_date(sysdate) from dual;

Second, when designing a table, one should always use the appropriate data type for the type of data being stored. Dates and times are stored in datetime columns, numbers are stored in numeric columns. I’ll write more on that later.

Of course, all I’ve shown here is to drive home the point about display vs. storage. We still need to discuss usage in comparisons — in the WHERE clause of a SQL statement. That will be the subject of my next article.

— Added 5 Nov 2016 —

Thanks to my Oracle Technology Network friend “rp0428” for pointing out that I had omitted links to Oracle documentation that actually describes the internal storage format of DATE data types. He pointed out that this information has been documented since at least Oracle 9i, in the Call Interface Programmer’s Guide. The 12c version of that document is found here, with the specific description of the DATE format internal storage located here. The OTN member that goes by the name “BlueShadow” has also written a good description of the internal storage format. The OTN posting that provides a link and further discussion is located here.

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.

Help! I can’t connect to my database …

(This is a revised version of an article I originally published at edstevensdba.wordpress.com)

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

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

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

C:> sqlplus scott/tiger@larry

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

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

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

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

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

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

What can go wrong?

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

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

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

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

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

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

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

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

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

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

“Film at eleven”.