首页 » ORACLE » How to enable SQL*Net Tracing with Oracle Instant Client 11G

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

Sometimes we need to enable sql*net trace to diagnose sqlplus  connection problems such as  ora-125*

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\network\admin\sqlnet.ora
TNS-03505: 无法解析名称

C:\> notepad E:\oracle\product\11.2.0\client_1\network\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:\NET\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:\NET\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
打赏

对不起,这篇文章暂时关闭评论。