Oracle Net Services - Tracing and Logging at a Glance

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

一、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时，必须添加下列参数   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=c:\your_directory

二、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 三、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则是在此基础之上提供更为详尽的故障诊断信息

四、trace的相关示例 1、客户端trace  修改客户端的sqlnet.ora文件  D:\>type d:\oracle\product\10.2.0\client_2\network\admin\sqlnet.ora  SQLNET.AUTHENTICATION_SERVICES= (NTS)  NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)  # Below item added by Robinson  Trace_level_client=16  Trace_directory_client=d:\oracle\product\10.2.0\client_2\network\admin  Trace_unique_client=on  Trace_timestamp_client=on  Diag_adr_enabled=off  tnsping.trace_directory=d:\oracle\product\10.2.0\client_2\network\admin  tnsping.trace_level=admin  D:\>tnsping cnmmbo      ............  Used parameter files:  d:\oracle\product\10.2.0\client_2\network\admin\sqlnet.ora  Used TNSNAMES adapter to resolve the alias  Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.7.2) (PORT=1910))  (CONNECT_DATA= (SERVICE_NAME=CNMMBO)))  OK (20 msec)  D:\>dir d:\oracle\product\10.2.0\client_2\network\admin\   Volume in drive D has no label.   Volume Serial Number is EC2E-8A31   Directory of d:\oracle\product\10.2.0\client_2\network\admin  04/12/2012  09:58 AM    <DIR>          .  04/12/2012  09:58 AM    <DIR>          ..  06/08/2012  10:45 AM    <DIR>          SAMPLE  04/12/2012  09:58 AM               725 sqlnet.ora  12/11/2012  02:04 PM            10,648 tnsnames.ora  22/08/2012  02:31 PM             6,564 tnsnames.ora.bak  04/12/2012  09:58 AM             5,932 tnsping.trc    #-->刚刚产生的trace文件  D:\>sqlplus goex_admin@cnmmbo  SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 4 10:02:05 2012  Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.  Enter password:  ERROR:  ORA-01017: invalid username/password; logon denied  D:\>dir d:\oracle\product\10.2.0\client_2\network\admin   Volume in drive D has no label.   Volume Serial Number is EC2E-8A31   Directory of d:\oracle\product\10.2.0\client_2\network\admin  04/12/2012  10:02 AM    <DIR>          .  04/12/2012  10:02 AM    <DIR>          ..  04/12/2012  10:02 AM            15,892 cli_12172.trc  04/12/2012  10:02 AM           114,703 cli_12172_1.trc  06/08/2012  10:45 AM    <DIR>          SAMPLE  04/12/2012  09:58 AM               725 sqlnet.ora  ............  修改tnsnames.ora的cnmmbo项的service_name之后再连接  #Author : Robinson  #Blog : http://blog.csdn.net/robinson_0612  D:\>sqlplus goex_admin@cnmmbo  SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 4 10:07:57 2012  Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.  Enter password:  ERROR:  ORA-12514: TNS:listener does not currently know of service requested in connect  descriptor  Enter user-name:  D:\>dir d:\oracle\product\10.2.0\client_2\network\admin   Volume in drive D has no label.   Volume Serial Number is EC2E-8A31   Directory of d:\oracle\product\10.2.0\client_2\network\admin  04/12/2012  10:08 AM    <DIR>          .  04/12/2012  10:08 AM    <DIR>          ..  04/12/2012  10:08 AM            15,902 cli_11444.trc  04/12/2012  10:08 AM            24,897 cli_11444_1.trc  #--又产生了两个新的trace文件  04/12/2012  10:02 AM            15,892 cli_12172.trc  04/12/2012  10:02 AM           114,703 cli_12172_1.trc       ..............  在文件cli_11444_1.trc可以看到相关的错误      [04-DEC-2012 10:08:02:560] niqme: reporting NS-12564 error as ORA-12564  [04-DEC-2012 10:08:02:560] niqme: exit  [04-DEC-2012 10:08:02:560] niomapnserror: exit  [04-DEC-2012 10:08:02:560] niqme: entry  [04-DEC-2012 10:08:02:560] niqme: reporting NSG-12514 error as ORA-12514  [04-DEC-2012 10:08:02:560] niqme: exit  [04-DEC-2012 10:08:02:560] niotns: Couldn't connect, returning 12514  [04-DEC-2012 10:08:02:562] niotns: exit

2、服务器端的trace      同样是修改sqlnet.ora  ...  trace_level_server = 16  trace_file_server = svr  trace_directory_server = /u01/app/oracle/product/9.0.1/network/trace  trace_unique_server = on  trace_timestamp_server = on  trace_filelen_server = 100  trace_fileno_server = 2  log_file_server = svr  log_directory_server = /u01/app/oracle/product/9.0.1/network/log  namesctl.trace_level = 16  namesctl.trace_file = namesctl  namesctl.trace_directory = /u01/app/oracle/product/9.0.1/network/trace  namesctl.trace_unique = on

3、监听器上的trace  LISTENER (LISTENER.ORA)  ...  trace_level_listener = 16  trace_file_listener = listener  trace_directory_listener = /u01/app/oracle/product/9.0.1/network/trace  trace_timestamp_listener = on  trace_filelen_listener = 100  trace_fileno_listener = 2  logging_listener = off  log_directory_listener = /u01/app/oracle/product/9.0.1/network/log  log_file_listener=listener  NAMESERVER TRACE (NAMES.ORA)  ....  names.trace_level = 16  names.trace_file = names  names.trace_directory = /u01/app/oracle/product/9.0.1/network/trace  names.trace_unique = off

4、CONNECTION MANAGER TRACE (CMAN.ORA)  ...  tracing = yes  10g and newer CMAN tracing is set using the following parameters under PARAMETER_LIST:  TRACE_LEVEL=SUPPORT  Additionally, cyclic tracing is now available for CMAN using:  TRACE_FILELEN=<length of each file>  TRACE_FILENO=<number of files to generate cyclically>  See above explanation for cyclic trace parameters.  As mentioned above, 11g would require these additional trace parameters:  DIAG_ADR_ENABLED=OFF  ADR_BASE=c:\your_directory

五、查看trace文件   对于session trace, Oracle提供了tkprof工具，对于net trace，Oracle也提供了工具，那就是trcasst来格式化trace文件   有关trcasst的使用请参考: trcasst Tips