Oracle pegging CPU, unable to log in

From: Steve Freeland <nephtes_at_hasc.com>
Date: Sat, 07 Apr 2001 17:40:57 GMT
Message-ID: <JiIz6.12297$up4.434818@carnaval.risq.qc.ca>

The current situation is this: The oracle process is hogging near-100% of the CPU (in kernel calls, according to "top") and it's impossible to log in as any user, including "internal". I know I should contact Oracle support but I don't have a valid CSI# right now (don't ask).

	This is Oracle 8.1.6.0.0 on the Solaris/Intel platform.
	Yesterday afternoon an instance of SQL*Loader was interrupted

when it was taking an inordinately large amount of time to load a fairly small amount of data (~ 1500 rows or so). It exited with an error (1242) saying that one of the database's tablespace files, which contained a large index, was showing corruption. The dbv utility confirmed this. Querying the associated table would return an error saying that the index was in an unusable state (although this sort of makes sense because the SQL*Loader invocation was using the direct path anyways). I tried rebuilding the index, not knowing if the corruption reported in the datafile might simply be due to the index being out of date (seemed like a good idea at the time). The index rebuild seemed to work for a little while, the the whole db shutdown.

        This was in the alert file:

CKPT: terminating instance due to error 1242 Instance terminated by CKPT, pid = 560

        And this was in the associated trace file:

Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production With the Partitioning option
JServer Release 8.1.6.0.0 - Production
ORACLE_HOME = /opt/oracle
System name: SunOS

Node name:      m6db1
Release:        5.7
Version:        Generic_106542-12
Machine:        i86pc

Instance name: iverson
Redo thread mounted by this instance: 1
Oracle process number: 5
Unix process pid: 560, image: oracle_at_m6db1 (CKPT)

• 2001-04-06 16:14:53.003
• SESSION ID:(4.1) 2001-04-06 16:14:51.589 error 1242 detected in background process

        When I tried to start it up again, it wouldn't come up due to the errors in the index datafile mentioned earlier. I "offline drop"ed that datafile and tried to open the db, and now it's in the 100% CPU state and no users can log in.

        The following selected entries appeared in the alert log after the crash:

Fri Apr 6 17:47:16 2001
Beginning crash recovery of 1 threads
Fri Apr 6 17:47:16 2001
Thread recovery: finish rolling forward thread 1 Fri Apr 6 17:47:16 2001
Thread recovery: 55 data blocks read, 0 data blocks written, 1112 redo blocks read Thread recovery: start rolling forward thread 1 Crash recovery completed successfully
Fri Apr 6 17:47:17 2001
SMON: enabling cache recovery

        If anyone has any ideas on what to do now, I'd be awfully grateful. Thanks in advance. Received on Sat Apr 07 2001 - 12:40:57 CDT