Donald K. Burleson Oracle Utilities Tips

Testing Connectivity
 

Tracing Listener Connections
 

Network connections are traced to provide a detailed description of the operations performed by Oracle's internal components. The trace data is stored in an output trace file that can then be analyzed.

The following four steps can be performed to trace listener connections:

1. Enable tracing for the appropriate period of time needed to capture enough data.
 

LSNRCTL> set trc_level ADMIN

2. Disable tracing.

LSNRCTL> set trc_level OFF

3. View trace file (optional) - the trace file can be viewed although it is very cryptic. The file can be located by using the show command along with trc_directory and again with trc_file. The following is an example of the raw trace file.

nsdo: cid=6, opcode=65, *bl=0, *what=0, uflgs=0x0, cflgs=0x2
nsdo: rank=64, nsctxrnk=0
nsdo: nsctx: state=7, flg=0x4200, mvd=0
nsdo: nsctxrnk=0
nsanswer: deferring connect attempt; at stage 5
nsevreg: begin registration process for 6
nsevreg: sgt=0, evn=4, evt[2]=0x0
nsevreg: begin notification process for 6
nsrah: setting transport read mode (2)
nsevreg: rdm=2, sgt=0, evt[0]=0x20, [1]=0x20, [2]=0x0, nrg=0
nsevreg: registering for 0x20
nsrah: reading (asynchronously) from transport...
nsrah: ...into overflow area...
ntnrd: failed to read 8208 bytes
ntn2err: entry
ntn2err: error: handle=396, op=5, nt[0]=524, nt[1]=997

4. Format Trace File with the trcasst utility. Oracle Release 2 introduces the Oracle Net Trace assistant to help administrators decipher information contained in the trace files. The trcasst command can be used to provide a more readable trace. This is very helpful when advanced diagnosis is required for problem debugging.
The trcasst utility provides the following command line options:

• -o – Displays services and TTC (Two Task Common) information. Valid options to use with –o include:

• c – Summary of connection information.

• d – Detailed connection information.

• u – Summary of TTC information.

• t – Detailed TTC information.

• q – SQL commands (used in combination with u).

• -e – Displays error information. Valid options to use with –e include:

• 0 – translates NS error numbers.

• 1 – Displays only error translation.

• 2 – Displays error numbers without translation.

• -l – Displays services and TTC information. Valid options to use with –l include:

• a – Displays data for all connections in trace file.

• i – Displays the trace data for a particular ID from the –la option.

• -s – Displays a summary of statistics. This includes total bytes sent and received, maximum open cursors, total calls, parse counts, and more.
If no options are specified on the command line, the default will be:

trcasst –odt –e0 –s <filename>

Sample output generated by the trcasst utility is below.

C:\Oracle\network\trace>trcasst listener.trc

Trace Assistant Utility: Version 9.2.0.1.0 Production on February 6, 2003 9:12:
2 PM

(c) Copyright 2002 Oracle Corporation. All rights reserved.


*************************************************************************
* Trace Assistant *
*************************************************************************

///////////////////////////////////////////////////////////////
Error found. Error Stack follows:
id:6
Operation code:68
NS Error 1:12537
NS Error 2:12560
NT Generic Error:507
Protocol Error:109
OS Error:0
NS & NT Errors Translation
12537, 00000 "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has disconnected.

// *Action: None needed; this is an information message.
/
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.
/
00507, 00000 "Connection closed"
// *Cause: Normal "end of file" condition has been reached; partner has
// disconnected.
// *Action: None needed; this is an information message.
/
///////////////////////////////////////////////////////////////


----------------------
Trace File Statistics:
----------------------
Total number of Sessions: 0

DATABASE:
Operation Count: 0 OPENS, 0 PARSES, 0 EXECUTES, 0 FETCHES


ORACLE NET SERVICES:
Total Calls : 0 sent, 0 received, 0 oci
Total Bytes : 0 sent, 0 received
Average Bytes: sent per packet, received per packet
Maximum Bytes: 0 sent, 0 received

Grand Total Packets: 0 sent, 0 received


*************************************************************************
* Trace Assistant has completed *
*************************************************************************

The raw trace file is not nearly as helpful as the results from the trcasst utility.

Due to the resources required, tracing the listener activity should be reserved only for debugging connectivity problems. The amount of trace information generated by the tool can be enormous.
 

To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.