Network Blues

I really, really do not understand why so many people are having problems
with Oracle Networking. Lots of questions on the usenet about it. And, I am
getting bored of it, so here's a receipe for Oracle networking.

I have installed Oracle, and it worked OK, but since [I booted|I tried at Home] it doesn't.

Well, since you ask, I assume you have the server software installed on your own system. I'll come to remote systems later, don't you worry. I also assume your SID is ORCL. It is a default, used a lot by Oracle. Of course, your installation could be different, but remember it. It's 8 characters max, so it should not be hard to remember.

And it means, if you recall orcl.cs.edu as your identifier, you're wrong.
Make it orcl.

If you're on MS Windows, there is a way of
finding out, if you would have forgotten your SID:

• Open your Services window
• Check for services, starting with OracleService
• What's following OracleService, that's the SID.

I know of no such trick for Linux or Unix (when the instance is shut down). There should be a file, called oratab, that contains SID and Oracle Home entries for those platforms. Location of this file varies, /etc/oratab and /var/opt/oratab are two options.

OK - I know my SID, what has that got to do with the network problems I have?

I'll come to that in a minute.
First of all, there is absolutely no reason why you need networking when all you want to do is access you locally installed Oracle. Just make sure you have an environment variable "oracle_sid" set, and you're done.

But I'm on Windows.

So? There is no difference in how Oracle behaves, just the setting of variables is different:

D:\>net start OracleServiceORCL
Starting service OracleServiceORCL........
Service OracleServiceORCL is started
D:\>set oracle_sid=ORCL

D:\>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Mar 4 10:53:51 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>

And on Linux:

[oracle10@csdb01 oracle10]$ export oracle_sid=o10gR1
[oracle10@csdb01 oracle10]$ sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Mar 4 10:45:23 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

SQL>

Okay? That's all there's to it. Works all the time, because you need it, Oracle needs it. It's the only way, during a fresh install, the instance can be brought up, and a database created.

Okay, got it. Command prompt local connections work.
But I would rather use the @-way of connecting, in stead of remembering SID and "set".

Okay, that still isn't a problem. What you are referring to, are network
aliases, or tns aliases. This "tns" stands for Transparent Network Substrate. You may forget that, but it's Oracle networking.
What you type after the @, is called an alias, and the aliases are stored in a flat file by the name of tnsnames.ora.

The type of connection used in the above example is called a Bequeath connection. It's a call directly to the program, specifying parameters needed. On Linux and Unix, you can see the process, using ps:

oracleo10gR1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))).

This protocol used to be a standard option in the network configuration
assistant, which allowed to get a bequeath entry stored in the tnsnames.ora
file. It isn't anymore, but the protocol is still supported, though a bit hard
to configure. There are some rules to respect; I already mentioned program
calls and parameters.

Here's an example of a bequeath tns-alias entry in tnsnames.ora:

ORCL.CS.NL =
 (DESCRIPTION=
    (ADDRESS = (PROTOCOL = BEQ)(PROGRAM=oracle)
      (ARGV0=oracleORCL)
      (ARGS='(DESCRIPTION=(LOCAL=yes)(ADDRESS=(PROTOCOL=BEQ)))')
    )
    (CONNECT_DATA=(SID = ORCL))
 )

Mind all the quotes; they belong there! And the spaces at the beginning of the lines, too! The only thing you may ever want to change are on the following lines.

Line 1: ORCL.CS.NL =
again, the tns alias (no spaces here, btw). It is what you type after the @: like sqlplus scott/tiger@orcl.cs.nl

Line 3: (PROGRAM=oracle)
it's the oracle executable. An ancient version had a different name under windows, once.
You had to rename the oracle bit to oracle73. Looking at the multiple home option, and naming convention I doubt you ever want to change this. I thought I just mention it as a reference.

Line 4: (ARGV0=oracleORCL):
it's where the SID comes in play. the first argument, passed to the execuable, is -once more- the program name (oracle), with the SID (ORCL) appended.
If you had two installations, one 9i release2, and one 10g, release 1, and each install had a SID, named db920 and db1002 respectively, you would have entries with oracleDB920 and oracleDB1002.
Oh - the upper case is not needed, It's just there to make it easier to read.

Line 7:(CONNECT_DATA=(SID=ORCL);
This is what the client passes to the server. Old notation, it's better to use the
SERVICE_NAME= notation, but this usually is easier to setup.

And it works:

D:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Mar 4 11:47:26 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL>

Hold it! Above you said I had to type orcl.cs.nl, but you just type orcl. How come?

Well spotted! Almost forgot about that. It's an option in a second file, sqlnet.ora. It's your default domain, and the option that takes care of that looks like this. Remember, this is not the file, where the tns aliases are kept, but sqlnet.ora:

NAMES.DEFAULT_DOMAIN = cs.nl

This option in sqlnet.ora takes care of the fact I do not have to type
orcl.cs.nl, but that just orcl will be sufficient. You do not have to use it; that is entirely up to you. And remember, the tns alias is just that! If you would like to use scott/tiger@test.home, and scott/tiger@test.school, you could have the following entries in your tnsnames.ora file:

TEST.HOME =
  (DESCRIPTION=
    (ADDRESS = (PROTOCOL = BEQ)(PROGRAM=oracle)
    (ARGV0=oracleORCL)
    (ARGS='(DESCRIPTION=(LOCAL=yes)(ADDRESS=(PROTOCOL=BEQ)))')
  )
    (CONNECT_DATA=(SID=ORCL))
  )

TEST.SCHOOL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora3.ux.mit.edi)(PORT = 1521))
    (CONNECT_DATA =  (SID = dev))
  )

Please note the fact that SIDs (orcl and dev, respectively) and aliases have nothing in common, although I must confess, these entries (SID and alias used) are fairly often the same.

Summary:

So, what have we got?

• No need for networking when you are working local. Use the bequeath protocol, almost no overhead, and it's fast!


• In order to use bequeath as protocol, set the oracle_sid environment variable.
  


• If you want, you can define a bequeath connection in tnsnames.ora - the central file where all tns-aliases are stored.
  In fact, every installation I do, has such an entry, to allow for lighting fast local connections. It allows for the well-known, familiar "user-id@tns-alias" type of connections, without the need for oracle_sid to exist as environment variable.
  

I see - but what about this TEST.SCHOOL entry?

Now, that is what I called a remote entry: it references a remote server.
Remote servers are most reliably connected to, using the Transmission Control
Protocol/Internet Protocol, TCP/IP. Yes, several other protocals are possible,
but today, TCP/IP is such a standard, anyone has the stacks installed for their
Operating System. Because if you had not, you could not read this... Other
protocols would be IPX on Novell, named pipes on MS Windows, DECNet, or IBM's
LU6.2.

In order to set up a connection to a remote server, you need to know a couple
of things about this server:

• You need the name of the server, or it's IP-address
• You need the port, the listener responds to
• You need the name of the SID, or service name of the Oracle instance

Let's work out an example, suppose you got an email with something like this:

Your account has been set up,
your username on the com4 database on csdb01.cs.nl is scott,
your initial password is tiger.
You are required to change the password upon first login....

So, we have the following information:

• Your IT department stinks - they should have mailed you the TNS entry!
• Name of the server is csdb01.cs.nl
• Listener port is missing, as well as
• the protocol we need to use
• the database SID is com4

Let's create an entry in our tnsnames.ora file:

com4.work =
(description =
(address = (protocol = tcp)(host=csdb01.cs.nl)(port=1521))
(connect_data = (sid=com4))
)

This time, I have choosen to use the SID in the tns alias. And, as this is work, I complete the alias with .work. My tns alias is therefore:
com4.work. I expect to be able to connect with sqlplus scott/tiger@com4.work

On the line address = , the protocol protocol = tcp to be used (TCPIP) is defined, as well as the machine ("host"), we need to connect to host=csdb01.cs.nl.

The protocol, which Oracle abbreviates to TCP, is an assumption (well, an educated guess), as well as the port number of the listener, 1521, in the port=1521 part
of the address =
line.
It is the default port for Oracle listeners.

If, for whatever reason, this does not work, we have the following options:

• ping: can we resolve the name to an IP-address, and can we reach out to the server?



• tnsping: similar to ping, but on Oracle level: resolves the alias, and contacts the listener.

See here:

D:\>ping csdb01.cs.nl
Pingen naar csdb01.cs.nl [192.168.1.199] met 32 byte gegevens:

Antwoord van 192.168.1.199: bytes=32 tijd<10 ttl="64">

D:\>tnsping com4.work
TNS Ping Utility for 32-bit Windows: Version 10.1.0.4.0 - Production on 04-MAR-2006 12:34:48
Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\ora92\network\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address = (protocol = tcp)(host=csdb01.cs.nl)(port=1521)) (connect_data = (sid=com4)))
TNS-12541: TNS:no listener

Looks like you IT department is making backups, or has some work to do... It may very well be, they do not use the default port 1521. Contact them, and ask for clarification; as mentioned, the email you had, was incomplete: protocol, as well as port number were missing.
You can keep guessing a long time, port numbers go up to 65535.

By the time you get the following as a reply to your tnsping:

Attempting to contact (description = (address = (protocol = tcp)(host=csdb01.cs.nl)(port=1521)) (connect_data = (sid=com4)))
OK (10 ms)

you are OK - at least there is a listener out there, the port number you defined is correct, as is the protocol used. So, let's see if we can get some work done:

D:\>sqlplus scott/tiger@com4.work

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Mar 4 13:30:33 2006

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

ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Hmmmm... Now this can be caused by two things:

1. The IT department indeed made backups, and had shut down the listener. It can take some time for an instance to register with a listener, so try again after at least 3 minutes.


2. Your email contains yet another error: the SID com4 is wrong!

In this case, the SID turned out to be wrong; changing it cased to error to disappear, and work could be done.

Another flavor of this error is:

ERROR:

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

this is caused by the fact no SID= was used in the connect descriptor
(that is the official name of the connect_data = part in your tns alias), but SERVICE_NAME=
The listener passes that info, but there is no such service. You can ask the IT
department to verify the services info you received by asking to execute the
lsnrctl services
command on the server. It should have your entry, so if you had
db920.csdb01.cs.nl
as a
service entry, and IT can only find Service "db920.cs.nl" has 1 instance(s), it means you better change to db920.cs.nl.

However, it teaches one important lesson: a successful tnsping does not guarantee a successfull connect! Tnsping contacts the listener, and that's it!

Summary:
So, what have we got?

• For a successful connection to a remote server, you need to know a few things about the (remote) machine:

1. name (or IP-address) of the server
2. the listener (I'll try the default of 1521 first, in case I don't know)
3. service_name or SID of the database
4. protocol used (I'll go for TCP, in case I don't know)

• use ping and tnsping to resolve naming issues
• SID and tns alias may be totally different (but are often the same)
• There are two configuration files involved, tnsnames.ora, which holds my tns aliases, and (possibly) sqlnet.ora, that hold my default domain - that's the bit I have defined in tnsames.ora for the alias domain, but refuse to type.

Oh, since you now are your own IT department, you should have a listener.ora file, that looks like:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = ORCL))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
  (SID_NAME = ORCL)
  (ORACLE_HOME = c:\oracle\DB92)
)
)

You still haven't explained why some things do not work after I rebooted.

Ask yourself: "What changed?". I see this scenario often, when new Oracle components have been installed, like a Forms/Reports (Developer Suite) installation after the database has been installed. Especially on MS Windows, that cannot distinguish between environments.

What happened is very simple: the Developer install added an entry to your path. It made sure the path to the developer executables came first.

But... Developer also comes with a network stack: Forms and Reports usually operate upon data, stored within databases, so these components need to connect.

And it is this new network environment, that is used, because it is first in line.

And the remedy is just as simple: reverse you path: open the Configuration screen, Click on System, go to the Advanced tab; and click the middle button, marked Environment Variables. In the bottom half, the system variables screen, scroll down until you find 'Path'. Double-click to edit, cut (Ctrl-X) the path to your Oracle developer environment (it is the first, left-most entry), and paste it back after the second entry, the one to your server environment.

Now, if you don't like doing that for each and every install, there's two other
options:

1. Maintain one network location
2. Synchronize (all your) network environments.

I like option one best, so I'll explain option 2 first:
Each network environment is maintained under %ORACLE_HOME%\network\admin (that's $ORACLE_HOME/network/admin for the Unix/Linux folk).
Your -previously working- server environment has it's tnsnames.ora, and so has your developer environment. Point is, %ORACLE_HOME% is different for both.

Locate those, and copy the working tnsnames.ora and sqlnet.ora from HOME1 (your first, working install) to HOME2 (and HOME3 and HOME4, etc. etc).

Or... (option 1)

Pick a location (any location, mine is d:\tns), put your oracle network configuration files there, and make sure all your environments understand where to look.

How do I get different Oracle network environments to look in one location?

Oh, quite simple, Oracle has introduced an environment variable for that, called tns_admin. Just define that, and give it the value you want, that is the directory where Oracle networking can find the configuration files. It is quite common to do so in Linux/Unix environments, not so common in MS Windows.

Show me how to define tns_admin in MS Windows.

You will have to use the registry for that:

Start-> Run, type: regedit and press return

You will see a split-window
editor, left side having My Computer. Navigate to HKEY_LOCAL_MACHINE,
and open it. Within that, navigate to Software, and open that. Don't be alarmed here - there's a lot of stuff! Navigate down to the Oracle entry, and open
that (hint: click anything in the left hand window, and press "o" - the
cursor jumps to the first 'O' entry).

Now, suppose I just added the Oracle Developer Suite in HOME8. In order to add
a tns_admin entry there, I will have to put the cursor on that entry, and the
press the right-mouse-button on the right half of the screen. Select to
add a New Value, New string
Value, and change the default New value #1 to TNS_ADMIN. Press return to enter the change, and again, to change the value from nothing to the location where you want your network configuration files stored.

That's all; just close the regedit window - all changes are instantanious.