数据库无法增删改,包括v$transaction视图无法查询,类似于HANG的状态,我首先我通过查询v$session_wait视图，情况如下：
SQL> select sid,event,p1,p2,p3,wait_time,seconds_in_wait,state from v$session_wait where wait_class <> ‘Idle’; SID EVENT P1 P2 P3 WAIT_TIME SECONDS_IN_WAIT STATE———- —————————————– ———- ———- ———- ———- ————— ——————- 125 library cache lock 2130013560 2158412560 301 0 7850 WAITING 126 library cache lock 2130014088 2158399144 301 0 7850 WAITING 127 library cache lock 2130014088 2158495528 301 0 9231 WAITING 128 library cache lock 2130013560 2158380576 301 0 9231 WAITING 129 library cache lock 2130013560 2158307736 301 0 10611 WAITING 130 library cache lock 2130014088 2158498840 301 0 10611 WAITING 131 buffer busy waits 1 11170 1 0 11839 WAITING 132 library cache lock 2130013560 2158391432 301 0 11992 WAITING 133 log file switch (checkpoint incomplete) 0 0 0 0 12616 WAITING 136 log file switch (checkpoint incomplete) 0 0 0 0 12947 WAITING 138 enq: TX – row lock contention 1415053318 589854 665 0 13321 WAITING 139 buffer busy waits 2 9 17 0 12616 WAITING 141 enq: WF – contention 1464205318 0 0 0 1650 WAITING 144 enq: CI – contention 1128857606 1 5 0 15355 WAITING 150 log file switch (checkpoint incomplete) 0 0 0 0 12891 WAITING 159 switch logfile command 0 0 0 0 17051 WAITING 161 log file switch (checkpoint incomplete) 0 0 0 0 12715 WAITING 164 rdbms ipc reply 7 21457644 0 0 0 WAITING 18 rows selected以上看到有三个log file switch (checkpoint incomplete)等待事件,意味着checkpoint没完成,于是查询v$log检查在线日志的情况:SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM———- ———- ———- ———- ———- — —————- ————- ——— 1 1 113 52428800 1 NO CURRENT 8590086940 21-MAR-15 <<<<CURRENT 2 1 111 52428800 1 YES ACTIVE 8590086619 21-MAR-15 <<<<ACTIVE 3 1 112 52428800 1 YES ACTIVE 8590086938 21-MAR-15 <<<<ACTIVESQL> archive log list;Database log mode Archive ModeAutomatic archival Enabled <<<<<<归模式档Archive destination /home/oracle/archOldest online log sequence 111Next log sequence to archive 113Current log sequence 113SQL> ![oracle@ora10g bdump]$ df -hFilesystem Size Used Avail Use% Mounted on/dev/sda3 95G 14G 77G 15% / <<<<<<<<<<<<磁盘空间足够,说明不是磁盘空间不够无法归档造成的/dev/sda1 99M 12M 82M 13% /boottmpfs 1006M 0 1006M 0% /dev/shm[oracle@ora10g bdump]$ ls -lrt /home/oracle/arch/total 107176-rw-r—– 1 oracle oinstall 952832 Mar 15 12:02 1_103_847657195.dbf-rw-r—– 1 oracle oinstall 29585920 Mar 17 21:35 1_104_847657195.dbf-rw-r—– 1 oracle oinstall 14306816 Mar 21 12:02 1_105_847657195.dbf-rw-r—– 1 oracle oinstall 22298112 Mar 21 12:19 1_106_847657195.dbf-rw-r—– 1 oracle oinstall 42112000 Mar 21 17:14 1_107_847657195.dbf-rw-r—– 1 oracle oinstall 159232 Mar 21 17:20 1_108_847657195.dbf-rw-r—– 1 oracle oinstall 1536 Mar 21 17:21 1_109_847657195.dbf-rw-r—– 1 oracle oinstall 15360 Mar 21 17:24 1_110_847657195.dbf-rw-r—– 1 oracle oinstall 148480 Mar 21 17:30 1_111_847657195.dbf-rw-r—– 1 oracle oinstall 1024 Mar 21 17:30 1_112_847657195.dbf <<<<<<<<自17:30时间以后不再有归档日志产生[oracle@ora10g bdump]$ dateSat Mar 21 22:24:09 CST 2015在线日志切换之后没有并归档之前都是处于ACTIVE状态,该状态下无法被复用,没有在线日志组可用时候,数据库会挂起.做了HANGANALYZE：==============HANG ANALYSIS:==============Open chains found:Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :132,128,125,129被sid为133的会话阻塞,而133又在等检查点完成Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :Other chains found:Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/123/6/0x83a6d750/5378/No Wait>Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/126/2/0x83a6c780/4991/library cache lock>– <0/127/1/0x83a6bf98/4933/library cache lock>– <0/130/1/0x83a6a7e0/4875/library cache lock>Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/131/1/0x83a69ff8/4821/buffer busy waits>– <0/126/2/0x83a6c780/4991/library cache lock>– <0/127/1/0x83a6bf98/4933/library cache lock>– <0/130/1/0x83a6a7e0/4875/library cache lock>Chain 6 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/136/7/0x83a68840/4770/log file switch (checkpoint inco>Chain 7 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/139/10/0x83a67870/4609/buffer busy waits>Chain 8 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/141/6/0x83a668a0/4748/enq: WF – contention>Chain 9 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/147/3/0x83a658d0/3079/Streams AQ: qmn slave idle wait>Chain 10 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/148/4/0x83a650e8/3077/Streams AQ: waiting for time man>Chain 11 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/150/166/0x83a64900/4752/log file switch (checkpoint inco>Chain 12 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/152/1/0x83a64118/3068/Streams AQ: qmn coordinator idle>Chain 13 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/159/3/0x83a62960/3062/switch logfile command>Chain 14 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/161/1/0x83a609c0/3054/log file switch (checkpoint inco>Extra information that will be dumped at higher levels:[level 4] : 2 node dumps — [REMOTE_WT] [LEAF] [LEAF_NW][level 5] : 8 node dumps — [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP][level 6] : 12 node dumps — [NLEAF][level 10] : 12 node dumps — [IGN]State of nodes([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):[122]/0/123/6/0x83b52750/5378/SINGLE_NODE_NW/1/2//none[124]/0/125/1/0x83b55220/4993/NLEAF/3/8/[128][132]/127[125]/0/126/2/0x83b56788/4991/NLEAF/9/12/[130]/126[126]/0/127/1/0x83b57cf0/4933/NLEAF/13/14/[125][130]/129[127]/0/128/3/0x83b59258/4931/NLEAF/15/16/[128][124][132]/131[128]/0/129/1/0x83b5a7c0/4877/NLEAF/4/7/[132]/124[129]/0/130/1/0x83b5bd28/4875/NLEAF/17/18/[125][126][130]/none[130]/0/131/1/0x83b5d290/4821/NLEAF/10/11/[132]/125[131]/0/132/3/0x83b5e7f8/4815/NLEAF/19/20/[128][124][127][132]/none[135]/0/136/7/0x83b63d98/4770/SINGLE_NODE/21/22//none[137]/0/138/137/0x83b66868/4757/NLEAF/23/28/[143]/none[138]/0/139/10/0x83b67dd0/4609/NLEAF/29/30/[132]/none[139]/0/140/8/0x83b69338/4605/IGN/31/32//none[140]/0/141/6/0x83b6a8a0/4748/NLEAF/33/34/[143]/none[146]/0/147/3/0x83b72910/3079/SINGLE_NODE/35/36//none[147]/0/148/4/0x83b73e78/3077/SINGLE_NODE/37/38//none[149]/0/150/166/0x83b76948/4752/SINGLE_NODE/39/40//none[151]/0/152/1/0x83b79418/3068/SINGLE_NODE/41/42//none[154]/0/155/1/0x83b7d450/3066/IGN/43/44//none[155]/0/156/1/0x83b7e9b8/3064/IGN/45/46//none[158]/0/159/3/0x83b829f0/3062/SINGLE_NODE/47/48//none[159]/0/160/1/0x83b83f58/3056/IGN/49/50//none[160]/0/161/1/0x83b854c0/3054/SINGLE_NODE/51/52//none[161]/0/162/1/0x83b86a28/3052/IGN/53/54//none[162]/0/163/1/0x83b87f90/3050/IGN/55/56//none <<<<<<<sid为164的阻塞了sid为143的会话
可我，仍在旅行的路上徘徊。等待着每一辆经过的车，让我走到更远的地方。
