Document ID: 33589.1
Subject: Sqlnet V2.x on windows NT.
Last Modified: 09 Jan 97
Author: COAKSHOT
GENERAL OVERVIEW
----------------
To enable Oracle client and server configurations to talk to each
other over a network, Oracle provides a networking product called sqlnet which
resides on top of an existing network layer. It does NOT replace
the underlying protocol which allows packet communication between the
client and server.
Sqlnet 2 comes bundled with Oracle Workgroup Server and Enterprise Server.
In order for the clients to talk to the server, it is necessary for
the server to have a listener which waits for the clients to talk to it.
Once a connection is established, two way communication starts, enabling
a client to modify and read data from the database as if it was local
as opposed to networked.
CONFIG FILES INVOLVED
---------------------
Sqlnet 2 is configured and controlled via three files.
LISTENER.ORA
SQLNET.ORA
TNSNAMES.ORA
Listener.ora is the file which the listener control program reads when started.
(See below for details on how to do this).
Sqlnet.ora is used for configuration and tracing parameters for both
server AND client.
Tnsnames.ora is read by the client and points the client to the correct host
and tells it which protocol to use etc. (it can also be used for a loopback
test on the server - more details below).
WINDOWS NT SAMPLE FILES
-----------------------
These can be located under %ORACLE_HOME%\NETWORK\ADMIN\SAMPLE
NOTE: The environment variable ORACLE_HOME has a default value
of C:\ORANT as found in the registry on the server.
On a client, ORACLE_HOME has a default value of C:\ORAWIN
found in oracle.ini under the clients windows directory.
In order for these sample files to be read, they need to exist in the
%ORACLE_HOME%\NETWORK\ADMIN directory.
CREATING A LISTENER ON THE SERVER
---------------------------------
Firstly we need to create a listener on the server.
From the %ORACLE_HOME%\NETWORK\ADMIN\SAMPLE directory,
copy the LISTENER.ORA, SQLNET.ORA and TNSNAMES.ORA up
one level to the %ORACLE_HOME%\NETWORK\ADMIN directory.
Edit Listener.ora with notepad (or some other text editor).
This is the point at which you need to decide which protocol(s)
you want to use. Below is a working listener.ora which
you may wish to take a subset of or add other protocols to.
There is no need to do configuration for protocols which you do
not need or have.
There are 3 protocols listed in the ADDRESS = section.
SPX - Novell Netware spx protocol
NMP - Named Pipes NT protocol
TCP - Tcp/ip protocol
################
# Filename......: listener.ora
# Node..........:
# Date..........:
################
LISTENER =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = SPX)
(SERVICE = SPX_SERV)
)
(ADDRESS =
(PROTOCOL = NMP)
(SERVER = WINNT35)
(PIPE = PIPE1)
)
(ADDRESS =
(PROTOCOL = TCP)
(Host = 123.4.56.78)
(Port = 1521)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = ADMIN
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = ORCL)
)
)
PASSWORDS_LISTENER = (oracle)
NOTE: Things that may be local to your configuration.
For SPX
(SERVICE = xxx) Where xxx is your chosen spx service.
For Named Pipes
(SERVER = xxx) Where xxx is the name of your NT server.
(PIPE = xxx) Where xxx is the name of a communication pipe.
For TCP/IP
(Host = xxx) Where xxx is the servers ip address.
(Port = xxx) Where xxx is the port number to listen on.
You may also need to change (SID_NAME = xxx) if connecting to
a non default database.
Sqlnet.ora is also read upon starting the listener and in the example
below, it doesn't serve much purpose.
Sqlnet serverside tracing is set to OFF
Expire time to 0 (effectively disabling DCD - Dead Connection Detection).
The other two parameters are commented out for simplicity.
################
# Filename......: sqlnet.ora
# Node..........:
# Date..........:
################
TRACE_LEVEL_SERVER = OFF
sqlnet.expire_time = 0
#names.default_domain = world
#name.default_zone = world
STARTING THE LISTENER
---------------------
Supplied with the Oracle workgroup/enterprise server software there
is a program called LSNRCTL.EXE which resides in the %ORACLE_HOME\BIN
directory.
For ease of use either create an icon for it or run it from the command prompt.
It should show
LSNRCTL>
To start the listener just type
LSNRCTL> start
You should see something similar to the following.
Starting tnslsnr: please wait...
Service OracleTNSListener start pending.
Service OracleTNSListener started.
TNSLSNR for WINDOWS 32: Version 2.2.2.1.0 - Production
System parameter file is C:\ORANT\network\admin\listener.ora
Log messages written to C:\ORANT\network\log\listener.log
Trace information written to C:\ORANT\network\trace\listener.trc
Listening on:
(DESCRIPTION=(CONNECT_TIMEOUT=10)(ADDRESS=(PROTOCOL=NMP)(SERVER=WINNT35)(
PIPE=PIPE1)))
Listening on: (DESCRIPTION=(CONNECT_TIMEOUT=10)(ADDRESS=(PROTOCOL=TCP)(Host=123.
4.56.78)
(Port=1521)))
Listening on: (DESCRIPTION=(CONNECT_TIMEOUT=10)(ADDRESS=(PROTOCOL=SPX)(Service=
SPX_SERV)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for WINDOWS 32: Version 2.2.2.1.0 - Production
Start Date 06-FEB-96 21:44:14
Uptime 0 days 0 hr. 0 min. 23 sec
Trace Level admin
Security ON
SNMP OFF
Listener Parameter File C:\ORANT\network\admin\listener.ora
Listener Log File C:\ORANT\network\log\listener.log
Listener Trace File C:\ORANT\network\trace\listener.trc
Services Summary...
ORCL has 1 service handlers
The command completed successfully
TESTING USING A LOOPBACK
------------------------
With a listener up and running on the server you could set up a client
on the network and connect over sqlnet to the server. Rather than
doing this we can execute a loopback on the server which will test the
connection without involving a physical client.
To enable this, we need to have a look at TNSNAMES.ORA.
################
# Filename......: tnsnames.ora
# Node..........: local.world
# Date..........: 24-MAY-94 13:23:20
################
#spx connection
spx_test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = SPX)
(SERVICE = SPX_SERV)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
#tcp/ip connection
tcp_test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = 123.4.56.78)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
#Named Pipes connection
nmp_test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = NMP)
(SERVER = WINNT35)
(PIPE = PIPE1)
)
)
(CONNECT_DATA = (SID = ORCL)
)
)
The above file is split up into 3 sections and has a lot of similarities
with listener.ora. This is where we specify what our 'connect strings' or
'sqlnet aliases' are going to be.
When a product that wants to use sqlnet connects, we need to pass certain
information over.
Let's take the tcp_test alias from the above example.
This specifies which host and port to connect to and which instance it
wishes to use. (via the SID = part).
To test this, run sqldba71.exe and just type
connect system/manager@tcp_test
It should come back with
Connected.
NOTE: SYSTEM/MANAGER can be any valid oracle username/password.
This proves that the listener can be found and contacted from the network.
WINDOWS CLIENTS
---------------
Assuming the above works, setting up a client should be reasonably easy.
Assuming the client has the required protocols and sqlnet installed
correctly, all that needs to be done is the following.
Copy the files TNSNAMES.ORA and SQLNET.ORA from the server to the client
and place them in the %ORACLE_HOME%\NETWORK\ADMIN directory.
NOTE: ORACLE_HOME defaults to C:\ORAWIN
On windows NT this does not have to be a seperate physical client as a client
connection can be simulated by taking advantage of the WOW subsystem. (Windows
On Windows).
There is a handy utility called nettest.exe found in %ORACLE_HOME%\BIN
which prompts for an oracle username, an oracle password and your chosen
connect string. eg tcp_test. This will respond with "Ping successful"
or an error.
To make this sqlnet connect string transparent to a user, one can place
an entry into oracle.ini called LOCAL. eg LOCAL=tcp_test
This will mean that a client connect will only need to supply the username
and password.