Re: Oracle pegging CPU, unable to log in

well, i'm no dba - but 1242 says:

ORA-01242 data file suffered media failure: database in NOARCHIVELOG mode Cause: The database is in NOARCHIVELOG mode and a database file was detected as inaccessible due to media failure.

Action: Restore accessibility to the file mentioned in the error stack and restart the instance.

My guess: restore the datafile in question, possibly the whole db. and i'm guessing you are no in archivelog mode (as per the errormessge) and will not have to roll the db forward in recovery.

"Steve Freeland" <nephtes_at_hasc.com> wrote in message news:JiIz6.12297$up4.434818_at_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 - 13:39:58 CDT