Re: shutdown abort and database consistency

"Mark D Powell" <mark.powell_at_eds.com> wrote in message news:178d2795.0207090446.5b4e35ff_at_posting.google.com...
> Rick Denoire <100.17706_at_germanynet.de> wrote in message
news:<ql1kius3ft25baa3kcj70raru7ou3jlled_at_4ax.com>...
> > 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.

Bit of an over-statement, there. A session encountering a block containing data still in the rolled-forward phase after instance recovery takes time out to roll that single *block* back. The rest of the *transaction* is handed off to SMON to deal with. Users won't need to rollback more than one block's worth of data.

Regards
HJR
>What does v$session_wait and v$transactions show for the
> affected sessions?
>
> -- Mark D Powell --
Received on Tue Jul 09 2002 - 15:21:52 CDT