SQL*Net Configuration [Orig Published - Apr 1999]

Let's set up SQL*Net (known stuff for most DBAs, but I get many questions on this subject often, so here I just provide some examples/templates for the configuration files) on the UNIX server and client PC using the TCP/IP communication protocol. In Oracle8 SQL*Net is called Net8. SQL*Net enables client-server and server-server communication across any network. When a client or server makes a connection request, SQL*Net receives the request and, if more than one machine is involved, passes the request to its underlying layer, the transparent network substrate (TNS), to be transmitted over the appropriate communications protocol to the appropriate server. On the server, SQL*Net receives the request from TNS and passes it to the database as a network message with one or more parameters (that is, a SQL statement). For more information on SQL*Net technology, please refer to "Understanding SQL*Net" manual from Oracle corp.

SQL*Net software

SQL*Net and required protocol adapters come with the Oracle RDBMS CD and the Oracle client CD. Install SQL*Net software and TCP/IP protocol adapters along with server installation. SQL*Net on the server would be installed to $ORACLE_HOME/network. The executables are under $ORACLE_HOME/bin. Oracle7 client software would be installed under $ORACLE_HOME/network and Oracle8 Net8 client software would be installed under $ORACLE_HOME/net80. It is possible to access a Oracle7 database using Net8, also to access a Oracle8 database using SQL*Net (Oracle8 specific features may not be available).

Configuration

SQL*Net is mainly configured using 3 ascii files, which contain information about the database, the protocol, the host name and connection port. The default location for the configuration files is $ORACLE_HOME/network/admin. You may specify a different location by specifying the enviornment variable TNS_ADMIN on the server side. For client side, set this variable in the client side Windows Registry (for 32 bit) or in the oracle.ini file (for 16 bit). The following are the files associated.

listener.ora

The listener configuration file. This file is set up in the server. Specify the type of protocol (we discuss TCP/IP), the host name, the database instance name (SID) and oracle home directory for this instance. You can have multiple databases configured under the same listener name. The standard and default and recommended listener name is LISTENER and most of the machines need only one listener. You can have a Oracle7 listener and Oracle8 listener running with the same name but listening to different ports. Here is a sample listener.ora file:

USE_PLUG_AND_PLAY_LISTENER = OFF
USE_CKPFILE_LISTENER = OFF
CONNECT_TIMEOUT_LISTENER = 40
LISTENER=
	(ADDRESS_LIST=
	(ADDRESS=
		(PROTOCOL=IPC)
		(KEY=PRODDB)
	)
	(ADDRESS=
		(COMMUNITY = tcp.world)
		(PROTOCOL=tcp)
		(HOST=prodserver)
		(PORT=1521)
	)
	)
SID_LIST_LISTENER=(SID_LIST=
	(SID_DESC=
	(GLOBAL_DBNAME = proddb1.world)
	(SID_NAME = PRODDB1)
	(ORACLE_HOME = /ora0/app/oracle/product/8.0.5)
	(PRESPAWN_MAX = 500)
	(PRESPAWN_LIST=
		(PRESPAWN_DESC=
		(PROTOCOL=TCP) 
		(POOL_SIZE=10)
		(TIMEOUT = 2)
	)
	)
	(SID_DESC=
	(GLOBAL_DBNAME = proddb2.world)
	(SID_NAME = PRODDB2)
	(ORACLE_HOME = /ora0/app/oracle/product/8.0.5)
	)
	)

Please note that since the listener name is LISTENER, the parameters also end with LISTENER, if your listener name is DALLAS, the parameters would be SID_LIST_DALLAS, CONNECT_TIMEOUT_DALLAS, etc.
There are two instances defined in this listener file. Note that the second entry does not have any PRESPAWN entries (optional). For a description of these parameters and other control parameters, please refer to the manual "Understanding SQL*Net".

tnsnames.ora

This file is used to identify the destinations (databases). The file has information for the connect string the user specify, such as, the port number, host name, SID name, etc. This file should be available locally from where the connection is made (at the client PC as well as server). You can have different alias names (connect strings) for the same database defined. For each alias name, define an entry in this file. Here is an example:

DALPROD.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = prodserver)
		(PORT = 1521)
		)
	)
	(CONNECT_DATA = (SID = PRODDB1)
	)
	)
PRODDB1.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = prodserver)
		(PORT = 1521)
		)
	)
	(CONNECT_DATA = (SID = PRODDB1)
	)
	)
DALDEV.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = devserver)
		(PORT = 1521)
		)
	(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = devserver)
		(PORT = 1525)
		)
	)
	(CONNECT_DATA = (SID = TESTDB1)
	)
	)

Make sure you have the HOST, PORT and SID defined correctly. You can also provide the IP address of the HOST instead of name. Notice the entry for DALDEV, there are two port addresses, this is to randomize connections between listners, if there are two listeners on the server (listener load balancing). All the lines are self explanatory, for more information, refer to "Understanding SQL*Net".

sqlnet.ora

This is a configuration file, which may be present in the server as well as the client. Basically this file is used to cofigure the following:

	Dead connections detection
	Tracing and logging parameters
	Default domain (notice that in the tnsnames.ora file we have .world in the connect string names, you can specify default domain as world in this file, so the users need not specity it while connecting)
	Other optional parameters

A sample file would be like this:

AUTOMATIC_IPC = OFF
SQLNET.EXPIRE_TIME = 0
NAMES.DEFAULT_DOMAIN = world
NAME.DEFAULT_ZONE = world
# DAEMON.TRACE_MASK = (106)
# DAEMON.TRACE_DIRECTORY = /ora_dump/network/trace
# DAEMON.TRACE_LEVEL = OFF
# Tracing Client Activity
# TRACE_LEVEL_CLIENT = USER
# TRACE_FILE_CLIENT = sqlnetc.trc
# TRACE_DIRECTORY_CLIENT = /ora_dump/network/trace
# Tracing Server Activity
# TRACE_LEVEL_SERVER = 16
# TRACE_FILE_SERVER = sqlnets.trc
# TRACE_DIRECTORY_SERVER = /ora_dump/network/trace
# Logging Client Activity
# LOG_FILE_CLIENT = sqlnetc.log
# LOG_DIRECTORY_CLIENT = /ora_dump/network/log
# Logging Server Activity
# LOG_FILE_SERVER = sqlnets.log
# LOG_DIRECTORY_SERVER = /ora_dump/network/log

Some parameters are specific to the server and some are specific to the client.

Managing the listener(UNIX):

Make sure you have ORACLE_HOME set, also if the config files are not in ORACLE_HOME/network/admin, set TNS_ADMIN to the directory.
To start the listener use the utility lsnrctl

lsnrctl start

If you have a listener name other than LISTENER then specify that name as

lsnrctl LISTENERNAME start

To stop the listener,

lsnrctl stop

If you have made changes to the listener, but do not want to stop and start, you may do

lsnrctl reload

To see the listener status,

lsnrctl status

You can also specify password for the listener.

Notes:

If you have many clients, it may be a maintenance problem to have tnsnames.ora and sqlnet.ora on each client PC. You can install SQL*Net on the network and everyone can share the same information. For 32 bit SQL*Net, make sure the client PC windows registry points to the network and for 16 bit SQL*Net set up the oracle.ini file with appropriate parameters. The oracle.ini file is mentioned in the win.ini file underORA_CONFIG=Drive:\path\oracle.ini. The parameters you have to look for are ORACLE_HOME, NLS_LANG, TNS_ADMIN, RDBMS73, ORA_NLS32 etc.