| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SOS!! SQL*NET on a VAX.
graham.long_at_gecm.com wrote:
>
> --
> --
> ...phil
> I will shortly be installing SQL*NET on a VAX 4000-300
> using the DECNET protocol via Pathworks OpenVMS (Netware). By
> use of the provided manuals and information gleaned from
> MetaLinks I know the files and process required, nameley
> TSNAMES.ORA, SQLNET.ORA and LISTNER.ORA, and the LISTENER
> process.
>
> Graham.
While I was there, I was able to get this off the Oracle Canada Web.
Hope it helps.
Greg...
Conference : platform.digital.vax_openvms
Topic : SQL*Net
Note : SQL*NET V2 DECNET CONFIGURATION FILE EXAMPLES
Date Created : 17-OCT-94
Date Modified : 17-OCT-94
Note ID : 13849
Document:105600.022
Updated:08-JUN-94
Category:VMS
Document ID: 105600.022
Title: SQL*Net V2 DECnet Configuration File Examples
Creation Date: 12 May 1994
Last Revision Date: 12 May 1994
Revision Number: 0
Product: SQL*NET
Product Version: V2
Platform: MIDRANGE
Information Type: SOLUTION
Impact: HIGH
Abstract: This bulletin provides samples of the three
SQL*Net V2
configuration files that are required for
running
SQL*Net V2 DECnet.
Keywords: SQL;NET;DECNET;LISTENER;CONFIGURATION
-------------------------------------------------------------------------------
SQL*Net V2 DECnet Configuration File Examples
The major problem in getting SQL*Net V2 to work is getting the SQL*Net
V2
listener process running. The major cause for not being able to start up
the SQL*Net V2 listener process is improper syntax in one of the SQL*Net
V2
configuration files. If the V2 listener process dies immediately after
issuing the LSNRCTL START command, you probably have a syntax error in
the
LISTENER.ORA file.
This bulletin provides samples of the three SQL*Net V2 configuration
files that
are required for running SQL*Net V2 DECnet.
In the samples below, all items in CAPITAL letters must be changed to
match
your specific installation.
Sample TNS_ADMIN:LISTENER.ORA:
(address = (p
rotocol=decnet)
(node=NODENAME)
(object=LISTEN1)))
sid_list_listener = (sid_list=
(sid_desc=(sid_name=SIDNAME)
(program='DISK1:[ORACLE7.DB_DBNAME]ORASRV_NETV2.COM')))
trace_level_listener = admin
trace_directory_listener = ora_root:[network.trace]
Sample TNS_ADMIN:TNSNAMES.ORA:
(address = (protocol = decnet)
(node = NODENAME)
(object = LISTEN1))
(connect_data = (sid=SIDNAME)))
Sample Server Command Procedure
DISK1:[ORACLE7.DB_DBNAME]ORASRV_NETV2.COM:
$! $! Run instance-specific ORAUSER.COM file $! $ @DISK1:[ORACLE7.DB_DBNAME]ORAUSER_DBNAME.COM $! $! Define server process image $! $ orasrv := $ora_system:srv.exe $! $! Start ORACLE SQL*Net server process $! $ orasrv "(local=no)" $ exit
Additional Notes:
Conference : platform.digital.vax_openvms
Topic : SQL*Net
Note : SQL*Net V2.0 Configuration - Customer Overview
Date Created : 31-MAY-96
Date Modified : 31-MAY-96
Note ID : 28788
SQLNET V2.0 CONFIGURATION OVERVIEW
Steve Walsh, Support Consultant
Oracle Worldwide Support
[This document is largely OpenVMS specific]
Introduction
the relevant HOSTS. and SERVICES. files are present and configured
o Ideally, it should only be configured using the Forms30 Netconfig
tool, but this document mainly explains how to do it manually, in
detail
o Net1 and Net2 can coexist on the same node, but neither can communicate
with each other directly
o Note that it is very particular about it's syntax in configuration files
SQLNET V2.0 architecture
SQLNET.ORA
LISTENER.ORA
TNSNAMES.ORA
- SQLNET.ORA
o This contains certain parameter settings for SQLNET (see
Appendix
A)
o It allows the setting of trace and log levels, for both the
client
and the server.
o There are four of these general levels, identified by a
mnemonic:
OFF
USER
DEBUG
ADMIN
o The TRACE_FILE_xxxxx parameter specifies the name of the file
th
at will be created, when trace information is written.
The .TRC on the end is automatically generated, so need not be
specified. The same applies to log files (.LOG)
o There is also a parameter to control IPC (Inter-Process
Communication). The default for this parameter is ON. It is
always best to leave it out of the file, to ensure it is on,
unless you are debugging.
Automatic IPC chooses between VAX/VMS Mailboxes or Unix Pipes,
depending upon the platform.
- LISTENER.ORA
o this file specifies the configuration of the listener (see
Appendix B)
Wherever the word LISTENER appears in this file, it is
referring
to the name of the listener. LISTENER is the default name
o Firstly the LISTENER.ORA has a command to specify the listener
name and its capabilities:
= (ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = DECNET)
(NODE = node-name)
(OBJECT = object-name)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = node-name)
(PORT = 1521)
)
)
AS can be seen, there is a slight difference between DECnet
and
TCP
o Comments are signified by hashes (#)
o When the listener is started, any DECnet objects specified are
created automatically, so none of this has to be performed
beforehand
o The port number for TCP/IP under SQLNET 2 is 1521 and although
generally accepted, is not an official allocation. Therefore,
if this port is already in use, another port number could be
utilised.
Again, nothing further needs to be done with this, the
listener
identifies and then listens on this port when it is started
o There are several control parameters that should be specified.
These are as follows:
STOP_LISTENER = YES
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
o Next, all valid SIDs that clients may connect to, must be
listed,
complete with information for the listener on what to execute
to
create the connection
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = )
(PROGRAM = 'full-file-spec:command-file')
)
(SID_DESC =
(SID_NAME = )
(PROGRAM = 'full-file-spec:command-file')
)
.
.
.
)
and so on
The program file, is basically a command procedure (like the
ORDN.COM file in SQLNET V1), that is invoked by the SQLNET
server process that is created by the listener, when the
client
connects (see Appendix D)
o Finally, TRACE and LOG levels are specified for the listener,
giving directory locations to put files etc.
- To control the listener, there is a utility called LSNRCTL
(listener control). This can be used to start, stop and check
the
status of the listener. The following commands may be used:
$LSNRCTL START
$LSNRCTL STOP
$LSNRCTL STATUS
Other commands are available. For a full list of all commands,
just type
$LSNRCTL HELP
and this will give a brief help list of all commands and their
syntax
o This file contains logical definitions such as ORACLE_SID and
ORA_RDBMS to allow the client process to create an Oracle
environment suitable for communicating with the Oracle kernel
o Ultimately, the Oracle server executable is identified and
then
invoked
- TNSNAMES.ORA
o This file defines the 'services' or sid-mappings that are
required for connection to a database. This file is used by
clients or servers when acting as clients (see Appendix C)
o Hashes (#) represent comments
o To define a servi
For DECnet...
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = DECNET)
(NODE = )
(OBJECT = )
)
)
(CONNECT_DATA =
(SID = )
)
)
For TCP/IP...
=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = )
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = )
)
)
o Multiple addresses can be specified for a service, thus giving
it
a choice. For example, both a DECnet and TCP/IP address could
be
defined for a service, thus if DECnet was down, the client
could
still gain access via TCP/IP. Addresses are attempted in the
order
they appear in the address descriptor
o The TNSNAMES.ORA should be copied in its entirety to ALL
participating clients, to allow them to select their services.
It is NOT necessary for a listener to be running on the client
node, in order for them
to connect. SQLNET V2 will read the
TNSNAMES.ORA on their behalf, to identify the selected
service,
when a connection request is issued by the client.
However, a listener may be running on the client if it acts as
a
server for other clients. This will not affect outgoing
connections
- Issuing a connection request is very simple and transparent
under
SQLNET V2. All that is required is a simple connect string
containing the service name that the client wishes to be
connected
to:
$SQLPLUS username/password@
Provided this service name exists in the TNSNAMES.ORA on the
node
issuing the request (client or server), SQLNET V2 will have a
good
attempt at making the connection
The SQL*Net V2.0 Configuration Tool
$NCFW NET_CONF/NET_CONF -C VT220:V220 (or similar)
Note the documentation bug, that states the command is NET_CONF!
Also, the tool MUST be executed from within the NCFW directory,
since it expects to see the forms there
- The configuration tool uses SQLFORMS30, and may need to be
generated before use. Refer to the SQLNET V2.0 IUG for further
details on this
- Note that early versions of the tool generate the LISTENER.ORA
parameters CONNECT_TIMEOUT_LISTENER and
STARTUP_WAIT_TIME_LISTENER
with a value of 'i'. This is, of course, incorrect and the
values
should be changed appropriately
- This is as far as this document is intended to specify the use
of
the config tool
Logging and Tracing
Optionally, LOG_DIRECTORY_ and
LOG_FILE_ can be used to explicity identify the
required values. Otherwise O/S specific defaults are used
- Tracing is set for the listener using the following parameter in
the LISTENER.ORA file:
TRACE_LEVEL_=OFF|USER|ADMIN
USER enables limited tracing and ADMIN enables detailed tracing
Optionally, TRACE_DIRECTORY_ and
TRACE_FILE_ can be used to explicity identify the
required values. Otherwise O/S specific defaults are used
- Logging/tracing for the server process is not enabled by default
and can be implemented via keywords in the SQLNET.ORA file:
Optionally, LOG_DIRECTORY_SERVER, LOG_FILE_SEVER,
TRACE_DIRECTORY_SERVER and TRACE_FILE_SERVER can be used to
explicity identify the required values. Otherwise O/S specific
defaults are used
- Logging/tracing for the client process is not enabled by default
and can be implemented via keywords in the SQLNET.ORA file:
Optionally, LOG_DIRECTORY_CLIENT, LOG_FILE_CLIENT,
TRACE_DIRECTORY_CLIENT and TRACE_FILE_CLIENT can be used to
explicity identify the required values. Otherwise O/S specific
defaults are used
Hints and Tips for Net V2 configurations
o Always use a cut-down version of the PROGRAM file. Do NOT allow it
to invoke the database-specific ORAUSER_.COM, since this
is a very large file and may result in time-out problems. The
sample in Appendix D should be enough to initiate the connection
o Ensure that the LOGIN.COM for the user that owns (i.e. STARTS)
the
LISTENER process, contains the following (or equivalent) line as
the first command:
$ IF F$MODE() .EQS. "NETWORK" .OR. F$MODE() .EQS.
"OTHER" -
THEN EXIT
This ensures that when a connection is made, all the usual
setting
of terminal types, setting up if symbols and so on is not
performed.
Many login scripts are vast and if the process has to wade
through
all these commands first, the listener may timeout the
connection
before the client process has even had a chance to attempt
anything
o Check that this command
is also in the SYLOGIN.COM. Many SYLOGINs
have vast quantities of user setup in them and these, too, may cause
timeout problems
o Ensure the user that is used to start the listener is set up
appropriately and has the required access and privileges to
create
detached processes. The main privilege required for this is
DETACH,
but additionally, LOG_IO and PRMMBX are required for newer
implementations of SQL*Net V2. Plus all the usual Oracle DBA
user
privileges must be set and the protection and ownership of files
configured appropriately
o VMS errors reported in logfiles or trace files can be translated
if
the value is even, by using the VMS EXIT command, for example:
$ EXIT 2312
%SYSTEM-W-NOSUCHDEV, no such device available
o If the listener or server process dies before any tracing
information can be obtained, sometimes further details may be
acquired by using the VMS Accounting utility to check process
termination statuses. For example:
$ ACCOUNTING/FULL/IDENT=
Common SQL*Net V2 errors & their likely causes
o ORA 12203, TNS:unable to connect to destination
This is often followed by:
ORA 12560, TNS:protocol adapter error
TNS 530, Protocol adapter error
VMS-nnnn Unknown system error
where nnnn is a VMS operating system error number
This is usually caused by some kind of underlying system problem, such
as the TCP/IP stack not being correctly configured, or file protection
on a directory etc.
The VMS error will often give the exact cause
o Subprocesses spawned by the listener detached process, enter a
MUTEX
process state
often caused by a lack of process resources for the listener process &
hence the VMS user that started the listener The usual resources to check for are Bytlm & Tqelm
o ORA 12535, TNS:operation timed out
this is often associated with a listener timeout. Try increasing the
listener.ora parameter CONNECT_TIMEOUT_, or set it to zero to wait indefinitely
o ORA 12500, TNS:listener failed to bequeath connection
This is a very generic error & covers a multitude of sins. A non-exhaustive list includes: syntax errors, instructions at the innermost level of parentheses split across more than 1 line & invalid
directory specifications in the listener.ora or tnsnames.ora, or an invalid/non-existent PROGRAM= file, or one that contains syntax errors
The most common cause of this is some form of syntax error or an invalid directory/file specification. Check also that all sids & connection details are correct for the system configuration
o ORA 12545, TNS:name lookup failure
often followed by:
ORA 12560, TNS:protocol adapter error
TNS 515, Name lookup failure
VMS-2 Unknown system error 2, no such file or directory
This is usually a result of an invalid directory specification or using
a full directory specification where only a file name is required, e.g. LOG_FILE_ = DISK$1:[ORACLE.LOGS]NETLOG.LOG This can also be caused by giving the file extention when not required,
such as LOG_FILE_ = NETLOG.LOG, since it assumes .LOG as default. Another possible cause is privileges of the user starting the
listener. Ensure that DEFPRIV is the same as PRIV in the UAF record for the VMS user
o ORA 12546, TNS:permission denied
TNS 525, Insufficient privilege for operation
As per the accompanying text in the Oracle Network Products Messages Manual, this is usually caused by platform-specific privileges The ones to check for SQLnet V2 are: DETACH, LOG_IO & PRMMBX in addition to the standard Oracle DBA required privileges (as documented
in the Oracle7 for Alpha AXP/VAX OpenVMS Installation Guide.
o TNS 1150, The address of the specified listener name is incorrect
This is usually attributed to having a mismatch between the parenthesised lists in the config files, i.e. more open '(' than closed ')'
o ORA 12546, TNS:permission denied
with:
ORA 12560, TNS:protocol adapter error
TNS 516, Permission denied
The most likely cause of this is that the Oracle account being used to
start the listener does not have sufficient privileges to create network objects, thus not allowing the listener to create objects dynamically for DECnet connections
o ORA 12538, TNS
:no such protocol adapter
ORA 12560, TNS:protocol adapter error
TNS 508, No such protocol adapter
As the associated message text in the messages guide states, the
protocol adaptor is not linked in.
Therefore, check via the NetConfig parameters in ORACLEINS, as to
whether the adaptor (net v2 options) required is installed & if not,
enable it & then relink the entire Oracle kernel i.e. ALL products
o VMS-nnnn Unknown system error
This type of problem is actually an issue with the underlying O/S configuration, for example file protections, invalid logical name etc.
Check the VMS error message to see exactly what the problem is. This
should give enough information to trace the problem. For instance, if
the error message is %SHOW-S-NOTRAN, no translation for logical name (the name will probably not be passed through), then try looking at any
directory specifications & ensure they are all valid for a detached process, i.e. process-level logicals will not be sufficient. This can
often be a problem within the file pointed to by PROGRAM= in the listener.ora
o ORA 12500, TNS:listener failed to bequeath connection
ORA 12547, TNS:lost contact
TNS 517, Lost contact
This error can be quite awkward to trace. It often ends up being some
kind of underlying protocol or O/S issue Sometimes it is as simple as an obscure file protection problem - other
times is is far harder to resolve
o ORA 3112, host string syntax error
This error is naturally usually as a result of typing an incorrect connect string, but if the connect string is syntactically correct, is
often also a reference to the fact that the specified adaptor has not
been linked into the oracle kernel correctly. Check ORA_UTIL:NETCONFIG.FIG to ensure all appropriate adaptors are linked in
o ORA 12545, TNS:name lookup failure
ORA 12560, TNS:protocol adapter error
TNS 515, Name lookup failure
The obvious cause of this error is that the address parameters in the
listener.ora & tnsnames.ora are incorrect. Usually it is the node name
that is specified incorrectly.
However, this can also occur when starting the listener if it is
performed from inside the interactive interface, rather than
directly
from the command line, or there may be a version mismatch somewhere
Appendix A - SQLNET.ORA (example)
###########
# FILENAME: sqlnet.ora
# NETWORK.: UK_VAX_NET
# SERVICE.: NA
###########
TRACE_LEVEL_CLIENT = OFF
TRACE_DIRECTORY_CLIENT =
ORACLE$DISK:[ORACLE7.PROD.NETWORK.TRACE]
TRACE_FILE_CLIENT = CLIENT
LOG_LEVEL_CLIENT = 0
LOG_DIRECTORY_CLIENT = ORACLE$DISK:[ORACLE7.PROD.NETWORK.LOG]
LOG_FILE_CLIENT = CLIENT
TRACE_LEVEL_SERVER = 0
TRACE_DIRECTORY_SERVER =
ORACLE$DISK:[ORACLE7.PROD.NETWORK.TRACE]
TRACE_FILE_SERVER = SERVER
LOG_LEVEL_SERVER = 0
LOG_DIRECTORY_SERVER = ORACLE$DISK:[ORACL
E7.PROD.NETWORK.LOG]
LOG_FILE_SERVER = SERVER
AUTOMATIC_IPC = ON
Appendix B - LISTENER.ORA (example)
###########
# FILENAME: listener.ora
# TIME....: 94-12-22 04:39:12
# NETWORK.: NETWORK
# NODE....: UKAA09
# SERVICE.: LISTENER
###########
LISTENER =
(ADDRESS_LIST =
(ADDRESS=
(PROTOCOL=IPC)
(KEY= prod)
)
(ADDRESS=
(PROTOCOL=IPC)
(KEY= test)
)
(ADDRESS =
(PROTOCOL = DECNET)
(NODE = ukaa09)
(OBJECT = LSNR)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = ukaa09)
(PORT = 1521)
)
)
STOP_LISTENER = YES
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = prod)
(PROGRAM=ORACLE$DISK:[ORACLE7.PROD.NETWORK.ADMIN]PROD_NET2.COM)
)
(SID_DESC =
(SID_NAME = test)
(PROGRAM=ORACLE$DISK:[ORACLE7.TEST.NETWORK.ADMIN]TEST_NET2.COM)
)
)
LOG_LEVEL_LISTENER = OFF
LOG_DIRECTORY_LISTENER = ORACLE$DISK:[ORACLE7.PROD.NETWORK.LOG]
LOG_FILE_LISTENER = LISTENER
TRACE_LEVEL_LISTENER = OFF
TRAC
E_DIRECTORY_LISTENER = ORACLE$DISK:[ORACLE7.PROD.NETWORK.TRACE]
TRACE_FILE_LISTENER = LISTENER
Appendix C - TNSNAMES.ORA (excerpt)
###########
# FILENAME: tnsnames.ora
# TIME....: 94-12-22 04:39:12
# NETWORK.: NETWORK
# NODE....: UKAA09
# SERVICE.: C_NET_DECNET
###########
prod =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = UKAA09_DECNET)
(PROTOCOL = DECNET)
(NODE = ukaa09)
(OBJECT = LSNR)
)
)
(CONNECT_DATA =
(SID = prod)
)
)
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = UKAA09_TCPIP)
(PROTOCOL = TCP)
(HOST = ukaa09)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = test)
)
)
prod_ipc =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = prod)
)
)
(CONNECT_DATA =
(SID = prod)
)
)
Appendix D - PROD_NET2.COM (from PROGRAM=...)
$ DEFINE ORA_SID PROD
$ DEFINE ORA_SYSTEM ORACLE$DISK:[ORACLE7.PROD.RDBMS] !srv here
$ DEFINE ORA_RDBMS ORACLE$DISK:[ORACLE7.PROD.RDBMS] !for rdbms
errors
$ DEFINE ORA_COMMON ORACLE$DISK:[ORACLE7.PROD.RDBMS] !sga here $ DEFINE ORA_PLS ORACLE$DISK:[ORACLE7.PROD.RDBMS] !for plsql errors
$ DE
FINE ORA_SLAX ORACLE$DISK:[ORACLE7.PROD.RDBMS] !for plsql errors
$ DEFINE ORA_NETWORK ORACLE$DISK:[ORACLE7.PROD.NETWORK]
$ DEFINE ORA_DUMP ORACLE$DISK:[ORACLE7.PROD.DB_PROD.TRACE]
$ DEFINE ORA_ARCHIVE ORACLE$DISK:[ORACLE7.PROD.DB_PROD.TRACE]
$ ORASRV := $ORA_SYSTEM:SRV.EXE !define
server
$ ORASRV "(LOCAL=NO)" !invoke
server
As a side note, make sure to add the "on error" line and its corresponding "cleanup" code to the net*.com file. If you don't, you go end up with logical tables sticking around. You can use the following to spot this:
SHOW LOG TNS* /TABLE=LNM$SYSTEM_DIRECTORY Greg...
$!*************
$! NET2_TST.COM
$!*************
$!
$!
$ PID = F$GETJPI("","PID") ! Pick up our process
id
$ TAB = "TNS_"+PID ! This is our LNM
table
$ ON ERROR THEN GOTO CLEANUP $! $ DEFINE ORA_SID TST !The databaseSID
$! $! $! Don't use concealed logicals such as ORA_ROOT in here $! $ DEFINE ORA_SYSTEM DISK$ORACLE:[ORACLE7.V71524.RDBMS] !SRV.EXE here $ DEFINE ORA_RDBMS DISK$ORACLE:[ORACLE7.V71524.RDBMS] !messagefiles
$ DEFINE ORA_COMMON DISK$ORACLE:[ORACLE7.V71524.RDBMS] ! -- ditto -- $ DEFINE ORA_PLS DISK$ORACLE:[ORACLE7.V71524.RDBMS] ! -- ditto -- $ DEFINE ORA_SLAX DISK$ORACLE:[ORACLE7.V71524.RDBMS] ! -- ditto -- $ DEFINE ORA_NETWORK DISK$ORACLE:[ORACLE7.V71524.NETWORK] !Nice to have $ DEFINE ORA_DUMP DISK$ORACLE:[ORACLE7.V71524.DB_TST.TRACE] $ DEFINE ORA_ARCHIVE DISK$ORACLE:[ORACLE7.V71524.DB_TST.TRACE] $ DEFINE TNS_ADMIN DISK$ORACLE:[ORACLE7.V71524.NETWORK.ADMIN] !Musthave
$! $ ORASRV := $ORA_SYSTEM:SRV.EXE $ ORASRV "(LOCAL=NO)" $! $! $! $! Make sure we remove the server's LNM ... $! $CLEANUP: $ STS = '$STATUS' $ ON ERROR THEN CONTI
 |
 |