4. Configuring SQL*Net on the Server
All of these files configure the Oracle networking software (SQL*Net, aka Net8 for Oracle8). These files should all be created on the server in the $ORACLE_HOME/network/admin directory.
4.1 tnsnames.ora
The TNSNAMES.ORA file identifies services available from the machine. On our instance here we will describe all databases that the server has mounted. For each database instance on your server add a section like below:
orcl.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = <INSERT HOST NAME OF SERVER HERE> )
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = <INSERT HOST NAME OF SERVER HERE> )
(Port = 1526)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
4.2 listener.ora
The listener.ora file contains the descriptions of the services that other machines are allowed to connect to and any configuration that is required for the server listener.
It contains sections for the listener name, listener address, databases served by the listener and configuration parameters.
Here is an example:
# Name of listener and addresses to listen on
LISTENER =
( ADDRESS_LIST =
(ADDRESS =
(PROTOCOL=tcp)
(HOST=<INSERT HOST>)
(PORT=1521)
(COMMUNITY=UK_SUP_TCPIP)
)
(ADDRESS =
(PROTOCOL=ipc)
(KEY=700)
(COMMUNITY=UK_SUP_TCPIP)
)
)
# List of services served by this listener
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl)
(ORACLE_HOME=/home/oracle/7.3.3.0.0)
)
)
# Start of configuration parameters.
TRACE_LEVEL_LISTENER=OFF
TRACE_FILE_LISTENER = "listener"
LOG_FILE_LISTENER = "listener"
CONNECT_TIMEOUT_LISTENER = 10
STOP_LISTENER = YES
DBA_GROUP = dba
4.3 sqlnet.ora
The sqlnet.ora file contains configuration for the particular node of the network. This is independent of the number of databases or the number of listeners. The most important thing in this file is the Dead Connection Timeout configuration variable.
Dead connection timeout checks every incoming process to a database instance and ensures that the client end of it is still responding. If the client (of whatever type) is not responding then the Oracle server shadow process is killed.
This is very useful if you have many clients accessing a database, especially during a developmental phase when those clients are more likely to be failing to exit cleanly from the Oracle database.
Below is a copy of my own sqlnet.ora file for you to puruse:
TRACE_LEVEL_CLIENT = OFF
sqlnet.expire_time = 30 # The number of seconds between client checks.
names.default_domain = world
name.default_zone = world
4.4 Starting and Stopping the Listeners
Now that the configuration of the listeners and SQL*Net is complete we can attempt to connect to the database using the networking software. (Before we were using direct links to the database, whereas here we are simulating a connection from a remote client machine).
To start the listener using the above configuration:
$ lsnrctl
LSNRCTL for SCO System V/386: Version 2.3.3.0.0 - Production on 23-FEB-98 20:38:25
Copyright (c) Oracle Corporation 1994. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> start
Starting /home/oracle/7.3.3.0.0/bin/tnslsnr: please wait...
TNSLSNR for SCO System V/386: Version 2.3.3.0.0 - Production
System parameter file is /home/oracle/7.3.3.0.0/network/admin/listener.ora
Log messages written to /home/oracle/7.3.3.0.0/network/log/listener.log
Listening on: (ADDRESS=(PROTOCOL=tcp)(DEV=6)(HOST=192.168.1.1)(PORT=1521))
Listening on: (ADDRESS=(PROTOCOL=ipc)(DEV=10)(KEY=700))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=magic.com)(PORT=1521)(COMMUNITY=UK_SUP_TCPIP))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for SCO System V/386: Version 2.3.3.0.0 - Production
Start Date 23-FEB-98 20:38:50
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP ON
Listener Parameter File /home/oracle/7.3.3.0.0/network/admin/listener.ora
Listener Log File /home/oracle/7.3.3.0.0/network/log/listener.log
Services Summary...
orcl has 1 service handler(s)
The command completed successfully
LSNRCTL> exit
To stop the listeners:
$ lsnrctl
LSNRCTL for SCO System V/386: Version 2.3.3.0.0 - Production on 23-FEB-98 20:43:20
Copyright (c) Oracle Corporation 1994. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=magic.com)(PORT=1521)(COMMUNITY=UK_SUP_TCPIP))
The command completed successfully
LSNRCTL> exit
If you have a DNS setup which doesn't return the IP address for the hostname specified then starting and stopping the listener can take some time (2-3 mins. dependant on the DNS timeout variable). If this is the case, don't worry, be patient.
Next Previous Contents