Browse DevX
Sign up for e-mail newsletters from DevX


Oracle Net Configurations: Find the Weak Link : Page 4

Properly developing and deploying an Oracle application requires a strong knowledge of network configuration and setup. This article discusses Oracle Net Services, the suite of products that enables a connection between your application and the Oracle database, and how best to configure and troubleshoot it.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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%\network\admin). The common, more important files are listed in Table 1.

File Lies on Contains Parameters for
sqlnet.ora Server, client Domain, naming methods, log/trace, route, security, access control
tnsnames.ora Client Net service names mapped to connect descriptors
listener.ora Server Protocol addresses, services, listener control
Table 1. The Common Configuration Files for Oracle Net Services

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:
  • Naming method parameters:
  • SQL*Net parameters:
  • Server parameters:

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<SID>.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<SID>.ora
  • The value of the latter part of service_name in init<SID>.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%\network\admin) 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_<SID>_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%\network\admin) 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<SID>.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<SID>.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<SID>.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<SID>.ora—Database/Instance Configuration File
In all the cases where the init<SID>.ora file has been mentioned, the user can opt to use the SPFILE. But keeping a fairly updated copy of INIT<SID>.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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date