ORA-3136 timeout 解决之道

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

WARNING: inbound connection timed out (ORA-3136)

    nt OS err code: 0

    nt main err code: 0

WARNING: inbound connection timed out (ORA-3136)

WARNING: inbound connection timed out (ORA-3136)WARNING: inbound connection timed out (ORA-3136)

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.101.xx)(PORT=60776))

    nt secondary err code: 0

WARNING: inbound connection timed out (ORA-3136)

    nt OS err code: 0

TNS-12535: TNS:operation timed out

TNS-12535: TNS:operation timed out

  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.101.xx)(PORT=60755))

TNS-12535: TNS:operation timed out

TNS-12535: TNS:operation timed out

TNS-12535: TNS:operation timed out

TNS-12535: TNS:operation timed out

    ns secondary err code: 12606

    ns secondary err code: 12606

WARNING: inbound connection timed out (ORA-3136)

    ns secondary err code: 12606

    ns secondary err code: 12606

    ns secondary err code: 12606

    ns secondary err code: 12606

Tue Jul 18 23:09:22 2006
WARNING: inbound connection timed out (ORA-3136)
Tue Jul 18 23:09:23 2006
WARNING: inbound connection timed out (ORA-3136)
Tue Jul 18 23:09:25 2006
WARNING: inbound connection timed out (ORA-3136)
Tue Jul 18 23:09:30 2006
WARNING: inbound connection timed out (ORA-3136)
Tue Jul 18 23:12:15 2006
WARNING: inbound connection timed out (ORA-3136)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

这是和网络连接相关的一个错误,Metalink上给出了如下的解决方案:

1.set INBOUND_CONNECT_TIMEOUT_<listenername>=0 in listener.ora
2. set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
3. stop and start both listener and database.
4. Now try to connect to DB and observe the behaviour

详细的解决：

修改listener的inbound_connect_timeout参数的方法
方法一:
[bglbj1][oracle][/home/oracle]>lsnrctl
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 01-SEP-2009 16:28:06
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start              stop               status            
services           version            reload            
save_config        trace              spawn             
change_password    quit               exit              
set*               show*             
LSNRCTL> show
The following operations are available after show
An asterisk (*) denotes a modifier or extended command:
rawmode                    displaymode               
rules                      trc_file                  
trc_directory              trc_level                 
log_file                   log_directory             
log_status                 current_listener          
inbound_connect_timeout    startup_waittime          
snmp_visible               save_config_on_stop       
dynamic_registration      
LSNRCTL> set
The following operations are available after set
An asterisk (*) denotes a modifier or extended command:
password                   rawmode                   
displaymode                trc_file                  
trc_directory              trc_level                 
log_file                   log_directory             
log_status                 current_listener          
inbound_connect_timeout    startup_waittime          
save_config_on_stop        dynamic_registration      
LSNRCTL>
LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully

LSNRCTL> set inbound_connect_timeout 0
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

LSNRCTL> show inbound_connect_timeout
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 0
The command completed successfully

LSNRCTL> set save_config_on_stop on  #表示修改参数永久生效，否则只是临时生效，下次重启监听又还原为原来的值了
LSNRCTL> exit

方法二：
修改listener.ora文件，加入: INBOUND_CONNECT_TIMEOUT_LISTENER_NAME=0
问题解决

修改后依然是启动不了数据库，但在依然启动不了数据库连sqlplus环境都进不去，我查看来最新一个udump 下的

trc文件，其中有Dumping diagnostic information for LCK0:
OS pid = 422264
loadavg : 0.23 0.25 0.26
swap info: free_mem = 17.67M rsv = 128.00M
           alloc = 12067.13M avail = 32768.00M swap_free = 20700.87M
       F S      UID    PID   PPID   C PRI NI ADDR    SZ    WCHAN    STIME    TTY  TIME CMD
  240001 A   oracle 422264      1   0  60 20 a0b36400 106964            Feb 10      -  1:47 ora_lck0_rzywk2
open: The file access permissions do not allow the specified action.
Warning: executed in non-root mode
procstack cannot verify that /unix matches the running kernel.
Kernel symbols might not be validated.
422264: ora_lck0_rzywk2
0x00000001000fc898  sskgpwwait(??, ??, ??, ??, ??) + 0x38
0x00000001000f9e7c  skgpwwait(??, ??, ??, ??, ??) + 0xbc
0x000000010011e42c  kslges(??, ??, ??, ??, ??) + 0x54c
0x000000010012225c  kslgetl(??, ??, ??, ??) + 0x33c
0x00000001049f0998  ksfglt(??, ??, ??, ??, ??) + 0x198
0x00000001045d1b84  kqlmbfre() + 0x144
0x00000001045d61c4  kqlmba(??, ??) + 0x24
0x000000010015efa8  ksbcti(??, ??, ??) + 0x3c8
0x0000000100169f1c  ksbabs(??) + 0x3fc
0x00000001019b5e18  kclabs(??) + 0xd8
0x0000000100166dd4  ksbrdp() + 0x4b4
0x000000010430c93c  opirip(??, ??, ??) + 0x3fc
0x0000000102d9ae38  opidrv(??, ??, ??) + 0x458
0x000000010370b950  sou2o(??, ??, ??, ??) + 0x90
0x0000000100000870  opimai_real(??, ??) + 0x150
0x00000001000006d8  main(??, ??) + 0x98
0x0000000100000368  __start() + 0x98
*** 2011-02-12 05:20:09.050
*** 2011-02-12 05:20:19.051
Waited for detached process: LCK0 for 310 seconds:
*** 2011-02-12 05:20:19.051
Dumping diagnostic information for LCK0:
OS pid = 422264
loadavg : 0.36 0.27 0.27
swap info: free_mem = 32.64M rsv = 128.00M
           alloc = 12072.14M avail = 32768.00M swap_free = 20695.86M
       F S      UID    PID   PPID   C PRI NI ADDR    SZ    WCHAN    STIME    TTY  TIME CMD
  240001 A   oracle 422264      1   0  60 20 a0b36400 106964            Feb 10      -  1:47 ora_lck0_rzywk2
open: The file access permissions do not allow the specified action.
Warning: executed in non-root mode
procstack cannot verify that /unix matches the running kernel.
Kernel symbols might not be validated.