Oracle Database Discussions

The Oracle Database server load average all of sudden increased to 25 and CPU Idle time continuously 0.

The TOP command are showing lots of Oracle proceses are in Running State.

Operating system commands are working but SQL command always hanging. So not able to get SQL prompt.

Due to unavailability of SQL prompt, not able to get any information from Oracle Database Views. No Informartion in Oracle alert File and in any Oracle Trace File.

The Following information captured on Operating System Level.

Please suggest on which conditions this problem can happen??

-------------------------------------------------------------------------------
OS Detail- RHEL3 Update4
Kernel- 2.4.21-27.ELsmp
-------------------------------------------------------------------------------
Database- 10.2.0.1.0
-------------------------------------------------------------------------------
Database Started-Thu Dec6,2007
-------------------------------------------------------------------------------
[root@backup root]# top
15:48:03 up 256 days, 23:15, 2 users, load average: 25.23, 24.72, 24.56

108 processes: 82 sleeping, 26 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 45.0% 0.0% 54.3% 0.4% 0.1% 0.0% 0.0%
cpu00 47.5% 0.0% 50.0% 1.9% 0.4% 0.0% 0.0%
cpu01 42.5% 0.0% 57.4% 0.0% 0.0% 0.0% 0.0%
cpu02 47.0% 0.0% 52.9% 0.0% 0.0% 0.0% 0.0%
cpu03 43.0% 0.0% 56.9% 0.0% 0.0% 0.0% 0.0%
Mem: 2055296k av, 2033244k used, 22052k free, 0k shrd, 205356k buff
1372632k actv, 259868k in_d, 31496k in_c
Swap: 4095976k av, 0k used, 4095976k free 1259084k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
5415 oracle 25 0 148M 147M 144M R 5.5 7.3 2167m 2 oracle
5399 oracle 25 0 13504 12M 11576[b] R 4.9 0.6 2208m 3 oracle
5403 oracle 25 0 131M 130M 129M[b] R 4.9 6.5 2095m 2 oracle
5432 oracle 25 0 12492 11M 10880 R 4.9 0.5 2062m 3 oracle
8417 oracle 25 0 39128 37M 37372 R 4.9 1.8 4452m 2 oracle
25005 oracle 25 0 20216 19M 18032 R 4.9 0.9 2105m 3 oracle
25321 oracle 25 0 11588 10M 9908[b] R 4.7 0.5 2099m 2 oracle
8367 oracle 25 0 39404 38M 37648 R 4.5 1.9 4354m 3 oracle
5417 oracle 25 0 472M 465M 375M[b] R 4.4 23.1 2114m 2 oracle
5401 oracle 25 0 11068 10M 9472 R 3.7 0.5 2054m 0 oracle
5405 oracle 25 0 502M 494M 468M R 3.7 24.6 2040m 1 oracle
8478 oracle 25 0 67676 65M 65380 R 3.7 3.2 2125m 1 oracle
24420 oracle 25 0 108M 108M 105M R 3.7 5.3 2100m 1 oracle
5407 oracle 25 0 36248 20M 19064[b] R 3.5 1.0 2164m 0 oracle
5411 oracle 25 0 441M 441M 438M R 3.5 22.0 2088m 0 oracle
5421 oracle 25 0 11140 10M 8952 R 3.5 0.5 2157m 0 oracle
8352 oracle 25 0 45392 44M 43624 R 3.5 2.1 5475m 0 oracle
5419 oracle 25 0 55184 53M 53544 R 3.3 2.6 2112m 0 oracle
5409 oracle 25 0 31228 29M 28004 R 3.2 1.4 2041m 1 oracle
5458 oracle 25 0 375M 371M 364M R 3.2 18.4 2196m 1 oracle
5413 oracle 25 0 70444 67M 67216 R 2.9 3.3 2031m 0 oracle
28488 root 15 0 704 704 596 S 0.1 0.0 0:00 2 in.telnetd
2235 oracle 15 0 1144 1144 900 S 0.1 0.0 0:03 3 top
-----------------------------------------------------------------------------------
[root@backup sa]# ps -ef | grep ORA
oracle 5399 1 0 2007 ? 1-12:46:57 ora_pmon_BPLORA
oracle 5401 1 0 2007 ? 1-10:13:37 ora_psp0_BPLORA
oracle 5403 1 0 2007 ? 1-10:54:32 ora_mman_BPLORA
oracle 5405 1 0 2007 ? 1-09:59:48 ora_dbw0_BPLORA
oracle 5407 1 0 2007 ? 1-12:03:33 ora_lgwr_BPLORA
oracle 5409 1 0 2007 ? 1-10:01:06 ora_ckpt_BPLORA
oracle 5411 1 0 2007 ? 1-10:47:44 ora_smon_BPLORA
oracle 5413 1 0 2007 ? 1-09:51:09 ora_reco_BPLORA
oracle 5415 1 0 2007 ? 1-12:06:09 ora_cjq0_BPLORA
oracle 5417 1 0 2007 ? 1-11:13:43 ora_mmon_BPLORA
oracle 5419 1 0 2007 ? 1-11:11:59 ora_mmnl_BPLORA
oracle 5421 1 0 2007 ? 1-11:56:12 ora_d000_BPLORA
oracle 5423 1 0 2007 ? 00:00:19 ora_s000_BPLORA
oracle 5432 1 0 2007 ? 1-10:20:58 ora_qmnc_BPLORA
oracle 5458 1 0 2007 ? 1-12:35:47 ora_q000_BPLORA
oracle 5462 1 0 2007 ? 00:00:37 ora_q001_BPLORA
oracle 8352 1 12 May13 ? 3-19:14:32 oracleBPLORA (LOCAL=NO)
oracle 8367 1 9 May13 ? 3-00:33:49 oracleBPLORA (LOCAL=NO)
oracle 8417 1 9 May13 ? 3-02:11:44 oracleBPLORA (LOCAL=NO)
oracle 8478 1 4 May13 ? 1-11:24:54 oracleBPLORA (LOCAL=NO)
oracle 24420 1 17 Jun05 ? 1-10:59:38 ora_j000_BPLORA
oracle 25005 1 17 Jun05 ? 1-11:04:20 ora_j001_BPLORA
oracle 25321 1 17 Jun05 ? 1-10:58:32 oracleBPLORA (LOCAL=N0)
-------------------------------------------------------------------------------------
[root@backup sa]# sar -f sa05
Linux 2.4.21-27.ELsmp (backup) 06/05/2008

12:00:00 AM CPU %user %nice %system %iowait %idle

03:30:00 AM all 16.60 0.00 10.35 0.07 72.98
03:40:00 AM all 16.80 0.00 10.24 0.07 72.88
03:50:00 AM all 16.85 0.00 10.25 0.06 72.84
04:00:00 AM all 16.76 0.00 10.20 0.07 72.97
04:10:00 AM all 17.05 0.03 10.31 1.73 70.88
04:20:00 AM all 19.11 0.00 10.57 0.07 70.25
04:30:00 AM all 17.29 0.00 10.11 0.06 72.54
04:40:00 AM all 16.70 0.00 10.15 0.06 73.08
04:50:00 AM all 16.68 0.00 10.16 0.06 73.09
05:00:00 AM all 16.82 0.00 10.07 0.06 73.05
05:10:00 AM all 16.91 0.00 10.32 0.09 72.67
05:20:00 AM all 16.68 0.00 10.19 0.07 73.06
05:30:01 AM all 16.70 0.00 10.20 0.07 73.03
05:40:00 AM all 16.67 0.00 10.52 0.73 72.08
05:50:00 AM all 27.20 0.00 25.27 0.06 47.48
06:00:00 AM all 47.01 0.00 52.99 0.00 0.00
06:10:01 AM all 46.82 0.00 53.18 0.00 0.00
06:20:00 AM all 47.01 0.00 52.99 0.00 0.00
06:30:00 AM all 46.86 0.00 53.14 0.00 0.00
06:40:00 AM all 46.89 0.00 53.11 0.00 0.00
06:50:00 AM all 47.19 0.00 52.81 0.00 0.00
07:00:00 AM all 47.05 0.00 52.95 0.00 0.00
07:10:01 AM all 47.14 0.00 52.86 0.00 0.00
---------------------------------------------------------------------------------
[root@backup bdump]# strace -tTp 5399
Process 5399 attached - interrupt to quit
16:15:46 times(NULL) = -2074588130 <0.009835>
16:15:46 times(NULL) = -2074588128 <0.009962>
16:15:46 times(NULL) = -2074588127 <0.007963>
16:15:47 times(NULL) = -2074588076 <0.009943>
16:15:47 times(NULL) = -2074588074 <0.009963>
16:15:47 times(NULL) = -2074588072 <0.009963>
------------------------------------------------------------------------------------
[root@backup root]# strace -tTp 24420
Process 24420 attached - interrupt to quit
15:45:02 times(NULL) = -2074772599 <0.000031>
15:45:02 times(NULL) = -2074772599 <0.000023>
15:45:02 times(NULL) = -2074772599 <0.000021>
15:45:02 times(NULL) = -2074772599 <0.000021>
15:45:02 times(NULL) = -2074772599 <0.000022>
15:45:02 times(NULL) = -2074772599 <0.000021>
15:45:02 times(NULL) = -2074772599 <0.000021>
-----------------------------------------------------------------------------------
[root@backup root]# strace -tTp 25005
Process 25005 attached - interrupt to quit
15:47:00 times(NULL) = -2074760724 <0.000037>
15:47:00 times(NULL) = -2074760724 <0.000009>
15:47:00 times(NULL) = -2074760724 <0.000009>
15:47:00 times(NULL) = -2074760724 <0.000009>
15:47:00 times(NULL) = -2074760724 <0.000009>
15:47:00 times(NULL) = -2074760724 <0.000009>
------------------------------------------------------------------------------------