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
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) ) )
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/220.127.116.11.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
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
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 18.104.22.168.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/22.214.171.124.0/bin/tnslsnr: please wait... TNSLSNR for SCO System V/386: Version 126.96.36.199.0 - Production System parameter file is /home/oracle/188.8.131.52.0/network/admin/listener.ora Log messages written to /home/oracle/184.108.40.206.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 220.127.116.11.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/18.104.22.168.0/network/admin/listener.ora Listener Log File /home/oracle/22.214.171.124.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 126.96.36.199.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.