Oracle9i Net Services Administrator's Guide Release 2 (9.2) Part Number A96580-01 |
|
Oracle Net Services provides methods for understanding and resolving network problems through the use of log and trace files. These files keep track of the interaction between network components as errors occur. Evaluating this information will help you to diagnose and troubleshoot even the most complex network problems.
This chapter describes common network errors and outlines procedures for resolving them. It also describes methods for logging and tracing error information to diagnose and troubleshoot more complex network problems. This chapter contains these topics:
If an attempt to make a basic peer-to-peer (single protocol network) connection returns an ORA
Error
, this section may help you diagnose the cause of the problem.
Any underlying fault, noticeable or not, is reported by Oracle Net Services with an error number or message that is not always indicative of the actual problem. This section helps you determine which parts of Oracle Net Services do function properly rather than the parts that do not work. It also helps you to decide in which of the following categories the fault belongs:
Testing the various network layers progressively should in most cases uncover any problem.
Note: You may need assistance from your server administrator to follow the instructions in this section. |
Answer the following questions:
If you answered YES to any of the preceding questions/statements, then skip this section and continue to "Client Diagnostics".
If you are unsure, or answered NO to any of the preceding questions, then continue.
Diagnosing Oracle Net Services on the server involves the following tasks:
To check that the database is up, login to the database and connect with a valid username and password. For example:
SQLPLUS system/manager
A message appears, confirming that you are connected with the database. If you receive the following errors, ask your Database Administrator to assist you:
To perform a loopback test from the server to the database:
listener.ora
, tnsnames.ora
, and sqlnet.ora
files exist in the correct locations, as described in "Localized Configuration File Support".At this point, you know the serverside listener works properly, because you could verify at least one of the following statements:
To perform diagnostics on the client:
On UNIX you can use the ADAPTERS utility to verify protocol support. On the database server, run the adapters 'which oracle'
command from $ORACLE_HOME/bin
to display the protocol support, naming methods, and security options linked with the oracle
executable. The adapters
utility displays output similar to the following:
Oracle Net transport protocols linked with ./oracle are: IPC BEQ TCP/IP SSL RAW Oracle Net naming methods linked with ./oracle are: Local Naming (tnsnames.ora) Oracle Directory Naming Oracle Host Naming Oracle Names Server Naming NIS Naming Oracle Advanced Security options linked with ./oracle are: RC4 40-bit encryption RC4 56-bit encryption RC4 128-bit encryption RC4 256-bit encryption DES40 40-bit encryption DES 56-bit encryption 3DES 112-bit encryption 3DES 168-bit encryption AES 128-bit encryption AES 192-bit encryption AES 256-bit encryption MD5 crypto-checksumming SHA crypto-checksumming (for FIPS) SHA-1 crypto-checksumming Kerberos v5 authentication CyberSAFE authentication RADIUS authentication ENTRUST authentication
On the client, run the adapters
command from $ORACLE_HOME/bin
to display the configured Oracle protocol support, naming methods, and security options. The ADAPTERS utility displays output similar to the following:
Installed Oracle Net transport protocols are: IPC BEQ TCP/IP SSL RAW Installed Oracle Net naming methods are: Local Naming (tnsnames.ora) Oracle Directory Naming Oracle Host Naming Oracle Names Server Naming NIS Naming Installed Oracle Advanced Security options are: RC4 40-bit encryption RC4 56-bit encryption RC4 128-bit encryption RC4 256-bit encryption DES40 40-bit encryption DES 56-bit encryption 3DES 112-bit encryption 3DES 168-bit encryption AES 128-bit encryption AES 192-bit encryption AES 256-bit encryption MD5 crypto-checksumming SHA-1 crypto-checksumming Kerberos v5 authentication CyberSAFE authentication RADIUS authentication
Protocol | Verify that you can... |
---|---|
TCP/IP |
Use terminal emulation or file transfer utilities, (PING, FTP, TELNET) from the client to the database server. |
Named Pipes |
tnsnames.ora
and the sqlnet.ora
files exist in the correct locations.
If you have any other working client computers connecting to the selected Oracle database, back up your existing files and copy both the working tnsnames.ora
and sqlnet.ora
files from the working computer onto the non-working client workstations. This eliminates the possibility of errors in the files.
http://support.oracle.com
for a specific diagnostics bulletin on the error receivedDue to the complexity of network communications, network errors may originate from a variety of sources, for a variety of reasons. If an error occurs, applications such as SQL*Plus, that depend on network services from Oracle Net Services, will normally generate an error message.
A list of the most common network error messages follows:
ORA-12154: TNS:could not resolve service name
ORA-12198: TNS:could not find path to destination
ORA-12203: TNS:unable to connect to destination
ORA-12224: TNS:no listener
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
ORA-12520: TNS:listener could not find available handler for requested type of server
ORA-12521: TNS:listener could not resolve INSTANCE_NAME given in connect descriptor
ORA-12533: TNS:illegal ADDRESS parameters
ORA-12545: TNS:name lookup failure
ORA-12560: TNS:protocol adapter error
ORA-3113: TNS:End of file on communication channel
ORA-3121: No interface driver connection - function not performed
See Also:
Oracle9i Database Error Messages for a complete listing of error messages |
Cause: Oracle Net could not locate the net service name specified in the tnsnames.ora
configuration file.
Action: Perform these steps:
tnsnames.ora
file exists.
See Also:
"Localized Configuration File Support" for configuration file location information |
tnsnames.ora
file.tnsnames.ora
file, verify that the net service name specified in your connect string is mapped to a connect descriptor.sqlnet.ora
file.sqlnet.ora
file contains a NAMES.DEFAULT_DOMAIN
parameter. If this parameter does not exist, you must specify the domain name in your connect string.Cause: Oracle Net could not locate the database service name or net service name specified in the directory server.
Action: Perform these steps:
See Also:
Chapter 8, "Setting Up Directory Server Usage" for directory setup instructions |
sqlnet.ora
file includes the following entry:
NAMES.DIRECTORY_PATH=(ldap, other_naming_methods
)
Cause: The client cannot find the desired database.
Action: Perform these steps:
ADDRESS
parameters in the connect descriptor.tnsnames.ora
file is stored in the correct directory.
See Also:
"Localized Configuration File Support" for configuration file location information |
lsnrctl
LSNRCTL> STATUS [
listener_name]
listener_name
is the name of the listener defined in the listener.ora
file. It is not necessary to identify the listener if you are using the default listener, named LISTENER
.
If the output indicates the listener is not running, try starting it with the command:
LSNRCTL> START [
listener_name]
Cause: ORA-12203
error is a generic error that often shields secondary errors.
Action: Check the latest sqlnet.log
file for secondary ORA
messages.
Cause: An invalid net service name was supplied in the connect string.
Action: Verify that the net service name supplied in the connect string exists in the tnsnames.ora
file or directory server and the ADDRESS
information for that net service name is valid. Ask yourself the following questions:
Cause: The tnsnames.ora
file is not located in the proper directory.
Action: Make sure that the tnsnames.ora
file is in the proper location.
See Also:
"Localized Configuration File Support" for configuration file location information |
Cause: The (HOST=
server_name
)
parameter for TCP/IP addresses is not consistent on the client and server computers.
Action: Ensure that the values for these parameter are the same on the server and client.
For TCP/IP, make sure that the HOST
parameter in listener.ora
on the server and in the tnsnames.ora
file on the client point to the same name, or at least to names that are then translated to the same IP address by each system. This is especially important for servers with multiple IP addresses assigned to the various network interfaces on the server.
Cause: The destination system's listener is not listening.
Action: Verify that the remote system's listener is running. Enter:
lsnrctl
LSNRCTL> STATUS [
listener_name]
listener_name
is the name of the listener defined in the listener.ora
file. It is not necessary to identify the listener if you are using the default listener, named LISTENER
.
If the output indicates the listener is not running, try starting it with the command:
LSNRCTL> START [
listener_name]
Cause: There are underlying network transport problems.
Action: Use the utilities supplied with the underlying networking protocol to verify that the protocol itself is functional. For example, with TCP/IP, try to ping
the remote system.
Cause: The incorrect Oracle protocol for the selected networking protocol is installed. A missing protocol support driver usually produces the following errors in the sqlnet.log
or any client trace file:
Action: Check that you have installed the appropriate Oracle protocol. On UNIX, you can run the ADAPTERS utility.
Cause: The connection request could not be completed because the listener is not running.
Action: Perform these steps:
Cause: The protocol specific parameters in the ADDRESS
section of the designated connect descriptor are incorrect.
Action: Correct the protocol address.
See Also:
Oracle9i Net Services Reference Guide for correct protocol syntax |
Cause: The service name specified in the connect descriptor is incorrect, or the database service is not registered with the listener.
Action: Perform these steps:
SERVICE_NAME
specified in the connect descriptor is correct.SERVICES
command to see what services are currently registered with the listener.
Cause: The type of service handler requested by the client is incorrect or not registered for the requested SERVICE_NAME
/INSTANCE_NAME
, or the database instance is not registered with the listener.
Action: If you suspect the problem is the wrong type of service handler, perform these steps:
(server=
value
)
is set is in the connect descriptor, ensure that the value is set to the appropriate service handler type for the database, that is, dedicated
for dedicated server or shared
for dispatchers. You can use the Listener Control utility SERVICES
command to see what service handlers are currently registered with the listener.
USE_DEDICATED_SERVER
is set to ON
in the sqlnet.ora
file, then ensure the database is configured to use dedicated servers. If it is not, set this parameter to off
.Cause: The INSTANCE_NAME
in the connect descriptor is incorrect, or the database instance is not registered with the listener.
Action: Perform these steps:
SERVICES
command to see what instances are currently registered with the listener.
Cause: The listener on the remote node cannot be contacted.
Action: Perform these steps:
ADDRESS
in the tnsnames.ora
file and the listener.ora
file is correct.lsnrctl
LSNRCTL> STATUS [
listener_name]
listener_name
is the name of the listener defined in the listener.ora
file. It is not necessary to identify the listener if you are using the default listener, named LISTENER
.
If the output indicates the listener is not running, try starting it with the command:
LSNRCTL> START [
listener_name]
Action: The listener was unable to start a process connecting the user to the database server.
Cause: Perform these steps:
Cause: An error has occurred on the database server.
Action: Check the alert_
sid.log
on the server. The location of alert_
sid.log
is specified by the BACKGROUND_DUMP_DEST
initialization parameter.
Cause: An unexpected end of file was processed on the communication channel. This may be an indication that the communications link may have gone down at least temporarily; it may indicate that the server has gone down.
Action: You may need to modify your retransmission count.
Cause: A SQL*Net version 1 prefix was erroneously used in the connect string.
Action: Do not use the following prefixes in the connect string.
Cause: The username and password were specified from a client computer that had no local Oracle database installed.
Action: Specify a connect string.
Directory naming issues associated with connectivity errors such as ORA-12154
, ORA-12203
, or ORA-12224
for database service or net service name entries in a directory server require analysis of the data. You can analyze the data contained within a directory server with the ldifwrite
command line tool.
ldifwrite
enables you to convert all or part of the information residing in a directory server to LDAP Data Interchange Format (LDIF). The ldifwrite
tool performs a subtree search, including all entries following the specified distinguished name (DN), including the DN itself.
The ldifwrite
tool syntax is as follows:
ldifwrite -cTable 17-1 ldapwrite Argumentsnet_service_name/database_service
-bbase_DN
-fldif_file
The following example writes all the directory naming entries under dc=us,dc=acme,dc=com
to the output1.ldi
file:
ldifwrite -c ldap -b "dc=us,dc=acme,dc=com" -f output.ldif
Errors in the region load operation will be reported in the Oracle Names server log file (names.log
). These errors may range from failure to contact the directory server to errors with the query for all, some, or one of the records.
Some directories, such as Oracle Internet Directory, have limits on ldapsearch
operations. There are settings in the directory server that limit the number of objects returned by the search and the amount of time spent performing a search.
The size limit specifies how many objects can be returned from a search. The default limit is 1000. If this limit is exceeded, you will see the following errors in the names.log
file:
NNO-00062: cannot load domain data from configuration database NNO-00850: Error: LDAP query returns 4
You can also use the ldapsearch
command line tool to mimic what the Oracle Names server will do when it loads its region. The following syntax shows loading data from DN (dn:dc=acme,dc=com)
:
ldapsearch -p 389 -h host
-b "dc=acme,dc=com"
"(objectclass=orclNetService)(objectclass=orclService)"
After returning the allowed number of object, ldapsearch
returns the following error message:
ldapsearch: Sizelimit exceeded
You can modify the size limit using the following sample LDIF file output. Enter the appropriate DN. In addition, set orclsizelimit
high enough to allow for the number of databases defined in the region in the directory server, with a little room for future expansion.
dn: changetype: modify replace: orclsizelimit orclsizelimit: 5000
The time limit specifies the amount of time that can be spent performing a search. The default time limit is 10 seconds. Ten seconds is sufficient to query for roughly 1,000 object, which is sufficient for most searches. If the query exceeds the time limit, you will see the following errors in the names.log
file:
NNO-00062: cannot load domain data from configuration database NNO-00850: Error: LDAP query returns 105
You can modify the time limit using the following sample LDIF file output. Enter the appropriate DN.
dn: changetype: modify replace: orcltimelimit orcltimelimit: 20
The time limit is applied at both the directory server and API levels. Therefore, in addition to resetting the directory server time limit, you will also need to set the TIMEOUT
subparameter of NAMES.ADMIN_REGION
. For example:
NAMES.ADMIN_REGION= (REGION= (TIMEOUT=20) (TYPE=ldap) (HOST=ldap_server) (PORT=389) (SUBTREE=(BASE=dc=acme,dc=com)))
Here are some tips you may find helpful when you are having difficulty diagnosing network problems:
This eliminates any internal lookup problems and make the connection slightly faster.
For example, change the
(HOST=
server_name
) line in the tnsnames.ora
file with the internet address, for example (HOST=198.32.3.5
).
Perform a loopback test on the server as described in "Testing Configuration on the Database Server". If the test passes, ftp
the tnsnames.ora
and sqlnet.ora
files to the client.
If it is a wide area network (WAN), identify any intermediate systems that may not work correctly. If all computers are fine, the problem may be a timing issue.
Timing issues are associated with ORA-12203
, ORA-12535
, or ORA-12547
errors in the client log files.
To resolve this, try speeding up the connection by using exact addresses instead of names and increase the CONNECT_TIMEOUT_
listener_name
parameter in the listener.ora
file. The default value for this parameter is 10 seconds.
SQL*Plus may work, but CASE tools may not. If you determine the problem is a data volume issue, try to transfer a large (5 MB) file with the base connectivity.
Here are some questions to ask yourself when diagnosing a problem:
If one computer works and another does not, and you are confident that the same software (Oracle and third-party products) is installed, on each computer, swap out the network cables, if they are close enough, to see if the problem moves. If it does move, it indicates that the problem has something to do with the client/server connection and is not local to the PC.
Sniffers and LAN analyzers are useful for intermittent failing connections or detecting time-outs and re-sent packets. You can also see what side of the conversation is waiting for a response.
Oracle Net Services provide detailed information about the source and context of problems as they arise. This information is generated and stored in log and trace files. The process of logging and tracing error information will help you to diagnose and resolve network problems.
All errors encountered in Oracle Net Services are appended to a log file for evaluation by a network or database administrator. The log file provides additional information for an administrator when the error message on the screen is inadequate to understand the failure. The log file, by way of the error stack, shows the state of the software at various layers.
To ensure that all errors are recorded, logging cannot be disabled on clients or Names Servers. Furthermore, only an administrator may replace or erase log files. The log file for the listener also includes Audit Trail information about every client connection request, as well as most listener control commands.
This section contains these topics:
Log files provide information contained in an error stack. An error stack refers to the information that is produced by each layer in an Oracle communications stack as the result of a network error. Figure 17-1 depicts the relationship among error stack components and Oracle Net.
The error stack components in Figure 17-1 are described in Table 17-2.
Table 17-2 Error Stack ComponentsAs an example, suppose that a user of a client application tries to establish a connection with a database server using Oracle Net and TCP/IP, and the user enters:
sqlplus scott/tiger@hrserver.com
The following error displays:
ORA-12203: TNS:Unable to connect to destination
This message indicates that the connection to the server failed because the database could not be contacted. Although the application displays only a one-line error message, an error stack that is much more informative is recorded in the log file by the network layer.
On the client side, a sqlnet.log
file (Figure 17-2) contains an error stack corresponding to the ORA-12203
error.
***********************************************************
Fatal OSN connect error 12203, connecting to: (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=) (HOST=lala)(USER=sviavant)))(ADDRESS_LIST=(ADDRESS= (PROTOCOL=ipc)(KEY=trace))(ADDRESS=(PROTOCOL=tcp) (HOST=lala)(PORT=1521)))) VERSION INFORMATION: TNS for SunOS: Oracle Bequeath NT Protocol Adapter for SunOS: Unix Domain Socket IPC NT Protocol Adaptor for SunOS: TCP/IP NT Protocol Adapter for SunOS: Tracing to file: /home/sviavant/trace_admin.trc Tns error struct: nr err code: 12203 TNS-12203: TNS:unable to connect to destination ns main err code: 12541 TNS-12541: TNS:no listener ns secondary err code: 12560 nt main err code: 511 TNS-00511: No listener nt secondary err code: 61 nt OS err code: 0
Each Oracle Net Services component produces its own log file. Table 17-3 provides the default log file names and lists the components that generate the log files.
Table 17-3 Oracle Net Log FilesLog File | Component |
---|---|
|
Client or database server |
|
Listener |
|
Oracle Names Server |
|
Oracle Connection Manager CMGW process |
|
Oracle Connection Manager CMADMIN process |
Parameters that control logging, including the type and amount of information logged, as well as the location where the files are stored, are set in the configuration file of each network component as described in Table 17-4.
Table 17-4 Oracle Net Log ParametersNetwork Component | Configuration Files |
---|---|
Client |
|
Database Server |
|
Listener |
|
Oracle Names Server |
|
Oracle Connection Manager |
|
This section contains these topics:
See Also:
Oracle9i Net Services Reference Guide for more information about these parameters |
Table 17-5 describes the log parameters settings that can be set in the sqlnet.ora
file.
Table 17-6 describes the log parameters settings that can be set in the listener.ora
file.
Table 17-7 describes the log parameters settings that can be set in the names.ora
file.
Table 17-8 describes the log parameters settings that can be set in the cman.ora
file.
Logging parameters for the sqlnet.ora
file, listener.ora
files and names.ora
file can be set with the Oracle Net Manager. The cman.ora
file logging parameters must be set manually.
To set logging parameters:
Log File | Set logging parameters here... |
---|---|
|
|
|
|
|
Logging can be set during runtime of control utilities. Note that setting logging with a control utility will not set parameters in the *.ORA
files; the setting is only valid for the session of the control utility:
SET
LOG_FILE
and SET
LOG_DIRECTORY
commands from the Listener Control utility.SET
LOG_LEVEL
command from the Oracle Connection Manager control utility.SET
LOG_FILE
_NAME command from the Oracle Names Control utility, or set logging settings through Oracle Net Manager.
To set tracing for an Oracle Names server with Oracle Net Manager:
To use a log file to diagnose a network error:
This section describes what is recorded in the listener log file, including:
The listener log file contains audit trail information that enables you to gather and analyze network usage statistics, as well as information indicating the following:
RELOAD
, START
, STOP
, STATUS
, or SERVICES
command issued by the Listener Control utilityThe Audit Trail formats text into the following fields:
Timestamp * Connect Data * [Protocol Info] * Event * [SID | Service] * Return Code
Properties of the Audit Trail are as follows:
*
).See Also:
"Resolving the Most Common Error Messages for Oracle Net Services" for the most common Oracle Net errors or Oracle9i Database Error Messages for a complete listing of error messages |
Typical output to the log file upon a RELOAD
request follows.
14-JAN-2002 00:29:54 * (connect_ data=(cid=(program=)(host=sales-server)(user=jdoe))(command=stop)(arguments=64)( service=listener)(version=135290880)) * stop * 0
Typical output to the log file upon a connection request follows.
14-JAN-2002 15:28:58 * (connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server) (user=jdoe))) * (address=(protocol=tcp)(host=144.25.185.246)(port=41349)) * establish * sales.us.acme.com * 0
You can use Audit Trail information to view trends and user activity by first storing it in a table and then collating it into a report format. To import the data into a table, use an import utility such as SQL*Loader.
The listener records service registration events. During service registration, the instance background process PMON process provides the listener with information about the following:
The following service registration-related events are recorded in the listener.log
file:
The service registration events are formatted into the following fields:
Timestamp * Event * Instance Name * Return Code
Properties of service registration fields are as follows:
*
).See Also:
|
The following example shows a log file with service registration events. Notice how the listener is able to receive a client request after a successful service_register
event, but is unable to receive client requests after a service_died
event.
------------------------------- 14-JAN-2002 15:28:43 * service_register * sales * 0 14-JAN-2002 15:28:43 * service_register * sales * 0 14-JAN-2002 15:28:58 * (connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server) (user=jdoe))) * (address=(protocol=tcp)(host=144.25.185.246)(port=41349)) * establish * sales.us.acme.com * 0 14-JAN-2002 15:38:44 * service_update * sales * 0 14-JAN-2002 15:38:44 * service_update * sales * 0 14-JAN-2002 15:48:45 * service_update * sales * 0 14-JAN-2002 15:48:45 * service_update * sales * 0 14-JAN-2002 15:50:57 * (connect_data=(service_ name=sales.us.acme.com)(cid=(program=)(host=sales-server)(u ser=jdoe))) * (address=(protocol=tcp)(host=144.25.185.246)(port=41365)) * establish * sales.us.acme.com * 0 14-JAN-2002 15:51:26 * service_died * sales * 12537 14-JAN-2002 15:51:26 * service_died * sales * 12537 14-JAN-2002 15:52:06 * (connect_data=(service_ name=sales.us.acme.com)(cid=(program=)(host=sales-server)(u ser=jdoe))) * (address=(protocol=tcp)(host=144.25.185.246)(port=41406)) * establish * sales.us.acme.com * 12514 TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor --------------------------------
The listener records direct hand-off events to dispatchers. These events are formatted into the following fields:
Timestamp * Presentation * Handoff * Error Code
Properties of direct hand-off fields are as follows:
*
).See Also:
"Resolving the Most Common Error Messages for Oracle Net Services" for the most common Oracle Net errors or Oracle9i Database Error Messages for a complete listing of error messages |
A direct hand-off event in the log file is shown in the following example.
21-JAN-2002 10:54:55 * oracle.aurora.net.SALESHttp2 * handoff * 0
Oracle Connection Manager generates two types of log files: one for its CMGW gateway process (cman
_
pid
.log
) and one for its CMADMIN administrative process (cmadm
_
pid
.log
).
Figure 17-3 and Figure 17-4 show examples of the log files.
(TIMESTAMP=20-JAN-2002 18:03:10)(EVENT=10)(VERSION=8.1.6.0.0) (TIMESTAMP=20-JAN-2002 18:03:10)(EVENT=36)(rule_list= (rule=(src=spcstn)(dst=x)(srv=x)(act=accept))) (TIMESTAMP=20-JAN-2002 18:03:10)(EVENT=32)(PARAMETER_LIST=(MAXIMUM_ RELAYS=1024)(RELAY_STATISTICS=no)(AUTHENTICATION_LEVEL=0)(LOG_LEVEL=1)(SHOW_TNS_ INFO=no)(ANSWER_TIMEOUT=0)(MAXIMUM_CONNECT_DATA=1024)(USE_ASYNC_ CALL=yes)(TRACING=no)(TRACE_DIRECTORY=default)(MAX_FREELIST_BUFFERS=0)) (TIMESTAMP=20-JAN-2002 18:03:10)(EVENT=34)(ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1610)(QUEUESIZE=32))) (TIMESTAMP=20-JAN-2002 18:03:12)(EVENT=38)(COMMAND=2) (TIMESTAMP=20-JAN-2002 18:03:27)(EVENT=26)(RLYNO=0)(SRC=(ADDRESS=(PROTOCOL=tcp)(HOST=spcstn.us.acme.c om)(PORT=34758)))(DST=(ADDRESS=(PROTOCOL=tcp)(HOST=144.25.187.89)(PORT=1581))) (TIMESTAMP=20-JAN-2002 18:03:43)(EVENT=28)(RLYNO=0)(SINCE=20-JAN-2002 18:03:27)(STATISTICS=(IN=(BYTES=0)(PACKETS=0)(DCDS=0)(OUT=(BYTES=0)(PACKETS=0)(D CDS=0)))
(TIMESTAMP=20-JAN-2002 18:03:09)(EVENT=Sent Admin Status to UI) (TIMESTAMP=20-JAN-2002 18:03:10)(EVENT=CMan Registration)
See Also:
"Analyzing Listener Log Files" on page 17-26 |
The cman_
pid
.log
on UNIX and cman
pid
.log
on Windows NT reports events using event code numbers. The event code reported depends upon the log level set with the LOG_LEVEL
parameter in the cman.ora
file or with the Oracle Connection Manager Control utility command SET
LOG_LEVEL
. This section explains what each of these event codes represents.
Code | Description |
---|---|
10 |
Gateway is starting up |
12 |
Gateway is shutting down |
14 |
Listening on TNS address(es) |
18 |
See Also: "Reasons for Event Code 18" |
20 |
See Also: "Reasons for Event Code 20" |
26 |
Relay is now open |
28 |
Relay is now closed |
30 |
Statistics report |
32 |
< |
34 |
< |
36 |
< |
38 |
Oracle Connection Manager Control utility command |
40 |
Oracle Connection Manager Control utility command refused because the gateway is busy |
42 |
Dead connection detected |
44 |
Relay has timed out |
11 |
Bad < |
13 |
Bad < |
15 |
Bad < |
23 |
Bad Oracle Connection Manager Control utility record |
25 |
Command line argument is too long |
27 |
Memory allocation failure |
29 |
TNS error |
31 |
TNS error while processing Oracle Connection Manager Control utility requests |
The answer can fail due to the following:
Code | Description |
---|---|
1 |
Timed out |
2 |
Connect data buffer is too small |
3 |
Refused by TNS |
4 |
TNS packet checksum error |
The incoming call can be refused if:
Code | Description |
---|---|
102 |
Answering in-coming call |
104 |
Making out-going call |
105 |
Accepting in-coming call |
106 |
Rule match report |
Code | Description |
---|---|
202 |
Call will block (no asynchronous TNS support) |
204 |
See Also: "Reasons for Event Code 204" |
206 |
Buffer contains leftover data |
The relay can be blocked due to the following:
Code | Description |
---|---|
1 |
Waiting for writer to be ready |
2 |
Waiting for writer to clear backlog |
3 |
WOULDBLOCK error on receive |
4 |
WOULDBLOCK or PARTIAL error on send |
5 |
Repeated WOULDBLOCK or PARTIAL send error |
Code | Description |
---|---|
302 |
Read this many bytes |
304 |
Wrote this many bytes |
306 |
Wrote this many bytes on retry |
Tracing produces a detailed sequence of statements that describe network events as they are executed. Tracing an operation enables you to obtain more information on the internal operations of the components of Oracle Net Services than is provided in a log file. This information is output to files that can be evaluated to identify the events that led to an error.
CAUTION: Tracing uses a large amount of disk space and may have a significant impact upon system performance. Therefore, you should enable tracing only when necessary. |
This section contains topics:
Each Oracle Net component produces its own trace file. Table 17-14 provides the default trace file names and lists the components that generate the trace files.
Table 17-14 Oracle Net Trace FilesParameters that control tracing, including the type and amount of information trace, as well as the location where the files are stored, are set in the configuration file of each network component as described in Table 17-15.
Table 17-15 Oracle Net Trace ParametersTrace Parameters | Configuration Files |
---|---|
Client |
|
Server |
|
Listener |
|
Oracle Names Server |
|
Oracle Connection Manager processes |
|
This sections contains these topics:
See Also:
Oracle9i Net Services Reference Guide for more information about these parameters |
Table 17-16 describes the trace parameters settings that can be set in the sqlnet.ora
file.
You can also manually add the following TNSPING utility tracing parameters described in Table 17-17 to sqlnet.ora
. The TNSPING utility determines whether or not a service (such as a database, an Oracle Names Server, or other TNS services) on a Oracle Net network can be successfully reached.
Table 17-18 describes the trace parameters settings that can be set in the listener.ora
file.
Table 17-19 describes the trace parameters settings that can be set in the names.ora
file.
Table 17-20 describes the trace parameters settings that can be set in the cman.ora
file.
sqlnet.ora
, listener.ora
and names.ora
logging parameters can be set with the Oracle Net Manager. The cman.ora
tracing parameters must be set manually.
To set tracing parameters:
Trace File | Instruction |
---|---|
|
|
|
|
|
Tracing can be set during a runtime of a control utility. Note that setting tracing with a control utility will not set parameters in the *.ora
files; the setting is valid only for the session of the control utility:
SET TRC_FILE
, SET TRC_DIRECTORY
and SET TRC_LEVEL
commands from the Listener Control utility.SET TRACE_LEVEL
and SET TRACE_DIRECTORY
commands from the Oracle Connection Manager Control utility.SET TRACE_FILE_NAME
and SET TRACE_LEVEL
commands from the Oracle Names Control utility, or set tracing settings through Oracle Net Manager.
To set tracing for an Oracle Names server with Oracle Net Manager:
Trace files can help Oracle Support Service diagnose and troubleshoot network problems.
This section explains how to perform basic analysis of trace files. The topics discussed include:
Oracle Net performs its functions by sending and receiving data packets.By specifying a trace level of support
, you can view the actual contents of the Oracle Net packet in your trace file. The order of the packet types sent and received will help you to determine how your connection was established.
Each line in the trace file begins with a procedure followed by a message. Following each procedure is a line of hexadecimal data representing actual data. The actual data that flows inside the packet is sometimes viewable to the right of the hexadecimal data.
Next is a list of the Oracle Net packet keywords and descriptions of the types of packets they represent:
Keyword | Packet Type |
---|---|
|
Connect |
|
Accept |
|
Refuse |
|
Resend |
|
Data |
|
Control |
|
Marker |
For example, the following line describes a procedure called "nscon
" sending a NSPTCN
packet over the network:
nscon: sending NSPTCN packet
Each packet has a keyword that denotes the packet type. All packet types begin with the prefix "nsp
". It is helpful to remember this when reviewing trace files for specific packet information
Figure 17-5 provides typical packet information.
nscon: entry nscon: doing connect handshake... nscon: sending NSPTCN packet nspsend: entry nspsend: plen=187, type=1 nspsend: 187 bytes to transport nspsend:packet dump nspsend:00 BB 00 00 01 00 00 00 |........| nspsend:01 33 01 2C 0C 01 08 00 |.3.,....| nspsend:7F FF 7F 08 00 00 00 01 |........| nspsend:00 99 00 22 00 00 08 00 |..."....| nspsend:01 01 28 44 45 53 43 52 |..(DESCR| nspsend:49 50 54 49 4F 4E 3D 28 |IPTION=(| nspsend:43 4F 4E 4E 45 43 54 5F |CONNECT_| nspsend:44 41 54 41 3D 28 53 49 |DATA=(SI| nspsend:44 3D 61 70 33 34 37 64 |D=ap347d| nspsend:62 31 29 28 43 49 44 3D |b1)(CID=| nspsend:28 50 52 4F 47 52 41 4D |(PROGRAM| nspsend:3D 29 28 48 4F 53 54 3D |=)(HOST=| nspsend:61 70 32 30 37 73 75 6E |ap207sun| nspsend:29 28 55 53 45 52 3D 6D |)(USER=m| nspsend:77 61 72 72 65 6E 29 29 |warren))| nspsend:29 28 41 44 44 52 45 53 |)(ADDRES| nspsend:53 5F 4C 49 53 54 3D 28 |S_LIST=(| nspsend:41 44 44 52 45 53 53 3D |ADDRESS=| nspsend:28 50 52 4F 54 4F 43 4F |(PROTOCO| nspsend:4C 3D 74 63 70 29 28 48 |L=tcp)(H| nspsend:4F 53 54 3D 61 70 33 34 |OST=ap34| nspsend:37 73 75 6E 29 28 50 4F |7sun)(PO| nspsend:52 54 3D 31 35 32 31 29 |RT=1521)| nspsend:29 29 29 00 00 00 00 00 |))).....| nspsend: normal exit nscon: exit (0)
When there is a problem a connection, the error code is logged in the trace file. Figure 17-6 depicts typical trace file output for a failed SQL*Plus connection to a database server.
[22-JAN-2002 13:34:07:687] nsprecv: entry [22-JAN-2002 13:34:07:687] nsbal: entry [22-JAN-2002 13:34:07:687] nsbgetfl: entry [22-JAN-2002 13:34:07:687] nsbgetfl: normal exit [22-JAN-2002 13:34:07:687] nsmal: entry [22-JAN-2002 13:34:07:687] nsmal: 44 bytes at 0x132d90 [22-JAN-2002 13:34:07:687] nsmal: normal exit [22-JAN-2002 13:34:07:687] nsbal: normal exit [22-JAN-2002 13:34:07:687] nsprecv: reading from transport... [22-JAN-2002 13:34:07:687] nttrd: entry [22-JAN-2002 13:35:09:625] nttrd: exit [22-JAN-2002 13:35:09:625] ntt2err: entry [22-JAN-2002 13:35:09:625] ntt2err: Read unexpected EOF ERROR on 10 [22-JAN-2002 13:35:09:625] ntt2err: exit [22-JAN-2002 13:35:09:625] nsprecv: transport read error [22-JAN-2002 13:35:09:625] nsprecv: error exit [22-JAN-2002 13:35:09:625] nserror: entry [22-JAN-2002 13:35:09:625] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0 [22-JAN-2002 13:35:09:625] nscon: error exit [22-JAN-2002 13:35:09:625] nsdo: nsctxrnk=0 [22-JAN-2002 13:35:09:625] nsdo: error exit [22-JAN-2002 13:35:09:625] nscall: unexpected response [22-JAN-2002 13:35:09:625] nsclose: entry [22-JAN-2002 13:35:09:625] nstimarmed: entry [22-JAN-2002 13:35:09:625] nstimarmed: no timer allocated [22-JAN-2002 13:35:09:625] nstimarmed: normal exit [22-JAN-2002 13:35:09:625] nsdo: entry [22-JAN-2002 13:35:09:625] nsdo: cid=0, opcode=98, *bl=0, *what=0, uflgs=0x440, cflgs=0x2 [22-JAN-2002 13:35:09:625] nsdo: rank=64, nsctxrnk=0 [22-JAN-2002 13:35:09:625] nsdo: nsctx: state=1, flg=0x4201, mvd=0 [22-JAN-2002 13:35:09:625] nsbfr: entry [22-JAN-2002 13:35:09:625] nsbaddfl: entry [22-JAN-2002 13:35:09:625] nsbaddfl: normal exit [22-JAN-2002 13:35:09:625] nsbfr: normal exit [22-JAN-2002 13:35:09:625] nsbfr: entry [22-JAN-2002 13:35:09:625] nsbaddfl: entry [22-JAN-2002 13:35:09:625] nsbaddfl: normal exit [22-JAN-2002 13:35:09:625] nsbfr: normal exit [22-JAN-2002 13:35:09:625] nsdo: nsctxrnk=0 [22-JAN-2002 13:35:09:625] nsdo: normal exit [22-JAN-2002 13:35:09:625] nsclose: closing transport [22-JAN-2002 13:35:09:625] nttdisc: entry [22-JAN-2002 13:35:09:625] nttdisc: Closed socket 10 [22-JAN-2002 13:35:09:625] nttdisc: exit [22-JAN-2002 13:35:09:625] nsclose: global context check-out (from slot 0) complete [22-JAN-2002 13:35:09:703] nsnadisc: entry [22-JAN-2002 13:35:09:703] nadisc: entry [22-JAN-2002 13:35:09:703] nacomtm: entry [22-JAN-2002 13:35:09:703] nacompd: entry [22-JAN-2002 13:35:09:703] nacompd: exit [22-JAN-2002 13:35:09:703] nacompd: entry [22-JAN-2002 13:35:09:703] nacompd: exit [22-JAN-2002 13:35:09:703] nacomtm: exit [22-JAN-2002 13:35:09:703] nas_dis: entry [22-JAN-2002 13:35:09:703] nas_dis: exit [22-JAN-2002 13:35:09:703] nau_dis: entry [22-JAN-2002 13:35:09:703] nau_dis: exit [22-JAN-2002 13:35:09:703] naeetrm: entry [22-JAN-2002 13:35:09:703] naeetrm: exit [22-JAN-2002 13:35:09:703] naectrm: entry [22-JAN-2002 13:35:09:703] naectrm: exit [22-JAN-2002 13:35:09:703] nagbltrm: entry [22-JAN-2002 13:35:09:703] nau_gtm: entry [22-JAN-2002 13:35:09:703] nau_gtm: exit [22-JAN-2002 13:35:09:703] nagbltrm: exit [22-JAN-2002 13:35:09:703] nadisc: exit [22-JAN-2002 13:35:09:703] nsnadisc: normal exit [22-JAN-2002 13:35:09:703] nsbfr: entry [22-JAN-2002 13:35:09:703] nsbaddfl: entry [22-JAN-2002 13:35:09:703] nsbaddfl: normal exit [22-JAN-2002 13:35:09:703] nsbfr: normal exit [22-JAN-2002 13:35:09:703] nsmfr: entry [22-JAN-2002 13:35:09:703] nsmfr: 2256 bytes at 0x130508 [22-JAN-2002 13:35:09:703] nsmfr: normal exit [22-JAN-2002 13:35:09:703] nsmfr: entry [22-JAN-2002 13:35:09:703] nsmfr: 484 bytes at 0x1398a8 [22-JAN-2002 13:35:09:703] nsmfr: normal exit [22-JAN-2002 13:35:09:703] nsclose: normal exit [22-JAN-2002 13:35:09:703] nscall: connecting... [22-JAN-2002 13:35:09:703] nsclose: entry [22-JAN-2002 13:35:09:703] nsclose: normal exit [22-JAN-2002 13:35:09:703] nladget: entry [22-JAN-2002 13:35:09:734] nladget: exit [22-JAN-2002 13:35:09:734] nsmfr: entry [22-JAN-2002 13:35:09:734] nsmfr: 144 bytes at 0x132cf8 [22-JAN-2002 13:35:09:734] nsmfr: normal exit [22-JAN-2002 13:35:09:734] nsmfr: entry [22-JAN-2002 13:35:09:734] nsmfr: 156 bytes at 0x138e70 [22-JAN-2002 13:35:09:734] nsmfr: normal exit [22-JAN-2002 13:35:09:734] nladtrm: entry [22-JAN-2002 13:35:09:734] nladtrm: exit [22-JAN-2002 13:35:09:734] nscall: error exit [22-JAN-2002 13:35:09:734] nioqper: error from nscall [22-JAN-2002 13:35:09:734] nioqper: nr err code: 0 [22-JAN-2002 13:35:09:734] nioqper: ns main err code: 12537 [22-JAN-2002 13:35:09:734] nioqper: ns (2) err code: 12560 [22-JAN-2002 13:35:09:734] nioqper: nt main err code: 507 [22-JAN-2002 13:35:09:734] nioqper: nt (2) err code: 0 [22-JAN-2002 13:35:09:734] nioqper: nt OS err code: 0 [22-JAN-2002 13:35:09:734] niomapnserror: entry [22-JAN-2002 13:35:09:734] niqme: entry [22-JAN-2002 13:35:09:734] niqme: reporting NS-12537 error as ORA-12537 [22-JAN-2002 13:35:09:734] niqme: exit [22-JAN-2002 13:35:09:734] niomapnserror: returning error 12537 [22-JAN-2002 13:35:09:734] niomapnserror: exit [22-JAN-2002 13:35:09:734] niotns: Couldn't connect, returning 12537 [22-JAN-2002 13:35:10:734] niotns: exit [22-JAN-2002 13:35:10:734] nsbfrfl: entry [22-JAN-2002 13:35:10:734] nsbrfr: entry [22-JAN-2002 13:35:10:734] nsbrfr: nsbfs at 0x132d90, data at 0x132dc8. [22-JAN-2002 13:35:10:734] nsbrfr: normal exit [22-JAN-2002 13:35:10:734] nsbrfr: entry [22-JAN-2002 13:35:10:734] nsbrfr: nsbfs at 0x1248d8, data at 0x132210. [22-JAN-2002 13:35:10:734] nsbrfr: normal exit [22-JAN-2002 13:35:10:734] nsbrfr: entry [22-JAN-2002 13:35:10:734] nsbrfr: nsbfs at 0x12d820, data at 0x1319f0. [22-JAN-2002 13:35:10:734] nsbrfr: normal exit [22-JAN-2002 13:35:10:734] nsbfrfl: normal exit [22-JAN-2002 13:35:10:734] nigtrm: Count in the NI global area is now 1 [22-JAN-2002 13:35:10:734] nigtrm: Count in the NL global area is now 1
The most efficient way to evaluate error codes is to find the most recent nserror
entry logged, as the session layer controls the connection. The most important error messages are the ones at the bottom of the file. They are the most recent errors and the source of the problem with the connection.
For information about the specific return codes, use the Oracle UNIX error tool oerr
, by entering the following at any command line:
oerr tns error_number
As an example, consider the following nserror
entry logged in the trace file shown in Figure 17-6:
[22-JAN-2002 13:35:09:625] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
Using oerr
, you can find out more information about return codes 12537 and 507. (Bold denotes user input.)
oerr tns 12537 12537, 00000, "TNS:connection closed" // *Cause: "End of file" condition has been reached; partner has disconnected. // *Action: None needed; this is an information message. oerr tns 507 00507, 00000, "Connection closed" // *Cause: Normal "end of file" condition has been reached; partner has // disconnected. // *Action: None needed; this is an information message.
Oracle Net Services provides a tool called the Trace Assistant to help you understand the information provided in trace files by converting existing lines of trace file text into a more readable paragraph. Note that the Trace Assistant runs against only a level 16
(support
) Oracle Net Services trace file.
This section contains the following topics:
To run the Trace Assistant, enter the following at any command line prompt:
trcasst [options
] <filename
>
The options are described in Table 17-21.
Table 17-21 Trace Assistant SyntaxOption | Description |
---|---|
|
Displays error information. After the |
|
If a connection ID exists in the NS connect packet, then the output displays the connection IDs. Connection IDs are displayed as hexadecimal, eight-byte IDs. A generated ID is created by Trace Assistant if the packet is not associated with any connection, that is, the connect packet is overwritten in the trace file. This can occur with cyclic trace files. For each ID, the output lists the following:
Notes: |
|
Displays the trace for a particular ID from the Note: Only use this option with output from the |
|
Displays the amount and type of information to be output. After the
Note: As output for |
|
Oracle internal use only |
|
Displays the following statistical information:
|
If no options are provided, then the default is -odt -e0 -s
, providing detailed connectivity and TTC events, error level zero (0
), and statistics in the trace file.
Figure 17-7 shows how the Trace Assistant converts trace file information into a more readable format.
ntus2err: exit
ntuscni: exit
ntusconn: exit
nserror: entry
-<ERROR>- nserror: nsres: id=0, op=65, ns=12541, ns2=12560; nt[0]=511, nt[1]=2,
nt[2]=0
Figure 17-8 shows how the Trace Assistant converts the trace file information into a
more readable format with the -e1
option.
************************************************************************* * Trace Assistant * ************************************************************************* ntus2err: exit ntuscni: exit ntusconn: exit nserror: entry -<ERROR>- nserror: nsres: id=0, op=65, ns=12541, ns2=12560; nt[0]=511, nt[1]=2, nt[2]=0 /////////////////////////////////////////////////////////////// Error found. Error Stack follows: id:0 Operation code:65 NS Error 1:12541 NS Error 2:12560 NT Generic Error:511 Protocol Error:2 OS Error:0 NS & NT Errors Translation 12541, 00000 "TNS:no listener" // *Cause: The connection request could not be completed because the listener // is not running. // *Action: Ensure that the supplied destination address matches one of // the addresses used by the listener - compare the TNSNAMES.ORA entry with // the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to // go by way of an Interchange). Start the listener on the remote machine. / 12560, 00000 "TNS:protocol adapter error" // *Cause: A generic protocol adapter error occurred. // *Action: Check addresses used for proper protocol specification. Before // reporting this error, look at the error stack and check for lower level // transport errors.For further details, turn on tracing and reexecute the // operation. Turn off tracing when the operation is complete. / 00511, 00000 "No listener" // *Cause: The connect request could not be completed because no application // is listening on the address specified, or the application is unable to // service the connect request in a sufficiently timely manner. // *Action: Ensure that the supplied destination address matches one of // the addresses used by the listener - compare the TNSNAMES.ORA entry with // appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to go // by way of an Interchange. Start the listener on the remote machine. / /////////////////////////////////////////////////////////////// ************************************************************************* * Trace Assistant has completed * *************************************************************************
However, other errors may also exist within the trace file that were not logged from the nserror
function.
Trace Assistant also enables you to view data packets from both the Oracle Net and TTC communication layers. Trace Assistant offers you two options to view these packets:
Figure 17-9 shows summary information from the -oc
option. The output shows....
************************************************************************* * Trace Assistant * ************************************************************************* ---> Send 198 bytes - Connect packet Connect data length: 140 Connect Data: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)(CID=(PROGRAM=) (HOST=sales-server)(USER=joe)))) <--- Received 76 bytes - Redirect packet Redirect data length: 66 Redirect Data: (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) ---> Send 198 bytes - Connect packet Connect data length: 140 Connect Data: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)(CID=(PROGRAM=) (HOST=sales-server)(USER=joe)))) <--- Received 32 bytes - Accept packet Connect data length: 0 ---> Send 153 bytes - Data packet Native Services negotiation packet <--- Received 127 bytes - Data packet Native Services negotiation packet ---> Send 32 bytes - Data packet <--- Received 140 bytes - Data packet ************************************************************************* * Trace Assistant has completed * *************************************************************************
Note that the packets being sent or received have a prefix of "---> Send
nnn
bytes
" or "<--- Received
nnn
bytes
" showing that this node is sending or receiving a packet of a certain type and with nnn
number of bytes. This prefix enables you to determine if the node is the client or the database server. The connection request is always sent by the client, but received by the database server (or listener).
Figure 17-10 shows detailed information from the -od
option. The output shows all of the details sent along with the connect data in negotiating a connection.
************************************************************************* * Trace Assistant * ************************************************************************* ---> Send 241 bytes - Connect packet Current NS version number is: 311. Lowest NS version number can accommodate is: 300. Global options for the connection: can receive attention no attention processing Don't care Maximum SDU size:2048 Maximum TDU size:32767 NT protocol characteristics: Test for more data Test operation Full duplex I/O Urgent data support Generate SIGURG signal Generate SIGPIPE signal Generate SIGIO signal Handoff connection to another Line turnaround value :0 Connect data length :183 Connect data offset :58 Connect data maximum size :512 Native Services wanted NAU doing O3LOGON - DH key foldedin Native Services wanted NAU doing O3LOGON - DH key foldedin Cross facility item 1: 0 Cross facility item 2: 0 Connection id : Ox000059F70000004C (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)(SRVR=SHARED)(CID=(PROGRAM=) (HOST=sales-server)(USER=joe)))) <--- Received 76 bytes - Redirect packet Redirect data length: 66 (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) ---> Send 241 bytes - Connect packet Current NS version number is: 311. Lowest NS version number can accommodate is: 300. Global options for the connection: can receive attention no attention processing Don't care Maximum SDU size:2048 Maximum TDU size:32767 NT protocol characteristics: Test for more data Test operation Full duplex I/O Urgent data support Generate SIGURG signal Generate SIGPIPE signal Generate SIGIO signal Handoff connection to another Line turnaround value :0 Connect data length :183 Connect data offset :58 Connect data maximum size :512 Native Services wanted NAU doing O3LOGON - DH key foldedin Native Services wanted NAU doing O3LOGON - DH key foldedin Cross facility item 1: 0 Cross facility item 2: 0 Connection id : Ox000059F70000007A (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)(SRVR=SHARED)(CID=(PROGRAM=) (HOST=sales-server)(USER=joe)))) <--- Received 32 bytes - Accept packet Accepted NS version number is: 310. Global options for the connection: no attention processing Don't care Accepted maximum SDU size: 2048 Accepted maximum TDU size: 32767 Connect data length: 0 Native Services wanted NAU doing O3LOGON - DH key foldedin Native Services wanted NAU doing O3LOGON - DH key foldedin ---> Send 153 bytes - Data packet Native Services negotiation packet version#: 150999040 Service data packet #0 for Supervisor has 3 subpackets Subpacket #0: Version #150999040 Subpacket #1: 0000000000000000 Subpacket #2: DEADBEEF0003000000040004000100010002 Service data packet #1 for Authentication has 3 subpackets Subpacket #0: Version #150999040 Subpacket #1: UB2: 57569 Subpacket #2: FCFF Service data packet #2 for Encryption has 2 subpackets Subpacket #0: Version #150999040 Subpacket #1: 000000000000000000 Service data packet #3 for Data Integrity has 2 subpackets Subpacket #0: Version #150999040 Subpacket #1: 000000 <--- Received 127 bytes - Data packet Native Services negotiation packet version#: 135290880 Service data packet #0 for Supervisor has 3 subpackets Subpacket #0: Version #135290880 Subpacket #1: 0000 Subpacket #2: DEADBEEF00030000000200040001 Service data packet #1 for Authentication has 2 subpackets Subpacket #0: Version #135290880 Subpacket #1: FBFF Service data packet #2 for Encryption has 2 subpackets Subpacket #0: Version #135290880 Subpacket #1: UB1: 0 Service data packet #3 for Data Integrity has 2 subpackets Subpacket #0: Version #135290880 Subpacket #1: UB1: 0 .... ---> Send 11 bytes - Marker packet One data byte. Hex character sent over to the server: 2 <--- Received 11 bytes - Marker packet One data byte. Hex character sent over to the server: 2 <--- Received 155 bytes - Data packet ---> Send 25 bytes - Data packet <--- Received 11 bytes - Data packet ---> Send 13 bytes - Data packet <--- Received 11 bytes - Data packet ---> Send 10 bytes - Data packet Data Packet flags: End of file ************************************************************************* * Trace Assistant has completed * *************************************************************************
TTC handles requests such as open cursor, select rows, and update rows that are directed to the database server. All requests are answered by the database server. If you request to logon, a response is returned from the database server that the request was completed.
Summary information for TTC from the -ou
option is different from other displays in that it shows two packets on each line, rather than one. This is done to mirror the request/response pairings process by which TTC operates.
Figure 17-11 shows all of the details sent along with the connect data in negotiating a connection.
************************************************************************* * Trace Assistant * ************************************************************************* Bytes Bytes Sent Rcvd Send operation(TTIPRO) 32 140 Send operation(TTIDTY) 33 22 Get the session key (OSESSKEY) 229 145 Generic authentication call (OAUTH) 368 1001 Send operation(TTIPFN) 44 144 Send operation(TTIPFN) 36 16 Parse a statement (OSQL) # 1 SELECT USER FROM ... 47 100 Fast upi calls to opial7 (OALL7) # 1 130 111 Fetch row (OFETCH) # 1 21 137 Close cursor (OCLOSE) # 1 17 11 New v8 bundled call (OALL8) # 0 !Keep Parse BEGI... 156 145 Send operation(TTIPFN) 51 16 Parse a statement (OSQL) # 1 SELECT ATTRIBUTE,... 186 100 Fast upi calls to opial7 (OALL7) # 1 246 111 Fetch row (OFETCH) # 1 21 126 Close cursor (OCLOSE) # 1 17 11 Send operation(TTIPFN) 36 16 Parse a statement (OSQL) # 1 SELECT CHAR_VALUE... 208 100 Fast upi calls to opial7 (OALL7) # 1 130 111 Fetch row (OFETCH) # 1 21 126 Close cursor (OCLOSE) # 1 17 11 Send operation(TTIPFN) 36 16 Fast upi calls to opial7 (OALL7) # 1 !Keep Parse BEGI... 183 41 Send operation(TTIRXD) 20 111 Close cursor (OCLOSE) # 1 17 11 New v8 bundled call (OALL8) # 0 Parse Fetch SELE... 165 278 Send operation(TTIPFN) 51 16 Parse a statement (OSQL) # 1 commit 31 100 Execute statement (OEXEC) # 1 number of rows: 1 25 100 Close cursor (OCLOSE) # 1 17 11 Send operation(TTIPFN) 36 16 Fast upi calls to opial7 (OALL7) # 1 !Keep Parse BEGI... 183 41 Send operation(TTIRXD) 60 111 Close cursor (OCLOSE) # 1 17 11 Send operation(TTIPFN) 36 16 Fast upi calls to opial7 (OALL7) # 1 !Keep Parse BEGI... 183 41 Send operation(TTIRXD) 20 111 Close cursor (OCLOSE) # 1 17 11 New v8 bundled call (OALL8) # 0 Parse Fetch sele... 144 383 New v8 bundled call (OALL8) # 1 !Keep Fetch 121 315 Logoff off of Oracle (OLOGOFF) 13 11 ************************************************************************* * Trace Assistant has completed * *************************************************************************
Output is displayed in the following format:
description
TTC_message
cursor_number
SQL_statement
bytes_sent
bytes_received
On each line of the output, the first item displayed is the actual request made. The second item shows on what cursor that operation has performed. The third item is either a listing of the SQL command or flag that is being answered. The number of bytes sent and received are displayed at the far right. A flag can be one of the following:
!PL/SQL = Not a PL/SQL request COM = Commit IOV = Get I/O Vector DEFN = Define EXEC = Execute FETCH = Fetch CAN = Cancel DESCSEL = Describe select DESCBND = Describe Bind BND = Bind PARSE = Parse EXACT = Exact
Figure 17-12 shows detailed SQL information from the -ouq
option.
************************************************************************* * Trace Assistant * ************************************************************************* Bytes Bytes Sent Rcvd Send operation(TTIPRO) 32 140 Send operation(TTIDTY) 33 22 Get the session key (OSESSKEY) 229 145 Generic authentication call (OAUTH) 368 1001 Send operation(TTIPFN) 44 144 Send operation(TTIPFN) 36 16 Parse a statement (OSQL) # 1 47 100 SELECT USER FROM DUAL Fast upi calls to opial7 (OALL7) # 1 130 111 Fetch row (OFETCH) # 1 21 137 Close cursor (OCLOSE) # 1 17 11 New v8 bundled call (OALL8) # 0 !Keep Parse 156 145 BEGIN DBMS_OUTPUT.DISABLE; END; Send operation(TTIPFN) 51 16 Parse a statement (OSQL) # 1 186 100 SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DA TE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('S QL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LI KE USERID) Fast upi calls to opial7 (OALL7) # 1 246 111 Fetch row (OFETCH) # 1 21 126 Close cursor (OCLOSE) # 1 17 11 Send operation(TTIPFN) 36 16 Parse a statement (OSQL) # 1 208 100 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND ((UPPE R(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND ( UPPER(ATTRIBUTE) = 'ROLES') Fast upi calls to opial7 (OALL7) # 1 130 111 Fetch row (OFETCH) # 1 21 126 Close cursor (OCLOSE) # 1 17 11 Send operation(TTIPFN) 36 16 Fast upi calls to opial7 (OALL7) # 1 !Keep Parse 183 41 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); E ND; Send operation(TTIRXD) 20 111 Close cursor (OCLOSE) # 1 17 11 New v8 bundled call (OALL8) # 0 Parse Fetch 165 278 SELECT DECODE('A','A','1','2') FROM DUAL Send operation(TTIPFN) 51 16 Parse a statement (OSQL) # 1 31 100 commit Execute statement (OEXEC) # 1 number of rows: 1 25 100 Close cursor (OCLOSE) # 1 17 11 Send operation(TTIPFN) 36 16 Fast upi calls to opial7 (OALL7) # 1 !Keep Parse 183 41 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); E ND; Send operation(TTIRXD) 60 111 Close cursor (OCLOSE) # 1 17 11 Send operation(TTIPFN) 36 16 Fast upi calls to opial7 (OALL7) # 1 !Keep Parse 183 41 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); E ND; Send operation(TTIRXD) 20 111 Close cursor (OCLOSE) # 1 17 11 New v8 bundled call (OALL8) # 0 Parse Fetch 144 383 select * from dept New v8 bundled call (OALL8) # 1 !Keep Fetch 121 315 Logoff off of Oracle (OLOGOFF) 13 11 ************************************************************************* * Trace Assistant has completed * *************************************************************************
Figure 17-13 shows detailed TTC information from the -ot
option.
************************************************************************* * Trace Assistant * ************************************************************************* Set protocol (TTIPRO) Operation 01 (con) Send protocol version=6 Originating platform: SVR4-be-8.1.0 Set protocol (TTIPRO) Operation 01 (con) Receive protocol version=6 Destination platform: SVR4-be-8.1.0 Set datatypes (TTIDTY) Set datatypes (TTIDTY) Start of user function (TTIFUN) (OSESSKEY) Return opi parameter (TTIRPA) Start of user function (TTIFUN) (OAUTH) Return opi parameter (TTIRPA) Start of user function (TTIFUN) session operations 71 (O71SESOPN) (switch session) Return opi parameter (TTIRPA) Start of user function (TTIFUN) Get Oracle version/date string in new format (OVERSION) Return opi parameter (TTIRPA) Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning option JServer Release 9.2.0.1.0 - Production Start of user function (TTIFUN) session operations 71 (O71SESOPN) (switch session) Return opi parameter (TTIRPA) Start of user function (TTIFUN) Open a cursor (OOPEN) Return opi parameter (TTIRPA) Cursor #: 1 Start of user function (TTIFUN) Parse a statement (OSQL) Cursor # 1 SELECT USER FROM DUAL ************************************************************************* * Trace Assistant has completed * *************************************************************************
Figure 17-14 shows output from the -la
option. The output shows the following information:
Receive
identifies the trace as a database server trace; Send
identifies the trace as a client trace. In this output, Receive
is the operation.
MULTIPLEX
attribute of the DISPATCHERS
parameter is set to ON
************************************************************************* * Trace Assistant * ************************************************************************* Connection ID: 00000B270000000B Socket Id: 15 Operation: Receive Multiplex: ON Session Id: 8362785DE4FC0B19E034080020F793E1 Connect Data: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA=(SERVER=shared) (SERVICE_NAME=sales.us.acme.com)(CID=(PROGRAM=)(HOST=sales-server) (USER=oracle)))) Connection ID: 00000B240000000B Socket Id: 15 Operation: Receive Multiplex: ON Session Id: 8362785DE4FB0B19E034080020F793E1 Connect Data: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA=(SERVER=shared) (SERVICE_NAME=sales.us.acme.com)(CID=(PROGRAM=)(HOST=sales-server) (USER=oracle)))) Connection ID: 00000B1F00000008 Socket Id: 15 Operation: Receive Multiplex: ON Session Id: 8362785DE4F90B19E034080020F793E1 Connect Data: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA=(SERVER=shared) (SERVICE_NAME=sales.us.acme.com)(CID=(PROGRAM=)(HOST=sales-server) (USER=oracle)))) ************************************************************************* * Trace Assistant has completed * *************************************************************************
Figure 17-15 shows output for connection ID 00000B1F00000008
from the -li 00000B1F00000008
option.
************************************************************************* * Trace Assistant * ************************************************************************* <--- Received 246 bytes - Connect packet Current NS version number is: 310. Lowest NS version number can accommodate is: 300. Global options for the connection: Can receive attention No attention processing Don't care Maximum SDU size: 2048 Maximum TDU size: 32767 NT protocol characteristics: Test for more data Test operation Full duplex I/O Urgent data support Generate SIGURG signal Generate SIGPIPE signal Generate SIGIO signal Handoff connection to another Line turnaround value: 0 Connect data length: 188 Connect data offset: 58 Connect data maximum size: 512 Native Services wanted NAU doing O3LOGON - DH key foldedin Native Services wanted NAU doing O3LOGON - DH key foldedin Cross facility item 1: 0 Cross facility item 2: 0 Connection id: Ox00000B1F00000008 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sales-server)(PORT=1521)) (CONNECT_DATA=(SERVER=shared)(SERVICE_NAME=sales.us.acme.com) (CID=(PROGRAM=)(HOST=sales-server)(USER=oracle)))) ---> Send 114 bytes - Accept packet Accepted NS version number is: 310. Global options for the connection: No attention processing Don't care Accepted maximum SDU size: 2048 Accepted maximum TDU size: 32767 Connect data length: 0 Native Services wanted NAU doing O3LOGON - DH key foldedin Native Services wanted NAU doing O3LOGON - DH key foldedin Connection Time out: 1000 Tick Size: 100 Reconnect Data: (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=34454)) Session Id: 8362785DE4F90B19E034080020F793E1 <--- Received 164 bytes - Data packet Native Services negotiation packet version#: 135290880 Service data packet #0 for Supervisor has 3 subpackets Subpacket #0: Version #135290880 Subpacket #1: 0000000000000000 Subpacket #2: DEADBEEF0003000000040004000100010002 Service data packet #1 for Authentication has 3 subpackets Subpacket #0: Version #135290880 Subpacket #1: UB2: 57569 Subpacket #2: FCFF Service data packet #2 for Encryption has 2 subpackets Subpacket #0: Version #135290880 Subpacket #1: 0000000000 Service data packet #3 for Data Integrity has 2 subpackets Subpacket #0: Version #135290880 Subpacket #1: 0000 ---> Send 143 bytes - Data packet Native Services negotiation packet version#: 135290880 Service data packet #0 for Supervisor has 3 subpackets Subpacket #0: Version #135290880 Subpacket #1: 0000 Subpacket #2: DEADBEEF00030000000200040001 Service data packet #1 for Authentication has 2 subpackets Subpacket #0: Version #135290880 Subpacket #1: FBFF Service data packet #2 for Encryption has 2 subpackets Subpacket #0: Version #135290880 Subpacket #1: UB1: 0 Service data packet #3 for Data Integrity has 2 subpackets Subpacket #0: Version #135290880 Subpacket #1: UB1: 0 <--- Received 48 bytes - Data packet Set protocol (TTIPRO) Operation 01 (con) Receive protocol version=6 Destination platform: SVR4-be-8.1.0 ---> Send 156 bytes - Data packet Set protocol (TTIPRO) Operation 01 (con) Send protocol version=6 Originating platform: SVR4-be-8.1.0 <--- Received 49 bytes - Data packet Set datatypes (TTIDTY) ---> Send 38 bytes - Data packet Set datatypes (TTIDTY) <--- Received 245 bytes - Data packet Start of user function (TTIFUN) Get the session key (OSESSKEY) ---> Send 161 bytes - Data packet Return opi parameter (TTIRPA) ... ************************************************************************* * Trace Assistant has completed * *************************************************************************
The type of statistics gathered is approximately on how many TTC calls, packets and bytes were sent and received between the network partners. Figure 17-16 shows typical trace file statistics from the -s
option.
************************************************************************* * Trace Assistant * ************************************************************************* ---------------------- Trace File Statistics: ---------------------- Total number of Sessions: 3 DATABASE: Operation Count: 0 OPENS, 21 PARSES, 21 EXECUTES, 9 FETCHES Parse Counts: 9 PL/SQL, 9 SELECT, 0 INSERT, 0 UPDATE, 0 DELETE, 0 LOCK, 3 TRANSACT, 0 DEFINE, 0 SECURE, 0 OTHER Execute counts with SQL data: 9 PL/SQL, 0 SELECT, 0 INSERT, 0 UPDATE, 0 DELETE, 0 LOCK, 0 TRANSACT, 0 DEFINE, 0 SECURE, 0 OTHER Packet Ratio: 6.142857142857143 packets sent per operation Currently opened Cursors: 0 Maximum opened Cursors : 0 ORACLE NET SERVICES: Total Calls : 129 sent, 132 received, 83 oci Total Bytes : 15796 sent, 13551 received Average Bytes: 122 sent per packet, 102 received per packet Maximum Bytes: 1018 sent, 384 received Grand Total Packets: 129 sent, 132 received ************************************************************************* * Trace Assistant has completed * *************************************************************************
If you are still unable to resolve your problems, or if you are requested to contact Oracle Support Services to report the error, please have the following information at hand:
|
![]() Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|