Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: shutdown abort and database consistency

Re: shutdown abort and database consistency

From: Mark D Powell <>
Date: 9 Jul 2002 05:46:58 -0700
Message-ID: <>

Rick Denoire <> wrote in message news:<>...
> Hello
> I have a fundamental question concerning the consistency of Oracle DB
> under certain circumstances (Version 8.1.7, Solaris)
> It happened that a long running script was startet, which would delete
> millions of lines in one transaction. After two days, the DB crashed
> while some other operations were being done. That means, it showed the
> error ORA-00600 and then it just didn't react to SQL queries any more.
> The long running job mentioned was set to nologging.
> The next day, the DB was "shutdown abort" and restartet. Now there
> seems to be a problem since for some reason some objects can't be
> dropped: The session goes into an undefinite wait state and nothing
> happens. But a full, consistent export (rows=no) of the DB structures
> works fine, so I would assume that the DB is still healthy.
> Now I am wondering if an Oracle DB is able to recover from uncommited
> transactions that are not being logged in redo logs. And I am
> wondering if a "shutdown abort" could render the DB inconsistent in
> this situation. As I understand, a "shutdown abort" should be the last
> thing to do, it is like a provoked crash. By the way, the size and
> number of the Rollback Segments are huge in this DB.
> So why was this "shutdown abort" done anyway? Because the employee
> doing that wanted to avoid the job being rolled back - it was running
> 2 days already. And he would have had to wait to days more for the DB
> to close after issuing "shutdown immediate".
> I very much appreciate your opinions on this matter.
> Rick Denoire.

Rick, the nologging option to my knowledge does not stop the recording of rollback information. The recovery of uncommited transaction data is still accomplished by Oracle through the reading and application of information stored in the rollback segments. The redo log information would have been read and applied during the crash recovery phase of startup and then rollback would be applied to rollback incomplete transactions. And because 8.1 and 9i open the database before rollback is completed sessions can encounter the situation where they must rollback data for incomplete transactions that occurred before the crash. What does v$session_wait and v$transactions show for the affected sessions?

Received on Tue Jul 09 2002 - 07:46:58 CDT

Original text of this message