Oracle
Oracle：長時間延遲打開連接
在我的 11g2 開發伺服器上打開初始連接時出現了很長的延遲（大約 2 分鐘） 。有時我得到
ORA-28547: connection to server failed, probable Oracle Net admin error並且根本沒有連接。但是，第二次嘗試時問題消失了。
多個客戶端出現問題：我們正在開發的應用程序（使用 occi）、SQLPlus、SQLDeveloper。
**編輯：**附加資訊。使用 strace，我看到客戶端 sqlplus 在我們的 Oracle DB 伺服器的正確埠上建立了 TCP 連接。一些數據交換正常，然後客戶端掛在套接字讀取上。
在掛起的連接上：讀取（6，
在良好的連接上： read(6, “\0\274\0\0\6\0\0\0\0\0\1\6\0x86_64/Linux 2.4.xx”…, 8208) = 188
良好的連接很快就會收到 AUTH_SESSKEY，用於身份驗證。因此掛起發生在密碼驗證完成之前。
這是來自的輸出
select * from v$sgainfo：Fixed SGA Size 2212448 No Redo Buffers 4980736 No Buffer Cache Size 20971520 Yes Shared Pool Size 155189248 Yes Large Pool Size 29360128 Yes Java Pool Size 4194304 Yes Streams Pool Size 4194304 Yes Shared IO Pool Size 0 Yes Granule Size 4194304 No Maximum SGA Size 263049216 No Startup overhead in Shared Pool 75497472 No Free SGA Memory Available 41943040編輯：
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-NOV-2012 23:54:07 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xoesrpora)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 12-SEP-2012 18:17:27 Uptime 61 days 5 hr. 36 min. 39 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /xoesrpora/linux/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /xoesrpora/linux/app/oracle/diag/tnslsnr/xoesrpora/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=1521))) Services Summary... Service "achen_l1ads.xoesr" has 1 instance(s). Instance "achenl1ads", status READY, has 2 handler(s) for this service... Service "anpingl1ads.xoesr" has 1 instance(s). Instance "anpingl1ads", status READY, has 2 handler(s) for this service... Service "bgoldl1ads.xoesr" has 1 instance(s). Instance "bgoldl1ads", status READY, has 2 handler(s) for this service... Service "bjodev01.xoesr" has 1 instance(s). Instance "bjodev01", status READY, has 2 handler(s) for this service... Service "bjodev02" has 1 instance(s). Instance "bjodev02", status READY, has 2 handler(s) for this service... Service "chen_l1ads.xoesr" has 1 instance(s). Instance "chenl1ads", status READY, has 2 handler(s) for this service... Service "gtullyl1ads.xoesr" has 1 instance(s). Instance "gtullyl1ads", status READY, has 2 handler(s) for this service... Service "jbentley.xoesr" has 1 instance(s). Instance "jbentley", status READY, has 2 handler(s) for this service... Service "jbentleyina.xoesr" has 1 instance(s). Instance "jbentleyina", status READY, has 2 handler(s) for this service... Service "jmiller1ads.xoesr" has 1 instance(s). Instance "jmiller1ads", status READY, has 2 handler(s) for this service... Service "l1ads.xoesr" has 1 instance(s). Instance "l1ads", status READY, has 2 handler(s) for this service... Service "l1adsSWIT.xoesr" has 1 instance(s). Instance "l1adsSWIT", status READY, has 2 handler(s) for this service... Service "mikedb.xoesrpora" has 1 instance(s). Instance "mikedb", status READY, has 2 handler(s) for this service... Service "nkovoorl1ads.xoesr" has 1 instance(s). Instance "nkovoorl1ads", status READY, has 2 handler(s) for this service... Service "nrekerl1ads.xoesr" has 1 instance(s). Instance "nrekerl1ads", status READY, has 2 handler(s) for this service... The command completed successfully xoesrpora[51]$ lsnrctl service LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-NOV-2012 23:55:07 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xoesrpora)(PORT=1521))) Services Summary... Service "achen_l1ads.xoesr" has 1 instance(s). Instance "achenl1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:5 refused:0 current:0 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 30680> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=34877)) Service "anpingl1ads.xoesr" has 1 instance(s). Instance "anpingl1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:39033 refused:0 current:16 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 31454> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=13523)) Service "bgoldl1ads.xoesr" has 1 instance(s). Instance "bgoldl1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:40133 refused:5 current:17 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 22276> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=16808)) Service "bjodev01.xoesr" has 1 instance(s). Instance "bjodev01", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:0 refused:0 current:2 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 1112> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=9362)) Service "bjodev02" has 1 instance(s). Instance "bjodev02", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:5324 refused:0 current:7 max:1022 state:ready DISPATCHER <machine: xoesrpora.aer.com, pid: 402> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora.aer.com)(PORT=43643)) Service "chen_l1ads.xoesr" has 1 instance(s). Instance "chenl1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:49787 refused:0 current:15 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 9501> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=57313)) Service "gtullyl1ads.xoesr" has 1 instance(s). Instance "gtullyl1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:17384 refused:0 current:20 max:1022 state:ready DISPATCHER <machine: xoesrpora.aer.com, pid: 26905> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora.aer.com)(PORT=50258)) Service "jbentley.xoesr" has 1 instance(s). Instance "jbentley", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER <machine: xoesrpora.aer.com, pid: 10737> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora.aer.com)(PORT=52195)) Service "jbentleyina.xoesr" has 1 instance(s). Instance "jbentleyina", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:17 refused:0 state:ready LOCAL SERVER "D000" established:8993 refused:0 current:14 max:1022 state:ready DISPATCHER <machine: xoesrpora.aer.com, pid: 18471> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora.aer.com)(PORT=28700)) Service "jmiller1ads.xoesr" has 1 instance(s). Instance "jmiller1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:25215 refused:0 current:17 max:1022 state:ready DISPATCHER <machine: xoesrpora.aer.com, pid: 6473> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora.aer.com)(PORT=64221)) Service "l1ads.xoesr" has 1 instance(s). Instance "l1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:9049 refused:0 current:17 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 18150> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=52593)) Service "l1adsSWIT.xoesr" has 1 instance(s). Instance "l1adsSWIT", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:4144 refused:0 current:20 max:1022 state:ready DISPATCHER <machine: xoesrpora.aer.com, pid: 26656> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora.aer.com)(PORT=45006)) Service "mikedb.xoesrpora" has 1 instance(s). Instance "mikedb", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:0 refused:0 current:2 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 17111> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=34540)) Service "nkovoorl1ads.xoesr" has 1 instance(s). Instance "nkovoorl1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:32557 refused:0 current:12 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 3601> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=21440)) Service "nrekerl1ads.xoesr" has 1 instance(s). Instance "nrekerl1ads", status READY, has 2 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER "D000" established:41491 refused:0 current:7 max:1022 state:ready DISPATCHER <machine: xoesrpora, pid: 25743> (ADDRESS=(PROTOCOL=tcp)(HOST=xoesrpora)(PORT=17368)) The command completed successfully
適當的方法是 sqlnet 跟踪。網路服務參考中描述了配置 ，輸出對 oracle 支持很有用。據我所知，現在已經發布了文件，但您可以嘗試獲取一些線索
在客戶端的sqlnet.ora中設置如下參數（客戶端必須使用tnsnames.ora）
DIAG_ADR_ENABLED=開啟 trace_directory_client= trace_level_client=管理員 trace_uniqe_client=on trace_timestamp_client=on如果這不是生產系統，您還應該在數據庫伺服器的 sqlnet.ora 中啟用跟踪：
DIAG_ADR_ENABLED=開啟 跟踪目錄伺服器= trace_level_server=管理員 trace_uniqe_server=on trace_timestamp_server=on使用每個跟踪的時間戳資訊使您能夠同步伺服器和客戶端文件的輸出。
trcasst目錄中還有一個$ORACLE_HOME/bin分析 sqlnet 跟踪的工具（但前提是你有trace_timesamp...=off）。在這裡你可以找到一些提示。但也許原始痕跡更有用。trace_level_...=user如果您設置或，您也可以減小跟踪資訊的大小admin。