| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: TNS:packet writer failure
Article-ID: <Note:28788.1> Revision: 0 PUBLIC Circulation: ** PUBLIC ** Creator: SWALSH Folder: platform.OpenVMS.VAX Topic: SQL*Net Subject: SQL*Net V2.0 Configuration - Customer Overview Modified: 11 Jul 97 12:24:08
SQLNET V2.0 CONFIGURATION OVERVIEW
Steve Walsh, Support Consultant
Oracle Worldwide Support
[This document is largely OpenVMS specific]
Introduction
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
that 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:
<lsnr-name> = (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/IP, but they both reside in the same file.
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 = <sid>)
(PROGRAM = 'full-file-spec:command-file')
)
(SID_DESC =
(SID_NAME = <sid>)
(PROGRAM = 'full-file-spec:command-file')
)
.
.
.
)
and so on
The program file, is basically a command procedure (like the
ORDN<sid>.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 service, use the following description structure:
For DECnet...
<service-name> =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = DECNET)
(NODE = <node-name>)
(OBJECT = <lsnr-object>)
)
)
(CONNECT_DATA =
(SID = <sid>)
)
)
For TCP/IP...
<service-name> =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = <node-name>)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = <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@<service-name>
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_<listener-name> and
LOG_FILE_<listener-name> 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_<listener-name>=OFF|USER|ADMIN
USER enables limited tracing and ADMIN enables detailed tracing
Optionally, TRACE_DIRECTORY_<listener-name> and
TRACE_FILE_<listener-name> 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_<db-name>.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=<pid>
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_<listener name>, 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_<listener name> = DISK$1:[ORACLE.LOGS]NETLOG.LOG This can also be caused by giving the file extention when not required, such as LOG_FILE_<listener name> = 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:[ORACLE7.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
TRACE_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=...)
$ PID = F$GETJPI("","PID")
$ TAB = "TNS_"+PID
$ ON ERROR THEN GOTO CLEANUP
$!
$ 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
$ DEFINE ORA_PLSQL ORACLE$DISK:[ORACLE7.PROD.RDBMS] !for plsql
$ DEFINE 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]
$ DEFINE TNS_ADMIN ORACLE$DISK:[ORACLE7.PROD.NETWORK.ADMIN]
$!
$ ORASRV := $ORA_SYSTEM:SRV.EXE !define server
$ ORASRV "(LOCAL=NO)" !invoke server
$!
$! Make sure that we remove the server's LNM...
$!
$CLEANUP:
$ STS = '$STATUS'
$ ON ERROR THEN CONTINUE
$ DEASSIGN/USER/TABLE=LNM$SYSTEM_DIRECTORY 'TAB'
$ EXIT STS
NOTE: From SQLNET 2.1.3 each SQLNET connection creates a logical name table of the form TNS_<pid>.
The sample program file above includes the workaround for BUG 274879.1 where this logical name table never gets deassigned. Without this workaround, you will evenutally run out of PAGED POOL. (PAGEDYN SYSGEN parameter) Received on Tue Nov 24 1998 - 03:40:51 CST
 |
 |