开启sqlnet 跟踪

在client 端有时候能ping的同server端、tnsping也能通，但是使用sqlplus却连不上server。这时候就可以尝试一下通过sqlnet来跟踪一下。

下面是如何打开sqlnet的跟踪和跟踪文件输出的一些信息

1、在client 端编辑sqlnet.ora文件打开 sqlnet跟踪

[oracle@normal admin]$ cat sqlnet.ora TRACE_LEVEL_CLIENT=16TRACE_DIRECTORY_CLIENT=/u01/app/oracle/product/11.2.0/db_1/network/adminTRACE_FILE_CLIENT=clientTRACE_UNIQUE_CLIENT=ONTRACE_TIMESTAMP_CLIENT=ON

2、使用sqlplus登陆database，会产生sqlnet跟踪信息。如下

[oracle@normal admin]$ sqlplus chenhao/oracle@normalSQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 27 10:06:46 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> --在另一个会话查看trace信息[oracle@normal trace]$ ls -Fltrtotal 800-rw-r----- 1 oracle oinstall   1629 Aug 26 17:40 sqlnet.log-rw-r----- 1 oracle oinstall  27249 Aug 27 10:06 ora_2189_139800775366400.trm-rw-r----- 1 oracle oinstall 157768 Aug 27 10:06 ora_2189_139800775366400.trc[oracle@normal trace]$ less ora_2189_139800775366400.trcTrace file /u01/app/oracle/diag/clients/user_oracle/host_4049341086_80/trace/ora_2189_139800775366400.trc2014-08-27 10:06:46.686807 : --- TRACE CONFIGURATION INFORMATION FOLLOWS ---2014-08-27 10:06:46.725281 : New trace stream is /u01/app/oracle/diag/clients/user_oracle/host_4049341086_80/trace/ora_2189_139800775366400.trc2014-08-27 10:06:46.725318 : New trace level is 162014-08-27 10:06:46.725332 : --- TRACE CONFIGURATION INFORMATION ENDS ---2014-08-27 10:06:46.725351 : --- PARAMETER SOURCE INFORMATION FOLLOWS ---2014-08-27 10:06:46.725434 : Attempted load of system pfile source /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora2014-08-27 10:06:46.725457 : Parameter source loaded successfully2014-08-27 10:06:46.725472 : 2014-08-27 10:06:46.725485 : Attempted load of local pfile source /home/oracle/.sqlnet.ora2014-08-27 10:06:46.725498 : Parameter source was not loaded2014-08-27 10:06:46.725510 : 2014-08-27 10:06:46.725522 :  -> PARAMETER TABLE LOAD RESULTS FOLLOW <-2014-08-27 10:06:46.725537 : Successful parameter table load2014-08-27 10:06:46.725552 :  -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-2014-08-27 10:06:46.725573 :   TRACE_LEVEL_CLIENT = 162014-08-27 10:06:46.725586 :   NAMES.DIRECTORY_PATH = (tnsnames, onames, hostname)2014-08-27 10:06:46.725599 :   TRACE_FILE_CLIENT = client2014-08-27 10:06:46.725612 :   TRACE_TIMESTAMP_CLIENT = ON2014-08-27 10:06:46.725625 :   TRACE_DIRECTORY_CLIENT = /u01/app/oracle/product/11.2.0/db_1/network/admin2014-08-27 10:06:46.725638 :   TRACE_UNIQUE_CLIENT = ON2014-08-27 10:06:46.725651 : --- PARAMETER SOURCE INFORMATION ENDS ---2014-08-27 10:06:46.725666 : --- LOG CONFIGURATION INFORMATION FOLLOWS ---2014-08-27 10:06:46.725714 : Log stream will be "/u01/app/oracle/diag/clients/user_oracle/host_4049341086_80/alert/log.xml"2014-08-27 10:06:46.725730 : Log stream validation not requested2014-08-27 10:06:46.725744 : --- LOG CONFIGURATION INFORMATION ENDS ---2014-08-27 10:06:46.725767 : nlstdipi:entry2014-08-27 10:06:46.725929 : nlstdipi:exit2014-08-27 10:06:46.725966 : nigini:entry2014-08-27 10:06:46.725992 : nigini:Count in the NL global area is now 12014-08-27 10:06:46.726009 : nigini:Count in NI gbl area now: 12014-08-27 10:06:46.726027 : nrigbi:entry2014-08-27 10:06:46.726048 : nrigbni:entry2014-08-27 10:06:46.726112 : nrigbni:Unable to get data from navigation file tnsnav.ora2014-08-27 10:06:46.726128 : nrigbni:exit2014-08-27 10:06:46.726141 : nrigbi:exit2014-08-27 10:06:46.726197 : nigini:exit2014-08-27 10:06:46.729820 : nigsui:entry

可以仔细的检查sqlnet的信息从而寻找到一些蛛丝马迹。

注意：其实上面有个问题关于sqlnet trace文件路径的问题，我在sqlnet.ora中设置了产生的trace文件应该在/u01/app/oracle/product/11.2.0/db_1/network/admin中但是在这个路径下却找不到。如果你也遇到了这样的情况可以到一下路径找找试一试：$ORACLE_BASE/diag/clients/user_oracle/host_$NUMBER/trace

还有就是我在Win7的client端死也早不到trace文件，有知道为什么的告诉下呗。共同学习共同进步哈。