Re: shutdown abort and database consistency

"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.

There's your problem, right there... ORA-600.

> The long running job mentioned was set to nologging.

A complete waste of time, if this job was doing regular 'delete from table where some_condition...'. Deletes *always* log, no matter whether you tell them to or not. They are regular DML statements, and regular DML pay no heed to the nologging attribute. So that's not the problem.

>
> The next day, the DB was "shutdown abort" and restartet.

Irrelevant. You can only get yourself in difficulty with a shutdown abort if you proceed immediately afterwards to delete your current redo log. Shutdown aborts are not dangerous, and don't cause you to lose committed data (uncommitted data is another matter, but then that's true of a shutdown immediate, too).

>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.

I'd expect it to be, too.

>
> Now I am wondering if an Oracle DB is able to recover from uncommited
> transactions that are not being logged in redo logs.

The short answer is "no", since if there's nothing in the redo logs, how do you expect Oracle to "redo" them? The rather longer answer is that unless your delete transaction was doing something peculiar, it logged just like any other operation (see above). So that's not the issue.

>And I am
> wondering if a "shutdown abort" could render the DB inconsistent in
> this situation.

Of course it renders it inconsistent. That's what shutdown abort does for a living. But inconsistent doesn't mean "broken", it just means "recovery is required to get it consistent again". Hence the need for redo, and if your transaction is doing normal deletes, then the redo will be available, regardless of whether you said nologging or not. So that's not the issue, either.

>As I understand, a "shutdown abort" should be the last
> thing to do, it is like a provoked crash.

There is a lot of old horse manure talked about shutdown aborts. They are perfectly and utterly safe. They do no more to damage your data than a shutdown immediate. The *only* risk they incur is that if you lose your current redo log right after a shutdown abort, you won't be able to re-open the database until you've performed an incomplete recovery, and you'll lose committed data as a result. But assuming your online logs are suitably multiplexed and mirrored, that's not going to be an issue, is it?

>By the way, the size and
> number of the Rollback Segments are huge in this DB.

Ah hah. I think we might take a wild stab in the dark at this point and say that we put this information with your ORA-600 and conclude that you've encountered an Oracle bug relating to the number of extents for a rollback segment (or something similar).

>
> 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.
>

My opinion is that you mustn't jump at shadows. Shutdown aborts are perfectly OK. Deletes always generate redo, so recoverability is not an issue. But huge rollbacks might be. And the need to recover database consistency after a shutdown abort also depends on rollback segments (you used them when you first performed the transaction, so Oracle needs to use them when re-performing those transactions during crash recovery). The fact that you got an ORA-600 doesn't necessarily mean it *is* the rollback segments, but I seem to recall bugs being logged on this matter for vast numbers of extents in such segments.

The essential point is that you've encountered an ORA-600. That means a bug. It means call Oracle support. It means start searching Metalink to see whether similar bugs have been encountered before, whether patches and workarounds are available.

See what Oracle support has to say first.

Regards
HJR
> Rick Denoire.
Received on Mon Jul 08 2002 - 20:00:40 CDT