Oracle Database Discussions

Oracle has gone bad on the [Red Hat Enterprise Linux ES release 4 (Nahant Update 6)] box taking up all the CPU.

When I try and connect with sqlplus /nolog as sysdba nothing happens, no output.

Any ideas, apart from restarting the box please?

We tried killing all the Oracle processes too & restarting the listener, but this just gives the same...

Log follows...

version = 10.2.0.1.0

Oh, its happened before, last time we couldn't work it out either so restarted the box - it was fine then.


$. oraenv
ORACLE_SID = [oralt] ?
dbhome
ORACLE_HOME = /u01/oracle/oracle/product/10.2.0/db_1
ORACLE_SID = oralt

rh4-ora-t3:oracle:oralt/opt/oracle$ sqlplus /nolog

(nothing)

vi /u01/oracle/oracle/product/10.2.0/db_1/admin/oralt/bdump/alert_oralt.log

Mon Nov 22 02:01:57 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 5118K exceeds notification threshold (2048K)
Details in trace file /u01/oracle/oracle/product/10.2.0/db_1/admin/oralt/udump/oralt_ora_12801.trc
KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.SCHEMA_OBJ.NAME=:NAME1 AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2
Mon Nov 22 11:13:21 2010
Thread 1 advanced to log sequence 3280
Current log# 3 seq# 3280 mem# 0: /u02/oradata/oralt/redo03_1.rdo
Current log# 3 seq# 3280 mem# 1: /u03/oradata/oralt/redo03_2.log

(end)


rh4-ora-t3:oracle:oralt/opt/oracle$ top
top - 10:27:16 up 199 days, 19:32, 2 users, load average: 24.82, 24.67, 24.19
Tasks: 146 total, 30 running, 116 sleeping, 0 stopped, 0 zombie
Cpu(s): 38.0% us, 62.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 3115248k total, 2736140k used, 379108k free, 247956k buffers
Swap: 6289436k total, 80168k used, 6209268k free, 2229880k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
2173 oracle 25 0 17 263:51.52 2.5 607m 74m 70m R oracle
2211 oracle 25 0 17 260:43.53 0.7 620m 21m 10m R oracle
2154 oracle 25 0 17 262:21.71 0.5 605m 14m 13m R oracle
2159 oracle 25 0 17 262:38.20 0.8 604m 24m 23m R oracle
2161 oracle 25 0 17 263:05.82 3.1 607m 93m 90m R oracle
2163 oracle 25 0 17 261:55.21 0.7 620m 19m 18m R oracle
2165 oracle 25 0 17 261:13.85 0.8 606m 24m 22m R oracle
2169 oracle 25 0 17 261:47.42 0.8 605m 22m 21m R oracle
2171 oracle 25 0 17 263:20.01 1.8 606m 55m 52m R oracle
2175 oracle 25 0 17 263:03.81 0.7 604m 22m 21m R oracle
2177 oracle 25 0 17 262:15.91 0.4 605m 10m 9500 R oracle
2209 oracle 25 0 17 261:55.91 0.7 620m 22m 10m R oracle
2745 oracle 25 0 17 257:08.69 0.5 604m 15m 14m R oracle
6885 oracle 25 0 17 89:05.80 0.1 19652 4564 3228 R exp
10344 oracle 25 0 17 1:26.30 0.2 91856 6584 5720 R oracle
17568 oracle 25 0 17 263:29.11 0.7 605m 21m 19m R oracle
22383 oracle 25 0 17 111:38.92 0.1 19576 4600 3280 R sqlplus
27963 oracle 25 0 17 22:56.01 0.2 91856 6584 5720 R oracle
8185 oracle 25 0 16 169:09.85 0.2 29816 6004 3300 R rman
13633 oracle 25 0 16 261:50.72 0.5 605m 14m 13m R oracle
12518 oracle 25 0 16 260:35.53 0.5 605m 14m 13m R oracle
2156 oracle 25 0 15 262:28.54 0.4 604m 11m 9.9m R oracle
2167 oracle 25 0 15 261:06.86 3.2 606m 98m 95m R oracle
2669 oracle 25 0 15 261:46.97 0.6 604m 18m 16m R oracle
15816 root 17 0 1 0:00.09 0.0 5680 1332 1052 R hpetfe
15663 root 16 0 1 0:00.12 0.1 7968 2348 1852 R sshd
15767 root 16 0 1 0:00.19 0.0 2156 1056 780 R top
15768 oracle 16 0 1 0:00.15 0.0 1948 1048 784 R top
1 root 16 0 0 0:58.86 0.0 2468 548 468 S init
2 root RT 0 0 3:44.17 0.0 0 0 0 S migration/0
3 root 34 19 0 0:12.75 0.0 0 0 0 S ksoftirqd/0
4 root RT 0 0 4:24.86 0.0 0 0 0 S migration/1
5 root 34 19 0 0:11.23 0.0 0 0 0 S ksoftirqd/1
6 root RT 0 0 4:28.40 0.0 0 0 0 S migration/2
7 root 34 19 0 0:12.55 0.0 0 0 0 S ksoftirqd/2
8 root RT 0 0 3:59.44 0.0 0 0 0 S migration/3
9 root 34 19 0 0:13.29 0.0 0 0 0 S ksoftirqd/3
10 root 5 -10 0 0:00.84 0.0 0 0 0 S events/0
11 root 5 -10 0 0:00.43 0.0 0 0 0 S events/1
12 root 5 -10 0 0:01.82 0.0 0 0 0 S events/2
13 root 5 -10 0 0:00.80 0.0 0 0 0 S events/3
14 root 6 -10 0 3:08.62 0.0 0 0 0 S khelper
15 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/0


#killed backkup jobs...
rh4-ora-t3:oracle:oralt/opt/oracle$ kill 6885
rh4-ora-t3:oracle:oralt/opt/oracle$ kill 8185
rh4-ora-t3:oracle:oralt/opt/oracle$ kill 22383


rh4-ora-t3:oracle:oralt/opt/oracle$ lsnrctl

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 23-NOV-2010 10:37:39

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 06-AUG-2010 14:57:55
Uptime 108 days 20 hr. 39 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /u01/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.209.208)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "oralt" has 1 instance(s).
Instance "oralt", status READY, has 1 handler(s) for this service...
Service "oraltXDB" has 1 instance(s).
Instance "oralt", status READY, has 1 handler(s) for this service...
Service "oralt_XPT" has 1 instance(s).
Instance "oralt", status READY, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
start

(nothing)

rh4-ora-t3:oracle:oralt/opt/oracle$ uptime
10:38:35 up 199 days, 19:43, 2 users, load average: 23.13, 23.30, 23.62


(lots of playing with listener - but it won't start and it won't let me connect with sqlplus /nolog)

Killed all Oracle processors, still can't connect to sqlplus


Tasks: 98 total, 1 running, 97 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.7% us, 0.2% sy, 0.0% ni, 99.1% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 3115248k total, 2679604k used, 435644k free, 248572k buffers
Swap: 6289436k total, 80168k used, 6209268k free, 2271904k cached

PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
1 root 16 0 0 0:58.87 0.0 2468 548 468 S init
2 root RT 0 0 3:44.19 0.0 0 0 0 S migration/0
3 root 34 19 0 0:12.75 0.0 0 0 0 S ksoftirqd/0
4 root RT 0 0 4:24.88 0.0 0 0 0 S migration/1
5 root 34 19 0 0:11.23 0.0 0 0 0 S ksoftirqd/1
6 root RT 0 0 4:28.42 0.0 0 0 0 S migration/2
7 root 34 19 0 0:12.55 0.0 0 0 0 S ksoftirqd/2
8 root RT 0 0 3:59.45 0.0 0 0 0 S migration/3
9 root 34 19 0 0:13.29 0.0 0 0 0 S ksoftirqd/3
10 root 5 -10 0 0:00.84 0.0 0 0 0 S events/0
11 root 5 -10 0 0:00.43 0.0 0 0 0 S events/1
12 root 5 -10 0 0:01.82 0.0 0 0 0 S events/2
13 root 5 -10 0 0:00.80 0.0 0 0 0 S events/3
14 root 6 -10 0 3:08.63 0.0 0 0 0 S khelper
15 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/0
16 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/1
17 root 5 -10 0 0:00.01 0.0 0 0 0 S kblockd/2
18 root 5 -10 0 0:00.00 0.0 0 0 0 S kblockd/3
19 root 15 0 0 0:00.00 0.0 0 0 0 S khubd
69 root 15 0 0 3:20.26 0.0 0 0 0 S pdflush
70 root 16 0 0 2:25.34 0.0 0 0 0 S pdflush
71 root 16 0 0 53:43.69 0.0 0 0 0 S kswapd0
72 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/0
73 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/1
74 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/2
75 root 6 -10 0 0:00.00 0.0 0 0 0 S aio/3
219 root 25 0 0 0:00.00 0.0 0 0 0 S kseriod
462 root 25 0 0 0:00.00 0.0 0 0 0 S scsi_eh_0
463 root 15 0 0 0:00.00 0.0 0 0 0 S ahc_dv_0
465 root 19 0 0 0:00.00 0.0 0 0 0 S scsi_eh_1
466 root 15 0 0 0:00.00 0.0 0 0 0 S ahc_dv_1
502 root 15 0 0 5:02.83 0.0 0 0 0 S kjournald
1064 root 6 -10 0 0:00.00 0.0 0 0 0 S kauditd
1683 root 6 -10 0 0:00.05 0.0 3336 448 368 S udevd
1795 root 25 0 0 0:00.00 0.0 0 0 0 S phpd_event
1957 root 15 0 0 0:00.01 0.0 0 0 0 S kjournald
1958 root 15 0 0 0:00.01 0.0 0 0 0 S kjournald
1959 root 15 0 0 0:01.44 0.0 0 0 0 S kjournald
1960 root 15 0 0 0:12.13 0.0 0 0 0 S kjournald
1961 root 15 0 0 0:04.08 0.0 0 0 0 S kjournald
1962 root 15 0 0 0:03.89 0.0 0 0 0 S kjournald
1963 root 15 0 0 0:04.93 0.0 0 0 0 S kjournald
1964 root 15 0 0 39:23.43 0.0 0 0 0 S kjournald

Edited by: sunpat on Nov 23, 2010 11:52 AM