erformance and connection are the main issues developers and testers face when deploying Oracle applications. Most of the connection issues can be traced to how the Oracle Net Services and its related components are configured. According to the Oracle Manual, Oracle Net Services is “a suite of networking components that provide enterprise-wide connectivity solutions in distributed, heterogeneous computing environments.” Seemingly benign changes to the network configuration at this layer can bring systems to a halt?and debugging at that point is a laborious, time-consuming task.

Oracle Net Services is comprised of Oracle Net, listener, Oracle Connection Manager, Oracle Net Configuration Assistant, and Oracle Net Manager. Oracle Net is the “communication software that enables a network session from a client application to an Oracle database server.” Put another way, Oracle Net is a “relay” layer that sits on top of network protocols and resides at both the client and server ends. Its function is to perform the following critical network tasks:

• Relay calls from the client to the Oracle server and back
• Perform character translations between the client characterset and the database characterset

This article details the role and importance of Oracle Net, and offers solutions for some of its common configuration and troubleshooting issues. It starts with an introduction of the mechanisms by which a database application interacts with the Oracle Net layer. Then it examines the server stack, the client stack, the various topologies, and the network configuration files that make up Oracle Net. The article concludes with a look at the two-task layer and the globalization issues.

The Oracle Net Client Stack

The Oracle Call Interface (OCI) Layer
Most database programming interfaces (JDBC, ADO, ODBC, etc.) communicate with the Oracle Net layer through the OCI layer (see Figure 1). Perl-DBI or PHP applications that talk to Oracle also interact via this layer. Pre-compiler applications like Pro*C/C++ or Pro*COBOL, however, communicate with Oracle through a layer parallel to OCI called SQLLIB. Both OCI and SQLLIB interact with Oracle Net through the UPI (User Program Interface) layer, the lowest layer of the client stack.

Figure 1: Client End of an Oracle Application

OCI is the lowest level at which client programs should interact with Oracle. OCI and SQLLIB layers get installed only when Oracle clients are installed. Although non-OCI JDBC drivers bypass the OCI layer, they are thin clients that contain limited networking capabilities for talking to the Oracle Net layer. Because these drivers do not require installation of the Oracle client, the applications are easier to deploy?but at the cost of performance. The throughput these drivers provide is less than that of OCI-based JDBC drivers. (asktom.oracle.com provides a nice example of this.)

The Network protocols Oracle Net supports are TCP/IP, TCP/IP + SSH, and named pipes. These days, you can find other ways to connect to an Oracle database (e.g., using HTTP, FTP, and WebDAV), but this article doesn’t cover these.

The Oracle Net Server Stack
On the server side, the Oracle Net layer consists of a listener and a combination of the protocol support and the foundation layer (see Figure 2). The PMON (process monitor) process of the server registers with the listener initially. The Oracle Listener listens for client requests and establishes a connection with the server. The listener serves only to establish a connection between the client and the server. Once it establishes the connection, it has no other purpose.

Figure 2: Server End of an Oracle Application

The OPI (Oracle Program Interface) is the highest layer of the server-side call stack. The Oracle Net layer talks to the OPI layer, which in-turn talks to the instance. (For the various layers that make up the Server Process, please refer to Oracle 8i Internal Services by Steve Adams, O’Reilly Press.)

Transparent Network Substrate (TNS)
TNS is the basis of the Net foundation layer. It provides a single common interface for all protocols.

The Two-task Common (TTC) Layer
Lying between the UPI and Oracle Net layers on the client is the TTC layer, which also exists between the OPI and RDBMSs. This layer enables conversion between different charactersets or formats. It evaluates the requirements for conversion when the listener initially establishes the connection.

Errors such as “ORA-03120 two-task conversion routine: integer overflow” are raised by this layer.

Oracle Net Application Topologies
Understanding the possible application topologies involved in Oracle Net is essential. This knowledge helps you recognize which network problems demand debugging at each layer and which you can tackle by elimination: monitoring each stage and making sure the error is not occurring at that layer. Topologies also help you analyze performance problems by judging the volume of traffic, the number of repeated calls, and the bulk of character conversions taking place across the network.

The following are the topologies you are likely to encounter (each will be discussed in detail below):

• Client/server architectures
• Server application architectures
• Web application architectures
• Database links
• Replication
• External procedures
• Heterogeneous connectivity

Irrespective of the programming interfaces you use (OCI, Pro*C, ODBC, etc.), application topologies influence the application design from the point of view of performance, error handling, and recovery.

Client/server Architectures
Figure 3 illustrates a scenario in which the client application accesses the database across the network. The Oracle Net pieces lie on either end of the network (say, LAN/WAN). Every user connection from the client connects to a server process on the server side. If the configuration is a dedicated server configuration, one server process is spawned by the listener per client connection.

Figure 3: Client Application Talking to the DB

Such a configuration is not scalable. In case the configuration is of shared server type, no one-to-one correspondence exists between the client connections and the server processes spawned. To know your configuration, you can query the SERVER column of the V$SESSION view.

Figure 4 illustrates a scenario in which the client application talks via a communication layer to the server application/middleware. The bridge between the ends of the network is the communication layer. The server application/middleware will access the Oracle Instance via Oracle Net. Another variant of this topology is when the server application/middleware and the database server are on different machines.

Figure 4: Client Application Talking to the Server Application

Server Application Architectures
Figure 5 illustrates a scenario in which the server application accesses the database on the same machine. This is a minor variant of Figure 3, except that this scenario likely will reduce issues of network latency.

Figure 5: Server Application Talking to the DB

Web Application Architectures
Figure 6 illustrates a scenario in which the Web browser connects to the Web server. The Web server talks to the Oracle database using a Web server module, an application server, a transaction server, a server component, or a CGI handler. As in Figure 4, another variant of this topology is when the application server/server component and the database server are on different machines.

Figure 6: Client Application Talking to the Server Application

Database Links
Figure 7 illustrates a scenario of a database link that accesses objects from another database. The other database may be on the same machine or on another machine. A database link is like a stub object that exists in database A, while operations over the link actually take place on database B.

Figure 7: Two Variants of a Database Link Topology

Note that in these figures, the database and the server process are mentioned separately. The database link is a persistent object and its lifetime is not limited to the lifetime of the instance. Just to clarify, the server processes, the background processes, and the SGA together form the instance. The database is the collection of files containing data and configuration/state information.

Replication
The basis of replication is a database link. The refreshing of data, from the materialized view log to the materialized view, happens over the database link and conversely over Oracle Net (see Figure 8).

Figure 8: A Replication Topology

External Procedures
External procedures (ExtProcs) are procedures in libraries declared in the database, but they are defined externally and run in a separate process space (see Figure 9). This is a mechanism by which database functionality may be extended to provide more OS interaction by means of a shared library or DLL. These are also used to perform computation-intensive operations in a more suited language like C. The listener spawns the ExtProc process in a manner similar to the server processes (hence the dotted line). Once the process is spawned, the communication happens via IPC.

Figure 9: External Procedure Invocation

Heterogeneous Connectivity
The purpose of heterogeneous gateways is to make non-Oracle databases seem like Oracle databases to client programs. The same SQL and APIs to be run against an Oracle database work against other databases. Oracle provides transparent gateways for commercial/popular DBMSs (see Figure 10). Observe that, conceptually, Figure 10 is similar to Figure 9.

Figure 10: Transparent Gateways

A variant of Figure 10, Figure 11 illustrates the heterogeneous connectivity to those DBMSs for whom no transparent gateway exists and for which Oracle needs to fall back upon generic interfaces like ODBC or OLEDB and the other DBMSs native drivers to communicate.

Figure 11: Generic Connectivity

Network Configuration for Oracle Net Services
The configuration files for Oracle Net Services lie in $ORACLE_HOME/network/admin (on Windows it’s %ORACLE_HOME%
etworkadmin). The common, more important files are listed in Table 1.

sqlnet.ora?Profile Configuration File
Sqlnet.ora is the smallest of the three common configuration files and normally contains only a line or two. In most cases, the only entry is the NAMES.DEFAULT_DOMAIN parameter.

The following are some of the parameters that are required during troubleshooting and configuration (For a comprehensive list, refer to the Oracle manual):

• Logging/tracing parameters:
  • LOG_DIRECTORY_SERVER, LOG_DIRECTORY_FILE, etc.
  • TRACE__DIRECTORY_SERVER, TRACE_DIRECTORY_FILE, etc.
  • TRACE_LEVEL_SERVER, TRACE_LEVEL_FILE
  • TNSPING.TRACE_DIRECTORY
  • TNSPING.TRACE_LEVEL
• Naming method parameters:
  • NAMES.DEFAULT_DOMAIN
• SQL*Net parameters:
  • SQLNET.AUTHENTICATION_SERVICES
  • SQLNET.EXPIRE_TIME
• Server parameters:
  • USE_DEDICATED_SERVER

Over time, you might notice the sqlnet.log file littering your disk and turning up in many directories. This is because the default value of LOG_DIRECTORY_CLIENT is the current working directory. It is recommended that this value be set in the client to point to a fixed location so as to aid post-facto troubleshooting.

Specifying the NAMES.DEFAULT_DOMAIN parameter is a good practice. Sites that dealt with the dreaded “.WORLD” issues (with versions 8.0.6 and 8i of Oracle) can definitely vouch for this. This parameter defaulted to .WORLD in the 8.0.x days. This behavior changed in 8i and later versions, defaulting instead to a blank. Queries over database links and synonyms (pointing to schema objects in another database over a database link) experienced errors like ORA-02019. init.ora parameters like GLOBAL_NAMES and DB_DOMAIN added to the confusion. A quick search on the comp.databases.oracle newsgroup on groups.google.com would returns a large list of results about the problems people faced.

For “healthy” connections, the value specified in the NAMES.DEFAULT_DOMAIN must match the following:

• The value of the latter part of the tnsalias in tnsnames.ora
• The value of db_domain in init.ora
• The value of the latter part of service_name in init.ora (Service name is the concatenation of instance_name/db_name and db_domain with a dot (.) in between.).
• The value of the latter part of global_dbname in listener.ora (in static SID_LISTs)

tnsnames.ora?Local Names Parameter File
Tnsnames.ora may lie either in $ORACLE_HOME/network/admin (on Windows, %ORACLE_HOME%
etworkadmin) or at any other location specified by the environment variable TNS_ADMIN (in Windows, a registry value too). In UNIX and related operating systems, it can be located in a global configuration directory.

The TNSPING utility determines whether the connectivity between the client and the listener process is functioning. Before you run any other utility, such as ODBC connection test, etc., you should run TNSPING. TNSPING output also indicates which tnsnames.ora file is being used. A wrong tnsnames.ora can cause a lot of debugging heartburn. TNSPING, however, will not give you an authoritative reply to the question: is the instance running? It communicates only with the listener, not the instance.

The Net Configuration utilities are the best way of configuring tnsnames.ora. Misplaced parentheses or a missing whitespace can cause errors. But since the Oracle Net Configuration Assistant is slow (and sometimes disliked), developers and testers opt for cutting and pasting earlier entries and modifing the required values.

In the case of client applications connecting to a database instance, the developer’s/tester’s arsenal must also contain other tools that verify the connection. ODBC connection test, ADO connection test, and JDBC connection test tools are essential. Such tools may either be homegrown or bundled with other products (such as ODBC drivers).

In the case of database-to-database connectivity via database links (snapshots, replication, streams, etc.) and applications built on top of these technologies, it is very important to verify and test the entries in the tnsnames.ora file on all the machines involved.

Tnsnames.ora contains a connect descriptor, which is a formatted description of a network connection. It contains information about the destination service and network route information.

The following is an example of the connect descriptor format:

net_service_name=  (DESCRIPTION=    (ADDRESS=(protocol_address_information))   (CONNECT_DATA=      (SERVICE_NAME=service_name))) 

This connect descriptor format requires minor variations to handle configurations like “Client load balancing” and “Connect-time failover”. Refer to the Oracle Net manual for details.

The description portion of the connect descriptor contains the session data unit (SDU) parameter, which may be tuned to improve performance when transferring large amounts of data over WANs. This parameter is set in the tnsnames.ora on the client side.

Please refer to this article for some notes about avoiding error TNS-12154 (TNS:listener could not resolve SERVICE_NAME given in connect descriptor). Another common error due to misconfiguration is TNS-12541 (TNS:no listener). It is often the result of specifying the wrong port number. Precious time is lost trying to debug issues like these.

Developers must also be aware of platform-specific quirks like, in case of Windows, the service (OracleServiceSID) may be running while the instance is down. The value of the (somewhat) obscure parameter ORA__AUTOSTART is to blame.

listener.ora?Listener Configuration File
Like the tnsnames.ora, this file may either lie in $ORACLE_HOME/network/admin (on Windows, %ORACLE_HOME%
etworkadmin) or at any other location specified by the environment variable TNS_ADMIN (in Windows, a registry value too). In UNIX and related operating systems, you’ll find it in a global configuration directory.

Listener.ora contains the following information:

• Name of the listener (defaults to LISTENER)
• Protocol addresses on which the listener accepts connection requests
• Database services
• Control parameters

The listener listens for client connections. Once it receives a session request and establishes the link between the client and the server, it then establishes a new process (or picks from a pool and allocates processes). The client and the server then communicate independently of the listener. Shutting down or restarting the listener while a client application is running has no effect on the existing application session, but it is not a recommended practice. Instead, you can use the Listener Control Utility (lsnrctl) to dynamically change the required parameters.

With the Listener Control Utility, you can control the listener. You can start and stop the listener using lsnrctl start and lsnrctl stop. Typically, the listener is started in inittab or rc3.d, while it is stopped in rc.shutdown. (Check your UNIX documentation for details).

The listener may be configured statically or dynamically. In the case of dynamic registration, no SID_LIST parameters need to be listed in the listener.ora. The PMON background process registers with the listener.

You would use dynamic registration for the following two sets of conditions, for example:

Condition 1:

• Listener is running on port 1521.
• The listener name is ‘LISTENER’.
• No local_listener parameter exists in init.ora.
• No sid list exists in listener.ora.

Condition 2:

• Listener is running on a port other than 1521 and/or the listener name is not ‘LISTENER’.
• The local_listener parameter points to a tnsalias entry in tnsnames.ora.
• No sid list exists in listener.ora.

Dynamic registration is also dependant on the values of DB_DOMAIN, INSTANCE_NAME, and SERVICE_NAMES in the init.ora file.

Static configuration is required in cases such as external procedure calls. In these cases, the SID_LIST section of the listener.ora file is mandatory.

Certain cases may not require the listener.ora file at all (e.g., when the server (instance) and the client are local and communicating on port 1521). But to be safe, configuring the listener is always a good idea.

Other noteworthy init.ora parameters are REMOTE_LISTENER (to be used instead of LOCAL_LISTENER) and DISPATCHERS (for shared servers).

The lsnrctl status displays a list of the listeners (including descriptions and services). A READY status indicates that the listener can accept connections, while a BLOCKED status indicates otherwise. If any of the services has an UNKNOWN status, the listener has been statically configured.

init.ora?Database/Instance Configuration File
In all the cases where the init.ora file has been mentioned, the user can opt to use the SPFILE. But keeping a fairly updated copy of INIT.ora is always a good idea.

The Importance of the Triad (sqlnet.ora, tnsnames.ora, listener.ora)
Whether you deal with replication, streams, heterogenous services, bulk data transfer across database links, or an ‘ordinary’ client application connecting to the Oracle instance, a number of issues you face are due to incorrect network configurations or due to lack of understanding of the issues involved.

The Two-task Layer (“Two-Task Common”) and Globalization
The two-task layer performs datatype and characterset translation operations between the server environment and the client environment. It carries out these conversions only when required. While connecting, it evaluates the differences between internal data and the target characterset.

In case of a database serving clients multilingual data, the NLS_LANG parameter is among the most important. Typically, the characterset of the database is US7ASCII, WE8ISO8859P1, or UTF8. The database characterset denotes the ‘range’ of data that may be stored in the database. The NLS_LANG value should match the version of the client’s operating system. Matching a value to the backend database characterset is a common mistake that prevents the two-task common layer from performing conversions. It assumes that the data flowing from the client to the server is exactly in the format required by the server, and this can lead to data corruption.

Another very common error is mismatching the client characterset and the database characterset. If a mismatch exists, some characters occurring on the client may not be part of the database characterset. In such a case, the data will get inserted in a corrupt fashion. Be particularly aware of this in export/import. Data corruption issues tend to lie low initially, and when they manifest, it is often too late.

Since multiple NLS_LANG registry entries (in Windows) are possible, in addition to an environment variable (Unix has only the environment variable), the best way to find out which value is being used is by typing "@.[%NLS_LANG%]" at the SQL*Plus prompt. The error message displayed correctly echoes the NLS_LANG being used.

Being aware of NLS_LANG is particularly important because the errors it causes are difficult to trace and pinpoint. If, for instance, certain inserted characters are showing up as ‘?’ (or