sqlnet跟蹤

用客戶端跟蹤資料庫連線問題
sqlnet.ora是oracle客戶端（例如sqlplus、PL/SQL、tnsping命令）使用的引數檔案，控制客戶端訪問資料庫伺服器


開啟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

一個有用的參考：