数据库无法增删改,包括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 <<< 2 1 111 52428800 1 YES ACTIVE 8590086619 21-MAR-15 <<< 3 1 112 52428800 1 YES ACTIVE 8590086938 21-MAR-15 <<<
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled <<<<<<归模式档
Archive destination /home/oracle/arch
Oldest online log sequence 111
Next log sequence to archive 113
Current log sequence 113
SQL> !
[oracle@ora10g bdump]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 95G 14G 77G 15% / <<<<<<<<<<<<磁盘空间足够,说明不是磁盘空间不够无法归档造成的
/dev/sda1 99M 12M 82M 13% /boot
tmpfs 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]$ date
Sat Mar 21 22:24:09 CST 2015
在线日志切换之后没有并归档之前都是处于ACTIVE状态,该状态下无法被复用,没有在线日志组可用时候,数据库会挂起.
做了HANGANALYZE：
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : :
<0/
133/2/0x83a69028/4790/log file switch (checkpoint inco>
-- <0/129/1/0x83a6afc8/4877/library cache lock>
-- <0/125/1/0x83a6cf68/4993/library cache lock>
-- <0/128/3/0x83a6b7b0/4931/library cache lock>
-- <0/132/3/0x83a69810/4815/library cache lock> <<<<<<<<<<< Chain 2 : :
<0/
164/1/0x83a5f208/3048/rdbms ipc reply>
-- <0/144/47/0x83a660b8/4402/enq: CI - contention>
-- <0/138/137/0x83a68058/4757/enq: TX - row lock contention> <<<<< Other chains found:
Chain 3 : : |