"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<agfgk0$jn8$1_at_lust.ihug.co.nz>...
> "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 --
Howard, you were right to point out that only those blocks that a new
transaction accesses would be rolled back by the new transaction and
not all remaining unrolled back blocks from the original transaction.
But this brings up the interesting question of if you run the same SQL
and the rollback has not yet taken place could your second transaction
end up having to rollback every block affected by the original
transaction? And in a situation like this what would be the
likelihood of a 'snapshot too old' from delayed block cleanout?
Received on Wed Jul 10 2002 - 10:32:59 CDT
