sqlnet跟踪

用客户端跟踪数据库连接问题
sqlnet.ora是oracle客户端（例如sqlplus、PL/SQL、tnsping命令）使用的参数文件，控制客户端访问数据库服务器

点击(此处)折叠或打开

1. [laolv@centhost3 admin]$ tnsping orclrman
   
2. 
   
3. TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 04-DEC-2016 21:59:47
   
4. 
   
5. Copyright (c) 1997, 2014, Oracle. All rights reserved.
   
6. 
   
7. Used parameter files:
   
8. /opt/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
   
9. 
   
10. 
    
11. Used TNSNAMES adapter to resolve the alias
    
12. Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.147)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclrman)))
    
13. OK (10 msec)

开启sqlnet 跟踪
下面的内容，参考自：http://blog.csdn.net/ch7543658/article/details/38865645 
在client 端有时候能ping的同server端、tnsping也能通，但是使用sqlplus却连不上server。这时候就可以尝试一下通过sqlnet来跟踪一下。

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

[oracle@normal admin]$ cat sqlnet.ora   
TRACE_LEVEL_CLIENT=16  
TRACE_DIRECTORY_CLIENT=/u01/app/oracle/product/11.2.0/db_1/network/admin  
TRACE_FILE_CLIENT=client  
TRACE_UNIQUE_CLIENT=ON  
TRACE_TIMESTAMP_CLIENT=ON 

[laolv@centhost3 admin]$ cat sqlnet.ora 
# sqlnet.ora Network Configuration File: /opt/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ###NAMES.DIRECTORY_PATH指定CLIENT NAME解析方法的次序，默认是NAMES.DIRECTORY_PATH=(tnsnames, onames, hostname),常使用NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)。 遇到TNSNAMES时：就去tnsnames.orc文件中查找数据库服务别名记录，将数据库服务别名解析为对应的数据库服务。
TRACE_LEVEL_CLIENT=16
TRACE_TIMESTAMP_CLIENT=ON

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

[laolv@centhost3 trace]$ vi ora_32363_140204926600640.trc
race file /opt/oracle/diag/clients/user_laolv/host_2849077872_82/trace/ora_32363_140204926600640.trc
2016-12-04 21:42:21.871784 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
2016-12-04 21:42:21.946500 : nlstddt_do_alter_trace:New trace stream is /opt/oracle/diag/clients/user_laolv/host_2849077872_82/trace/ora_32363_140204926600640.trc
2016-12-04 21:42:21.946548 : nlstddt_do_alter_trace:New trace level is 16
2016-12-04 21:42:21.946569 : nlstddt_do_alter_trace:--- TRACE CONFIGURATION INFORMATION ENDS ---
2016-12-04 21:42:21.946591 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION FOLLOWS ---
2016-12-04 21:42:21.946710 : nlstdts_trace_source:Attempted load of system pfile source /opt/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
2016-12-04 21:42:21.946778 : nlstdts_trace_source:Parameter source loaded successfully
2016-12-04 21:42:21.946913 : nlstdts_trace_source:
2016-12-04 21:42:21.946937 : nlstdts_trace_source:Attempted load of local pfile source /home/laolv/.sqlnet.ora
2016-12-04 21:42:21.946961 : nlstdts_trace_source:Parameter source was not loaded
2016-12-04 21:42:21.946975 : nlstdts_trace_source:
2016-12-04 21:42:21.946992 : nlstdtp_trace_pfile: -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
2016-12-04 21:42:21.947009 : nlstdtp_trace_pfile:Successful parameter table load
2016-12-04 21:42:21.947025 : nlstddp_dump_ptable: -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
2016-12-04 21:42:21.947046 : nlstddp_dump_ptable:  TRACE_LEVEL_CLIENT = 16
2016-12-04 21:42:21.947062 : nlstddp_dump_ptable:  NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
2016-12-04 21:42:21.947077 : nlstddp_dump_ptable:  TRACE_TIMESTAMP_CLIENT = ON
2016-12-04 21:42:21.947092 : nlstdtp_trace_pfile:--- PARAMETER SOURCE INFORMATION ENDS ---
2016-12-04 21:42:21.947110 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION FOLLOWS ---
2016-12-04 21:42:21.947163 : nlstddl_do_alter_log:Log stream will be "/opt/oracle/diag/clients/user_laolv/host_2849077872_82/alert/log.xml"
2016-12-04 21:42:21.947182 : nlstddl_do_alter_log:Log stream validation not requested
2016-12-04 21:42:21.947198 : nlstddl_do_alter_log:--- LOG CONFIGURATION INFORMATION ENDS ---


2016-12-04 21:42:21.947221 : nlstdipi:entry
2016-12-04 21:42:21.947349 : nlstdipi:exit
2016-12-04 21:42:21.947375 : nigini:entry
2016-12-04 21:42:21.947395 : nigini:Count in the NL global area is now 1
2016-12-04 21:42:21.947407 : nigini:Count in NI gbl area now: 1
2016-12-04 21:42:21.947420 : nrigbi:entry
2016-12-04 21:42:21.947434 : nrigbni:entry
2016-12-04 21:42:21.957870 : nrigbni:Unable to get data from navigation file tnsnav.ora
……
注意：其实上面个问题关于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
[laolv@centhost3 trace]$ pwd
/opt/oracle/diag/clients/user_laolv/host_2849077872_82/trace

一个有用的参考：http://book.51cto.com/art/201312/421448.htm   诊断案例之二：使用客户端跟踪数据库连接问题 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25097694/viewspace-2129693/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25097694/viewspace-2129693/