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
六、更多参考
有关Oracle RAC请参考
使用crs_setperm修改RAC资源的所有者及权限
使用crs_profile管理RAC资源配置文件
RAC 数据库的启动与关闭
再说 Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Oracle RAC 连接到指定实例
Oracle RAC 负载均衡测试(结合服务器端与客户端)
Oracle RAC 服务器端连接负载均衡(Load Balance)
Oracle RAC 客户端连接负载均衡(Load Balance)
ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)
ORACLE RAC 监听配置 (listener.ora tnsnames.ora)
配置 RAC 负载均衡与故障转移
CRS-1006 , CRS-0215 故障一例
基于Linux (RHEL 5.5) 安装Oracle 10g RAC
使用 runcluvfy 校验Oracle RAC安装环境
有关Oracle 网络配置相关基础以及概念性的问题请参考：
配置非默认端口的动态服务注册
配置sqlnet.ora限制IP访问Oracle
Oracle 监听器日志配置与管理
设置 Oracle 监听器密码(LISTENER)
配置ORACLE 客户端连接到数据库
有关基于用户管理的备份和备份恢复的概念请参考
Oracle 冷备份
Oracle 热备份
Oracle 备份恢复概念
Oracle 实例恢复
Oracle 基于用户管理恢复的处理
SYSTEM 表空间管理及备份恢复
SYSAUX表空间管理及恢复
Oracle 基于备份控制文件的恢复(unsing backup controlfile)
有关RMAN的备份恢复与管理请参考
RMAN 概述及其体系结构
RMAN 配置、监控与管理
RMAN 备份详解
RMAN 还原与恢复
RMAN catalog 的创建和使用
基于catalog 创建RMAN存储脚本
基于catalog 的RMAN 备份与恢复
RMAN 备份路径困惑
使用RMAN实现异机备份恢复(WIN平台)
使用RMAN迁移文件系统数据库到ASM
linux 下RMAN备份shell脚本
使用RMAN迁移数据库到异机
有关ORACLE体系结构请参考
Oracle 表空间与数据文件
Oracle 密码文件
Oracle 参数文件
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 控制文件(CONTROLFILE)
Oracle 归档日志
Oracle 回滚(ROLLBACK)和撤销(UNDO)
Oracle 数据库实例启动关闭过程
Oracle 10g SGA 的自动化管理
Oracle 实例和Oracle数据库(Oracle体系结构)
