如何使用sqlnet net trace 跟踪或调试oracle连接故障

Oracle Net trace 用于跟踪或调试oracle连接故障，连接异常断开或者连接超时等情形，通过产生详细的跟踪信息来进行分析和诊断Oracle Net相关故障。关于这个网络调试主要是通过为相关的网络配置文件添加相关的参数来实现

Oracle trace的相关参数如下：
trace_level_[client|server|listener] = [0-16|user|admin|support|off]
trace_file_[client|server|listener] = <file name>
trace_directory_[client|server|listener] = <directory>
trace_unique_[client|server|listener] = [on|true|off|false]
trace_timestamp_[client|server|listener] = [on|true|off|false] #oracle8i+
trace_filelen_[client|server|listener] = <size in kb> #oracle8i+
trace_fileno_[client|server|listener] = <number> #oracle8i+
log_file_[client|server|listener] = <file name>
log_directory_[client|server|listener] = <directory name>
logging_listener = [on|off]
tnsping.trace_level = [0-16|user|admin|support|off]
tnsping.trace_directory = <directory>
names.trace_level = [0-16|user|admin|support|off]
names.trace_file = <file name>
names.trace_directory = <directory>
names.trace_unique = [on|off]
names.log_file = <file name>
names.log_directory = <directory>
names.log_unique = [on|off]
namesctl.trace_level = [0-16|user|admin|support|off]
namesctl.trace_file = <file name>
namesctl.trace_directory = <directory>
namesctl.trace_unique = [on|off]notes on tracing 11g and newer client/server/listener/cman

对于Oracle 11g，使用传统的Oracle net tracing时，必须添加下列参数禁用adr

sqlnet.ora file on either client or server:
diag_adr_enabled=off

listener.ora file:
diag_adr_enabled_<listener_name>=off

cman.ora:
diag_adr_enabled=off
adr_base=/your_directory

启用sqlnet trace
为客户端的sqlnet.ora文件添加下列项来启用sqlnet trace，trace后，建议移除这些选项以避免额外的网络开销
Trace_level_client=16
Trace_directory_client=<path_to_the_trace_directory> # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off


oracle net trace的组件，参数文件及缺省的trace文件
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



03、TRACE时的注意事项
   1、确保所需trace的配置文件存在
     缺省情况下，Oracle会从下列位置搜索网络配置文件
     a.TNS_ADMIN environment variable (incl. Windows Registry Key)
     b./etc or /var/opt/oracle (Unix)
     c.$ORACLE_HOME/network/admin (Unix)
     d.%ORACLE_HOME%/Network/Admin or %ORACLE_HOME%/Net80/Admin (Windows)
     Note: User-specific Oracle Net parameters may also reside in $HOME/sqlnet.ora file.
     An Oracle Net server installation is also a client itself.
  2、 确保trace文件所在的磁盘有足够的空间
  3、当有关Oracle net故障解决后应立即禁用Oracle net trace特性
  4、大型的跟踪日志文件使得进程开销过大，因此在无异常的情况下关闭trace特性，截断日志文件等以提高Oracle net整体性能
  5、确保连接的用户对跟踪日志文件所在的路径用户有写权限
  6、对于服务器端的trace，将其置为OFF后，并不能立即停止服务器端的trace，需要重启监听
  7、Oracle Net本身也产生相关的日志，如监听日志,sqlnet日志等，Oracle Net trace则是在此基础之上提供更为详尽的故障诊断信息
04、trace的相关示例
分客户端与服务端：

001、客户端trace
修改客户端的sqlnet.ora文件
Trace_level_client=16
Trace_directory_client=D:\app\Administrator\product\11.2.0\client_1\network\admin
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off
tnsping.trace_directory=D:\app\Administrator\product\11.2.0\client_1\network\admin
tnsping.trace_level=admin

实验测试

修改客户端的sqlnet.ora文件：

1 2 3 4 5 6 7 8 9 10 11 12