Database hangs
Mas.dbaAug 4 2011 — edited Aug 4 2011Hi I have Oracle 9i RAC on IBM AIX .
Database almost hangs. Getting SQl prompt , some commands are working other hangs.
following is the Alert log messege "
Waited too long for library cache load lock. More info in file /mtn/mtn_app/oracle/admin/****.trc
What to do its a prod database. Killing the sessions does not helped.
Waiting sessions:
SQL> /
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
174 PX Deq: Execute Repl sleeptime/sende 0 WAITING
y rid
185 PX Deq: reap credit -1 WAITED KNOWN TI
ME
82 PX Deq: reap credit -1 WAITED KNOWN TI
ME
398 PX Deq: reap credit -1 WAITED KNOWN TI
ME
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
104 PX Deq: reap credit -1 WAITED KNOWN TI
ME
2 async disk IO count -1 WAITED SHORT TI
ME
2 async disk IO count -1 WAITED KNOWN TI
ME
2 async disk IO count -1 WAITED SHORT TI
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
ME
110 buffer busy global C file# 0 WAITING
R
56 buffer busy global C file# 0 WAITING
R
416 buffer busy global C file# 0 WAITING
R
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
418 buffer busy global C file# 0 WAITING
R
422 buffer busy global C file# 0 WAITING
R
119 direct path read file number 1 WAITED KNOWN TI
ME
42 enqueue name|mode 0 WAITING
64 enqueue name|mode 0 WAITING
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
48 enqueue name|mode 0 WAITING
44 global cache cr requ file# 0 WAITING
est
54 global cache cr requ file# 0 WAITING
est
41 global cache cr requ file# 0 WAITING
est
114 global cache cr requ file# 0 WAITING
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
est
85 global cache cr requ file# 0 WAITING
est
165 global cache cr requ file# 0 WAITING
est
253 global cache cr requ file# 0 WAITING
est
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
262 global cache cr requ file# 0 WAITING
est
53 global cache cr requ file# 0 WAITING
est
32 global cache cr requ file# 0 WAITING
est
44 global cache cr requ file# 0 WAITING
est
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
41 global cache cr requ file# 0 WAITING
est
37 global cache cr requ file# 0 WAITING
est
44 global cache cr requ file# 0 WAITING
est
45 global cache cr requ file# 0 WAITING
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
est
47 global cache cr requ file# 0 WAITING
est
56 global cache cr requ file# 0 WAITING
est
93 global cache cr requ file# 0 WAITING
est
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
107 global cache cr requ file# 0 WAITING
est
108 global cache cr requ file# 0 WAITING
est
115 global cache cr requ file# 0 WAITING
est
420 global cache cr requ file# 0 WAITING
est
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
423 global cache cr requ file# 0 WAITING
est
427 global cache cr requ file# 0 WAITING
est
66 global cache cr requ file# 0 WAITING
est
72 global cache cr requ file# 0 WAITING
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
est
78 global cache cr requ file# 0 WAITING
est
80 global cache cr requ file# 0 WAITING
est
81 global cache cr requ file# 0 WAITING
est
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
27 global cache cr requ file# 0 WAITING
est
266 global cache cr requ file# 0 WAITING
est
267 global cache cr requ file# 0 WAITING
est
269 global cache cr requ file# 0 WAITING
est
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
401 global cache cr requ file# 0 WAITING
est
402 global cache cr requ file# 0 WAITING
est
413 global cache cr requ file# 0 WAITING
est
415 global cache cr requ file# 0 WAITING
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
est
419 global cache cr requ file# 0 WAITING
est
91 global cache cr requ file# 0 WAITING
est
46 global cache cr requ file# 0 WAITING
est
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
43 global cache cr requ file# 0 WAITING
est
53 global cache cr requ file# 0 WAITING
est
96 global cache open x file# 0 WAITING
51 global cache open x file# 0 WAITING
49 global cache open x file# 0 WAITING
393 global cache open x file# 0 WAITING
39 global cache open x file# 0 WAITING
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
410 global cache open x file# 0 WAITING
164 global cache s to x file# 0 WAITING
408 global cache s to x file# 0 WAITING
405 global cache s to x file# 0 WAITING
40 global cache s to x file# 0 WAITING
76 global cache s to x file# 0 WAITING
155 jobq slave wait 0 WAITING
407 jobq slave wait 0 WAITING
125 jobq slave wait 0 WAITING
111 jobq slave wait 0 WAITING
9 latch free address 0 WAITING
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
57 latch free address 0 WAITING
22 latch free address 0 WAITING
23 latch free address 0 WAITING
21 latch free address 0 WAITING
73 latch free address 0 WAITING
58 latch free address 0 WAITING
260 latch free address 0 WAITING
395 latch free address 0 WAITING
403 latch free address 0 WAITING
414 latch free address 0 WAITING
400 latch free address 0 WAITING
SID EVENT P1TEXT WAIT_TIME STATE
---------- -------------------- --------------- ---------- ---------------
261 latch free address 0 WAITING
127 library cache pin handle address 0 WAITING
1 pmon timer duration 0 WAITING
1 pmon timer duration 0 WAITING
1 pmon timer duration 0 WAITING
1 pmon timer duration 0 WAITING
27 smon timer sleep time 0 WAITING
27 smon timer sleep time 0 WAITING
27 smon timer sleep time 0 WAITING
95 rows selected.
SQL> select INST_ID,SID,SERIAL#,SERVER#,DEGREE,REQ_DEGREE from gv$px_session;
INST_ID SID SERIAL# SERVER# DEGREE REQ_DEGREE
---------- ---------- ---------- ---------- ---------- ----------
2 174 3057
2 185 4020 2 4 4
4 114 1972 4 4 4
3 91 375 3 4 4
1 410 203 1 4 4
Many Thanks
Regards
Edited by: user7699943 on Aug 4, 2011 6:51 PM
Edited by: user7699943 on Aug 4, 2011 6:54 PM
Edited by: user7699943 on Aug 4, 2011 7:09 PM