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