How to enable SQL*Net Tracing with Oracle Instant Client 11G

Below are the steps to configure Sql*Net verbose Client side tracing:
(1) Open the SQLNET.ORA file typically found in the installation location (or simply do a Search):
C:\\>tnsping xx
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 03-12月-2013 11:49:46
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的参数文件:
E:\\oracle\\product\\11.2.0\\client_1\
etwork\\admin\\sqlnet.ora
TNS-03505: 无法解析名称
C:\\> notepad E:\\oracle\\product\\11.2.0\\client_1\
etwork\\admin\\sqlnet.ora
(2) Add the following parameters at the end of the file in pre-11g:
#CLIENT-SIDE SQL*NET TRACE PARAMETERS
#====================================
trace_level_client = 16
trace_file_client = cli
trace_directory_client = c:\\trace
trace_unique_client = on
trace_timestamp_client = on
trace_filelen_client = 100
trace_fileno_client = 2
log_file_client = cli
log_directory_client = c:\\trace\\log
tnsping.trace_directory = c:\\trace\\trace
tnsping.trace_level = admin
Starting from Oracle version 11.1 there are two possible destinations for trace and log files: Automatic Diagnostic Repository (ADR) and non-ADR(old pre 11.1-style).
For ADR-destination we specify:
TRACE_LEVEL_CLIENT=(OFF | USER | ADMIN | SUPPORT)
DIAG_ADR_ENABLED=ON
ADR_BASE=D:\\ORACLE\\LOG
(default for ?nux: ~/oradiag_username/clients/user_username/host*/trace
for windows %systemdrive%\\Documents and Settings\\%username%\\oracle\\oradiag_%username%\\diag\\clients\\user_%username%\\host*\\trace)
TRACE_TIMESTAMP_CLIENT=(ON | true | off | false)
For non-ADR(old style – pre 11.1) destination we specify:
Just Add the following line above the pre-11g content, then sql*net trace files in trace_directory_client directory to generated
DIAG_ADR_ENABLED=OFF
Parameters prefixed with“#” are interpreted as comments and will not affect tracing.
If you want to change the location where the trace file will be saved modify the following two parameters:
(a) TRACE_DIRECTORY_CLIENT
Set this parameter to the folder where you want the trace file to be saved.
WARNING: Do not end the path with a “\\”. This is not necessary and may prevent the trace file from being generated.
(b) trace_level_client
Table 3 - 2. SQLNET.ORA Diagnostic Parameters
 

Parameters	Default	Values	Example (DOS client, UNIX server)
TRACE_LEVEL_CLIENT	OFF	[OFF | USER |ADMIN]	TRACE_LEVEL_CLIENT=USER
TRACE_FILE_CLIENT	SQLNET.TRC	string	TRACE_FILE_CLIENT=CLIENT
TRACE_DIRECTORY_CLIENT	O/S specific	valid directory	TRACE_DIRECTORY_CLIENT= c:\ ET\\ADMIN
TRACE_UNIQUE_CLIENT	OFF	[ON|OFF]	TRACE_UNIQUE_CLIENT=ON
LOG_FILE_CLIENT	SQLNET.LOG	string	LOG_FILE_CLIENT=CLIENT
LOG_DIRECTORY_CLIENT	O/S specific	valid directory	LOG_DIRECTORY_CLIENT=c:\ ET\\ADMIN
TRACE_LEVEL_SERVER	OFF	[OFF | USER | ADMIN]	TRACE_LEVEL_SERVER=ADMIN
TRACE_FILE_SERVER	SQLNET.TRC	string	TRACE_FILE_SERVER=unixsrv_2345.trc
TRACE_DIRECTORY_SERVER	O/S specific	valid directory	TRACE_DIRECTORY_SERVER=/tmp/trace
LOG_FILE_SERVER	SQLNET.LOG	string	LOG_FILE_SERVER=unixsrv.log
LOG_DIRECTORY_SERVER	O/S specific	valid directory	LOG_DIRECTORY_SERVER=/tmp/trace

Note: You control both log and trace parameters for the client through Oracle Network Manager. You can also control trace parameters for the client through the SQLNET.ORA Editor. You control log and trace parameters for the server by manually adding the desired parameters to the SQLNET.ORA file.

Parameter	Description
TRACE_DIRECTORY_CLIENT	Specifies the directory for the client trace file
TRACE_FILE_CLIENT	Specifies the name of the client trace file
TRACE_DIRECTORY_SERVER	Specifies the directory for the server trace file
TRACE_FILE_SERVER	Specifies the name of the server trace file
TRACE_FILELEN_CLIENT	Specifies the size of each client trace file in kilobytes
TRACE_FILENO_CLIENT	Specifies the number of client trace files
TRACE_FILELEN_SERVER	Specifies the size of each server trace file in kilobytes
TRACE_FILENO_SERVER	Specifies the number of server trace files
TRACE_LEVEL_CLIENT	Specifies the level of detail for client trace
TRACE_LEVEL_SERVER	Specifies the level of detail for server trace
TRACE_TIMESTAMP_CLIENT	Includes a timestamp (to microseconds) for each event in the client trace
TRACE_TIMESTAMP_SERVER	Includes a timestamp (to microseconds) for each event in the client trace
TRACE_UNIQUE_CLIENT	Creates an individual client trace file for each process

 
For both TRACE_LEVEL_CLIENT and TRACE_LEVEL_SERVER, the parameter can take a numeric value between 0 and 16 where 0 is disabled and 16 is the most detailed. Alternatively these parameters can also take a scalar value was follows:

OFF 0 No tracing USER 4 Include user errors ADMIN 6 Include administrative errors SUPPORT 16 Include packet contents

more references http://docs.oracle.com/cd/A57673_01/DOC/net/doc/NWTR23/ch3trc.htm#toc025
(3) When you are finished with the SQL*trace, be sure to remove the lines from the sqlnet.ora file.
 
take a example
C:\\>tnsping icme
C:\\trace\\trace 的目录
2013-12-03  10:04    <DIR>          .
2013-12-03  10:04    <DIR>          ..
2013-12-03  11:38             6,018 tnsping.trc
 
CONFIGURATION FILES
Files required to enable Oracle Net tracing and logging features include:

Oracle Net Component 		Configuration File			 Default Trace File 
Oracle Net Listener 		LISTENER.ORA 				LISTENER.TRC 
Oracle Net - Client 		SQLNET.ORA on client 			CLI*.TRC 
Oracle Net  - Server 		SQLNET.ORA on server 			SVR_*.TRC 
TNSPING SQLNET.ORA 		on client | server 			TNSPING.TRC 
Oracle Name Server 		NAMES.ORA 				NAMES.TRC 
Oracle NAMESCTL 		SQLNET.ORA on server   
Oracle Connection Manager 	CMAN.ORA

References
 http://www.juliandyke.com/Diagnostics/Trace/NetTrace.html
复制