Bringing client and server to talking terms

Bringing client and server to talking terms
A SQL*Net primer

This paper describes something on which many Oracle users waste a lot of time – configuring a client properly. We ignore any exotic setting to concentrate on the staple food of 99% of Oracle sites, plain vanilla TCP/IP SQL*Net, without any protocol interchange nor even TNS names server, and a UNIX or NT server. Basic stuff.

On the server

When a client connects to a server, it needs a broker to establish the connection. This broker is the listener (which of course is not named listener, it would be too easy, but tnslsnr), which, as the name listener implies, spends most of its time waiting on a special port for incoming connections. What the listener receives is something such as ‘I want to connect as SCOTT/TIGER to instance ORADB’. The only thing the listener is interested in is the instance name (although Net8 sometimes refers to ‘services’, probably to make things look more abstract and confusing). The instance name is great but it is not enough to know how to connect; remember, when you are trying to work locally with Oracle, you usually need two things, the Oracle system identifier (SID, i.e. the instance name), and the Oracle home directory where Oracle is installed. The reason is, that you may well have two or more different versions of Oracle installed on the same machine (especially on development machines) and you must run the appropriate code to connect to the right instance. The information saying ‘this instance is running this version of Oracle’ is recorded in the SID_LIST section of the listener configuration file listener.ora
Most people use a single listener; however you may start several listeners on a machine (may be useful as in some cases you have SQL*Net incompatibilities between two versions of Oracle, such as 7.1 and 8.x for instance you may have to start two separate listeners, one for Oracle8 and one for Oracle7).

Setting up the listener
Most of the setup is normally automatically done by Oracle during installation all you have to do usually is add your databases to the listener.ora file as you create them on the server.

What you should check :
The listener must be declared as an existing service; you must find under Unix a line such as

listener
1521/tcp

# Oracle SQL*Net listener

in the /etc/services file
(the port number is usually 1521 or 1526, but you can define anything as long as it does not conflict with something else)

On the client

The file which matters most on the client is the so called tnsnames.ora file. What does it contain ? Basically, it associates a name (the TNS alias TNS stands for Transparent (wishful thinking !) Network Substrate) with something more physical, namely a host name and an instance identifier. It must also say which port the listener is listening on at the other side.

So what happens ?

You say to SQL*Plus (for instance) that you want to connect as larry/ilovebill@single_instance.

The client SQL*Net layer spots the @ in the connection string and the TNS alias, here single_instance. It looks up your tnsnames.ora file to find out what single_instance is

What can go wrong at this stage :

You may have no tnsnames.ora file (unlikely, any installation creates one)

It finds in the tnsnames.ora an entry which says that single_instance refers to a database named PROD on a machine named chillywilly.us.oracle.com, and that the port used by the listener on this machine is 1521.

The most common error is
ORA-12154: TNS:could not resolve service name
This indicates that the tns alias has not been found in the tnsnames.ora file. Several possibilities :

You have mistyped the tns alias should be more or less obvious
You are not using the tnsnames.ora file you think you are using. This may occur when several releases of Oracle have been installed on a machine. Check for a file named sqlnet.log
in your current directory (in Windows/NT environment where the notion of ‘current directory’ is often hazy, locate all sqlnet.log files and edit the one which was most recently modified). The sqlnet.log files are appended (never rewritten), so what matters to you is probably towards the end of the file. You may find indications there about the file you are using, or at least clues as to whether you are really using the one you think. Check the TNS_ADMIN environment variable under Unix. If defined, this is where Oracle looks for a tnsnames.ora file instead of the default
$ORACLE_HOME/network/admin/tnsnames.ora file.
There is a suffix problem. Beware that there may be a sqlnet.ora file located in the same directory as tnsnames.ora. sqlnet.ora is to SQL*Net what glogin.sql or login.sql are to SQL*Plus - a default set up. It is used, among other things, to turn trace on/off and it may indicate a default domain (indicated by a prefix) in case of doubt, you may create two aliases for the same database, one with a prefix, and another one without, eg

Single_instance = ( # This is the TNS alias
  (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = ORACLE.world) # You can live without this
          (PROTOCOL = TCP) # Underlying protocol
          (Host = chillywilly.us.oracle.com) # Host name
          (Port = 1521) # Port the listener is listening on
         )
      )
  (CONNECT_DATA =
     (SID = PROD ) # Instance name on the target machine
                   # you can live without GLOBAL_NAME
)
)

Single_instance.world = (
   # Another alias for the same database
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = ORACLE.world)
          (PROTOCOL = TCP)
          (Host = chillywilly.us.oracle.com)
          (Port = 1521)
        )
     )
  (CONNECT_DATA =
     (SID = PROD)
   )
)

It sends a request to connect to PROD on the port 1521 to
chillywilly.us.oracle.com.
What can go wrong at this stage :
ORA-12545: TNS:name lookup failure

Look at the sqlnet.log file; you will probably find :
TNS-00515: Connect failed because target host or object does not exist
Check that the hostname really exists. Try to ping it. What may be missing is the right entry in /etc/hosts or the DNS (dynamic name server) files. Note that if having those files updated is a problem, you can put the IP address of the machine in place of its name - now this is not a good practice.
ORA-12203: TNS:unable to connect to destination
The machine may be down. Try to ping it.
There may also be problems at the TCP layer. You should check that you can telnet or ftp the machine.
There may be firewall problem. You can check it with :
$ telnet chillywilly.us.oracle.com 1521
Trying 255.255.255.255...
telnet: Unable to connect to remote host: Connection refused
(A typical ‘no, you can’t enter here’ message). Check with network engineers and/or security people so that you are allowed to connect to this port on the host from your client.
There may also be no listener on the machine nothing at all listening on port 1521

The listener at the other end picks up the request, and searches the listener.ora
file for PROD. This tells it that its ORACLE_HOME directory is, say , /usr/app/oracle/products/8.1.7 , so it forks (creates a sub-process), and makes this sun-process run the Oracle kernel found in $ORACLE_HOME/bin.
Then the new process will talk directly to the client process and the match-making role of the listener is over. When using multi-threaded servers (MTS), the picture is hardly different, no sub-process is systematically created but the request is passed to a dispatcher which passes it in turn to a server (or starts a new server if all servers are already busy).

A common error at his stage is
ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Means that the SID was give is undefined in the part of the listener.ora file which is relevant to the listener which answered. Beware that there may be several listeners. Check that you hit the right one. Otherwise check that the SID specified for the alias in the tnsnames.ora file is correct.
This may also (more rarely) occur :
ORA-12500 Listener failed to shut a dedicated server process
The more likely reason is a reason linked either to the maximum number of processes allowed for an instance (the processes parameter in the init.ora file) or to the maximum number of processes allowed to run as oracle something which you should check with your system administrator.

Then one goes through the normal authentification process, etc. Quite obviously the database must be up.

The special case of database links

One creates a database link by specifying, on a machine, a user name, password and connect string to another database. The thing to be aware of is that with a database link, the actual client of the database one tries to connect to through the database link is the database on which the database link was created, not the ‘final’ client.
Suppose you are using SQL*Plus on a PC, and from here accessing a database named ALPHA on which a database link is created to give access to a database named OMEGA.
When you are connected to ALPHA and try a
select * from some_table@omega;
The tnsnames.ora file which is used to fetch the data is located on the same server as the ALPHA machine NOT on your PC. It often puzzles people to be able, from their machine, to be able to connect with
sqlplus scott/tiger@omega
And getting an ORA-12154 error (or the reverse). In fact, different tnsnames.ora files are used in each case.

In search of a backup strategy Stay tuned....

Bringing client and server to talking terms A SQL*Net primer

Bringing client and server to talking terms
A SQL*Net primer